Disk Latency

Storage is often the real constraint, and it is frequently misdiagnosed as a query problem because the same query is fast when its data is cached.

Measuring

# Per-device latency and utilisation.
iostat -xz 5
 
# Which processes are waiting on I/O.
iotop -o
 
# Latency distribution for a specific device.
biolatency-bpfcc -D 10 1

In iostat, the columns that matter are r_await and w_await (average wait per read and write, in milliseconds) and %util. Sustained await in the tens of milliseconds on SSD-class storage means the device is saturated or throttled.

From inside the database:

-- PostgreSQL: sessions waiting on I/O right now.
SELECT wait_event, count(*) FROM pg_stat_activity
WHERE wait_event_type = 'IO' GROUP BY 1 ORDER BY 2 DESC;

track_io_timing = on adds real I/O timings to EXPLAIN (ANALYZE, BUFFERS), which turns "this query is slow" into "this query spent 4 seconds reading".

Cloud storage limits

Check the instance limit, the volume limit and the credit balance before concluding the database is at fault.

What generates the I/O

• Reads that miss the cache — the working set does not fit. See Memory Pressure.
• Write-ahead logging, on every commit with durable settings.
• Checkpoints and flushes, which arrive in bursts and can saturate a device that is fine on average.
• Background maintenance — vacuum, compaction, merges — which is often the largest I/O consumer.
• Backups, competing with production traffic unless taken from a replica.

Reducing it

Fix the cache first. Most excess read I/O is a working set that no longer fits, not a slow disk.

Separate the write path. Putting the transaction log on its own device removes the interference between sequential log writes and random data reads.

Smooth the checkpoints. In PostgreSQL, a larger max_wal_size with checkpoint_completion_target = 0.9 spreads the flush over the interval instead of concentrating it.

Tell the engine what the storage is. random_page_cost in PostgreSQL and innodb_io_capacity in MySQL default to values describing spinning disks; leaving them wrong on NVMe produces bad plans and throttled flushing respectively.

Move backups to a replica, so a nightly backup does not compete with the primary's I/O.

Check the filesystem mount options — noatime avoids a write on every read.