1. The Midnight I/O Bottleneck Nightmare
The clock strikes 1:30 AM. The on-call Slack channel is blowing up with P1 alerts. Server CPU spikes to 98%, disk I/O hits 100%, and disk latency (await) on the NVMe drives surges from 0.5ms to 45ms. Cart API response times jump from 50ms to 30-second timeouts. Customer complaints start flooding in.
A common knee-jerk reaction is to immediately run SHOW PROCESSLIST;. The screen floods with hundreds of connections. Most SELECT statements are stuck in states like Sending data or Creating tmp table. Staring at that wall of text, you have no way of knowing which query started the fire. Which table is saturating the disk? Which thread is exhausting memory? The SHOW PROCESSLIST; command is completely useless here.
Digging through hundreds of identical status lines only adds to the confusion. What you need are concrete metrics, not panicked guesswork.
2. Where Do I/O Bottlenecks Actually Come From?
Barring actual hardware failures, I/O bottlenecks in InnoDB typically stem from three main sources:
- Full Table Scans: Unindexed queries force InnoDB to read millions of data pages from disk into the Buffer Pool. When the Buffer Pool fills up, dirty page flushing kicks into overdrive to free up space, choking disk bandwidth.
- Disk Temporary Tables: Queries using
GROUP BY,DISTINCT, orUNIONexceed thetemptable_max_ramthreshold (in MySQL 8.0) ortmp_table_size. The data is immediately spilled to disk as an on-disk InnoDB or MyISAM temporary table. - Redo Log and Doublewrite Buffer Contention: High-volume insert and update transactions write data heavily, overwhelming the I/O threads’ ability to flush pages fast enough.
Standard monitoring tools only tell you that the disk is busy—they cannot pinpoint which thread is keeping it that way.
3. Limitations of Traditional Diagnostic Tools
Method 1: Operating System Commands (top, htop, iostat)
# Monitor extended I/O every second
iostat -xz 1 5Limitation: The iostat command merely reveals that the mysqld process is hitting 100% %util with sky-high await times. The operating system cannot see beneath the surface. You still cannot tell which table or .ibd datafile is generating those thousands of IOPS.
Method 2: Inspecting the Slow Query Log Combined with EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC;Limitation: The slow query log only logs queries after they have finished executing. If a heavy transaction is hanging for 15 minutes, the slow log will be completely empty in the meantime. Furthermore, a ‘death by a thousand cuts’ scenario—such as 1,000 lightweight 20ms queries firing simultaneously within a single second—slips right past a slow log configured with long_query_time = 1, even though they collectively crush disk I/O.
4. The Solution: Leveraging Performance Schema and sys Schema
Available since MySQL 5.7 and enabled by default in MySQL 8.0, Performance Schema introduces very low overhead (typically just 1% to 3% CPU). It is complemented by the sys Schema—a collection of built-in views that translate complex, raw instrumentation metrics into human-readable units like ms, sec, MB, and GB.
Step 1: Verify That Performance Schema Is Active
Check the system variable on the server:
SHOW VARIABLES LIKE 'performance_schema';If it returns ON, your system is ready. If it displays OFF, add the following configuration to your /etc/my.cnf file and restart MySQL:
[mysqld]
performance_schema=ONStep 2: Find the Top 5 Most Time-Consuming Queries
Query the statement_analysis view to locate the statements with the highest total execution time (total latency) since server startup:
SELECT
query,
exec_count,
total_latency,
avg_latency,
rows_sent_avg,
rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 5;Compare the two columns rows_examined_avg and rows_sent_avg. If a query scans an average of 500,000 rows just to return 10 results, you have found a prime culprit: a missing index causing wasteful disk reads, which can often be resolved by designing covering indexes to eliminate table lookups.
Step 3: Locate the Most Frequently Full-Scanned Tables
The schema_tables_with_full_table_scans view immediately highlights the tables undergoing the most full table scans:
SELECT
object_schema,
object_name,
rows_full_scanned,
latency
FROM sys.schema_tables_with_full_table_scans
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
ORDER BY latency DESC
LIMIT 5;If a table containing 10 million records shows a rapidly climbing rows_full_scanned metric, it indicates the application is missing filter conditions on indexed columns.
Step 4: Identify Which .ibd Files Face the Heaviest I/O Load
To pinpoint the exact physical files bottlenecking your storage, query the io_global_by_file_by_bytes view:
SELECT
file,
count_read,
total_read,
count_write,
total_written
FROM sys.io_global_by_file_by_bytes
WHERE file LIKE '%.ibd'
ORDER BY (total_read + total_written) DESC
LIMIT 5;The output clearly shows which tablespace file accounts for the highest read/write volume. You can then consider moving that table to a dedicated storage mount, enabling InnoDB page compression, reclaiming space from table fragmentation, partitioning the table, or caching hot data in Redis.
Step 5: Detect Queries Forcing Temporary Tables to Disk
The statements_with_temp_tables view helps identify queries generating disk-based temporary tables:
SELECT
query,
exec_count,
memory_tmp_tables,
disk_tmp_tables,
avg_latency
FROM sys.statements_with_temp_tables
WHERE disk_tmp_tables > 0
ORDER BY disk_tmp_tables DESC
LIMIT 5;If disk_tmp_tables is high, check whether your SQL statement runs SELECT * with TEXT or BLOB columns alongside a GROUP BY. Also, review the temptable_max_ram setting (defaulting to 1GB in MySQL 8.0) or tmp_table_size in MySQL 5.7 and tune them accordingly.
Step 6: Reset Metrics to Validate Optimization Results
After creating indexes, removing unused indexes, or refactoring application code, clear the accumulated metric counters to evaluate the new workload:
CALL sys.ps_truncate_all_tables(FALSE);This procedure only clears in-memory performance statistics tracked by Performance Schema. Your application tables and business data remain completely untouched. You can now accurately compare I/O metrics before and after optimization, or simulate load with mysqlslap to verify the improvements.
