Identifying Slow Disk I/O in MySQL Using performance_schema and sys.x$latest_file_io

Problem: Disk I/O alarm indicates saturation. Which MySQL file—binlog, redo log, or a table—is responsible?

Environment Setup: Based on a previous experiment, an environment is built to simulate slow binlog disk I/O.

Experiment Steps:

Enable performance_schema instruments (producers) and consumers (waits).

Reset performance_schema configuration to defaults, where I/O instruments are already enabled.

Enable the waits consumer.

Clear previously recorded performance data.

Apply load to MySQL.

In another session, observe recent I/O behavior.

Observations show that binlog flush I/O is noticeably slower than other operations, matching the constructed scenario, allowing rapid identification of the slow file.

With the thread ID, the corresponding operation can be located (see image).

Conclusion: By querying sys.x$latest_file_io we retrieve recent I/O records and sort them.

Note:

1. sys.latest_file_io is not used because it cannot sort by operation latency.

2. Views starting with x$ in the sys schema contain raw data; other views present human‑readable formats (e.g., time units).

3. The sys.x$latest_file_io view involves two tables:

performance_schema.events_waits_history_long

and

performance_schema.threads

. If a thread exits, it disappears from the view, so the view reflects the latest I/O of currently active threads, not the absolute latest I/O operation.

Feel free to leave comments if you have more MySQL questions.

Want more technical articles? Click “Watching”.