Menu
Enable MySQL slow query log and use pt-query-digest to identify the most expensive database queries
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
MCP tool reference — exec, benchmark_start, benchmark_status, note_write, etc. Use when: executing commands on VMs, running benchmarks, or recording findings
ISUCON13 contest manual — server topology, service management, benchmark execution, and rules. Use when: checking environment setup, running benchmarks, or understanding contest constraints
ISUPipe application manual — features, terms, constraints, and API specs. Use when: understanding the app domain, checking API behavior or MUST/MAY requirements
General strategy for identifying and adding missing database indexes in ISUCON web applications
General techniques for optimizing user icon/avatar image serving in ISUCON web applications
General techniques for identifying and fixing N+1 query problems in ISUCON web applications
| name | slow-query-analysis |
| description | Enable MySQL slow query log and use pt-query-digest to identify the most expensive database queries |
Identify the most expensive MySQL queries using the slow query log and pt-query-digest.
exec host="vm1" command="sudo mysql -u isucon -pisucon -e \"
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.01;
\""
Note: long_query_time = 0.01 captures queries taking >10ms. For initial analysis, 0.1 (100ms) may be sufficient.
exec host="vm1" command="which pt-query-digest || sudo apt-get install -y percona-toolkit"
Clear the slow query log:
exec host="vm1" command="sudo truncate -s 0 /var/log/mysql/slow.log"
Run a benchmark:
benchmark_start
Analyze results:
exec host="vm1" command="sudo pt-query-digest /var/log/mysql/slow.log --limit 10"
Profile — Top queries by total time:
Rank Query ID Response time Calls R/Call
==== ================================ ============= ===== ======
1 0x1234567890ABCDEF... 150.0000 45.0% 500 0.3000
Per-query details — Full SQL, EXPLAIN output, index usage
| Pattern | Issue | Fix |
|---|---|---|
| High R/Call, low Calls | Single slow query | Add index, rewrite query |
| Low R/Call, high Calls | N+1 pattern | Batch or JOIN |
| Full table scan in EXPLAIN | Missing index | Add appropriate index |
| filesort in EXPLAIN | Sorting without index | Add composite index |
exec host="vm1" command="sudo tail -200 /var/log/mysql/slow.log | grep -E 'Query_time|^SELECT|^UPDATE|^INSERT'"
To avoid log file growth during production benchmarks:
exec host="vm1" command="sudo mysql -u isucon -pisucon -e \"SET GLOBAL slow_query_log = 'OFF'\""