I/O investigations go wrong when every read is called a storage problem. MySQL is expected to read and write; the issue is whether physical work is disproportionate to useful work or whether the storage path cannot serve the workload at the required latency.
Build a time-aligned picture
Compare read and write activity, latency or queueing where available, buffer-pool reads, dirty-page pressure, checkpoint behaviour and query duration. A cache miss rate rising with full scans points first to query and index design. High write activity during a bulk load or index build may be expected, but it can still delay foreground commits. A saturated device can affect otherwise efficient statements, so do not isolate the database counters from the storage evidence.
Read workload clues
- Rows examined far above rows returned suggests inefficient access.
- Temporary tables and filesorts can multiply read and write work.
- A working set that exceeds available cache may need a capacity decision after query tuning.
- A sudden read pattern after a release often deserves plan comparison before hardware changes.
Write workload clues
For write-heavy systems, look at commit rate, redo and data-file activity, long transactions, secondary-index cost and batch size. More indexes can make reads cheaper while making inserts and updates more expensive. Assess the whole write path and test a representative load rather than optimizing one query in isolation.
Choose the remedy deliberately
The remedy may be a better index, a rewritten query, a smaller batch, more appropriate storage or a larger cache-capable instance. Record the expected change before acting, then check latency, throughput and error rates afterwards. That is how an I/O change becomes an improvement rather than an expensive experiment.
Measure physical read demand over an interval
Collect Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests at two times. The first counts logical requests that required a disk read; the second provides the logical-read context. Compare their deltas over the same interval and discard comparisons that cross a counter reset. A lifetime hit ratio can remain excellent while a new reporting workload is causing disruptive reads now.
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Innodb_buffer_pool_reads',
'Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_wait_free',
'Innodb_os_log_written'
);
Use counts and rates alongside latency. Ten thousand IOPS with acceptable response time is different from a smaller workload queued behind a slow device. On a managed service, check both its storage allowance and its instance throughput limit; increasing one may leave the other unchanged.
Example: the daily report evicts useful pages
Suppose customer lookups slow at the same time a report scans several large tables. Lookup SQL has not changed, but physical reads increase during and after the report. Investigate whether the report reads unnecessary columns or historical partitions, whether an appropriate index can reduce its footprint, and whether it can run on a suitable reporting replica. More memory is one candidate, not the only explanation.
A replica creates another decision: can the report tolerate stale data, and can the replica apply changes while also running the report? Moving the work without checking freshness and replication capacity may simply relocate the incident. Record the reporting service's consistency requirement before changing its destination.
Keep the before-and-after comparison honest
Run the same representative business workload after the change. Compare physical reads per completed lookup, p95 response time and report duration, then look at the write cost of any added index. A warm-cache test alone is insufficient if the incident happens after maintenance, restart or a large scan.
For a storage upgrade, preserve both the configured capacity and measured service time. A lower I/O percentage on a larger limit does not prove the query improved; the same expensive work may simply consume a smaller fraction of a more costly allocation. Explain which result the business actually needed.
Keep the evidence for the next incident
Mini DBA MySQL monitoring brings query and metric history together with InnoDB and connection diagnostics for investigating recurring incidents. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading