Slow SELECT * on InnoDB While Disk Read Speed Looks Low

A plain SELECT * FROM orders on a 40GB InnoDB table takes twenty-five minutes. You run iostat while it happens and the disk is reading at maybe 8 MB/s. The same disk does several hundred MB/s when you throw dd at it. So the disk is fine, MySQL is fine, nothing is at 100% of anything, and the query is still crawling.

This exact question has been sitting on dba.stackexchange for over a decade with no accepted answer, and I understand why: everyone who replies gives one of the standard answers, "add an index" or "your disk is slow", and neither is true. There is no WHERE clause to index, and the disk is not slow.

The thing to understand is that throughput is not a property of your disk, it is a property of your access pattern. A device that reads 500 MB/s in 1MB chunks can read 30 MB/s in 16KB chunks, on the same hardware, in the same second. InnoDB's page is 16KB. That is the whole mystery, and once you see it, the diagnosis becomes arithmetic.

The arithmetic that explains the number you are seeing

InnoDB reads in 16KB pages. If it issues those one at a time and waits for each, your ceiling is page size divided by round-trip latency, and nothing else matters. Half a millisecond of latency gives you roughly 32 MB/s. Two milliseconds, common on network-attached storage, gives you roughly 8 MB/s. That is the 8 MB/s in your iostat output, and it is not a hardware fault.

InnoDB avoids this with read-ahead. It groups pages into extents (64 pages, so 1MB with the default page size) and, when innodb_read_ahead_threshold pages of an extent have been read sequentially, it prefetches the whole next extent asynchronously. The default threshold is 56 out of 64, which is deliberately conservative. When read-ahead engages you are moving megabytes per request and the disk finally gets to show what it can do. When it does not engage, you are back to page-at-a-time.

Diagnose in four measurements, in this order

Step 1: Is it disk-bound at all?

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

You get five counters. The two that matter first:

• Innodb_buffer_pool_read_requests is logical page reads, served from memory or not.
• Innodb_buffer_pool_reads is the subset that had to go to disk.

Take a snapshot, run the slow query, snapshot again, and use the deltas rather than the since-startup totals, which are meaningless on a server that has been up for months. If reads is a large fraction of read_requests for that window, the scan is genuinely reading from disk and you continue to step 2. If it is near zero and the query is still slow, your problem is CPU or row-by-row processing, not I/O, and you should stop reading this section and go look at EXPLAIN ANALYZE.

While you are there, note Innodb_buffer_pool_read_ahead. If that counter barely moves during a full table scan, read-ahead is not helping you and step 2 will confirm why.

Step 2: How big is each read?

iostat -x 1 5

The column to look at is rareq-sz, the average read request size in kB (on older sysstat builds you only get avgrq-sz in sectors, covering reads and writes together, which is less useful but still indicative).

• 16 to 32 kB with a high r/s: page-at-a-time. You are latency-bound. This is the case that produces the mystifying low throughput.
• 128 kB and up: read-ahead is working and you are actually moving data. If it is still slow, you are bandwidth or IOPS capped, which is step 4.

Also read r_await. Single-digit milliseconds on local NVMe is normal; tens of milliseconds means the storage is queued, shared, or throttled.

Step 3: How many pages does this query actually need?

Two things silently multiply the page count for the same logical rows.

Fragmentation. A table that has had heavy deletes leaves half-empty pages behind. InnoDB does not compact them for you, so a table holding 20GB of rows can occupy 32GB of pages, and a full scan reads all 32GB.

SELECT TABLE_NAME,
       ROUND(DATA_LENGTH  / 1024 / 1024) AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024) AS index_mb,
       ROUND(DATA_FREE    / 1024 / 1024) AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_FREE DESC
LIMIT 10;

DATA_FREE is an estimate, not gospel, but a table showing several GB free next to its data size is worth rebuilding.

Off-page columns. Large TEXT, BLOB and long VARCHAR values are stored in overflow pages outside the row. SELECT * fetches every one of them; SELECT id, customer_id, total does not touch them at all. If your table has a payload JSON or raw_response TEXT column holding webhook bodies, that is very likely the bulk of your 40GB, and you are reading all of it to display a list of order numbers.

Step 4: Is the storage capped?

Multiply your volume's IOPS limit by 16KB and compare it with the throughput you observed. A default AWS gp3 volume gives a baseline of 3,000 IOPS regardless of size, which lands you near 48 MB/s of small random reads. Older gp2 volumes and many budget VPS plans use burst credits: fast for the first few minutes, then a cliff. That cliff is a very common explanation for "it was fine last week", and on shared hosting you may simply be sharing spindles with someone running a backup.

The fixes, roughly in order of payoff

Stop selecting the columns you do not use

This is boring and it is usually the biggest win. Naming the four columns you render instead of * can drop the pages read by an order of magnitude on a table with wide text columns, and if those columns are covered by an existing secondary index, MySQL can satisfy the query from the index without touching the clustered index at all.

Size the buffer pool for the working set, not the disk

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
       @@innodb_buffer_pool_instances               AS instances,
       @@innodb_read_io_threads                     AS read_threads,
       @@innodb_read_ahead_threshold                AS ra_threshold,
       @@innodb_random_read_ahead                   AS random_ra;

On a dedicated database server, 50 to 70 percent of RAM is a sane buffer pool. Going to 90 percent is a well-loved piece of advice that reliably ends in the OOM killer, because per-connection buffers, temporary tables and the operating system all need memory too. And note that a buffer pool cannot cache a 40GB table on a 8GB box, so if the whole table genuinely must be scanned regularly, the fix is not memory, it is not scanning the whole table.

Cold pool after a restart is worth ruling out explicitly: run the query twice. If the second run is dramatically faster, you were warming the cache, and the question becomes why the pool is being evicted, not why the disk is slow.

Rebuild a fragmented table, once, with your eyes open

ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

This rebuilds and compacts the tablespace. It needs roughly as much free disk as the table's size for the rebuild, it takes a long time on a big table, and the benefit erodes as churn resumes. Do it when DATA_FREE justifies it, not as routine maintenance.

Chunk the scan instead of running one enormous one

If this is an export or a batch job, keyset pagination on the primary key turns one long, cache-hostile scan into many short ones that behave far better under concurrency:

SELECT id, customer_id, total
FROM orders
WHERE id > ?
ORDER BY id
LIMIT 10000;

Use the last id of each batch as the next parameter. Never use LIMIT 10000 OFFSET 990000 for this; the offset form re-reads and discards everything before it, so each page gets slower than the last.

Verification

Take the deltas again, the same way, and compare like for like:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- run the query
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

What you want to see is Innodb_buffer_pool_reads for the query dropping substantially, and rareq-sz in iostat rising if read-ahead is now engaging. Wall-clock time is the headline, but the counters tell you why it improved, which is what stops you re-breaking it next quarter.

On MySQL 8.0.18 and later, EXPLAIN ANALYZE gives actual timings per step, so you can confirm the scan is where the time went rather than assuming:

EXPLAIN ANALYZE SELECT id, customer_id, total FROM orders;

What people get wrong

"Add an index." A query with no WHERE and no ORDER BY cannot use one usefully, and forcing a secondary index makes things worse: InnoDB walks the index in index order and then looks up each row in the clustered index, which is a random 16KB read per row. That is the slowest possible way to read a whole table. Indexes are for filtering, and getting them right is its own discipline, as I have written about for large ERPNext ledger tables.

"Prove the disk is fine with dd." dd with a 1MB block size measures large sequential reads. InnoDB in this state is doing 16KB reads. You are benchmarking a workload you do not have, then concluding the storage is innocent. Use fio with a 16KB random read profile if you want a comparable number.

"Turn on innodb_random_read_ahead." It is off by default because it costs CPU and frequently prefetches pages nobody wants. It is worth testing, not worth enabling blindly, and it will not rescue a query whose real problem is reading a TEXT column.

"OPTIMIZE TABLE weekly." On InnoDB this maps to a table rebuild. Running it on a schedule burns hours of I/O and disk space to reclaim space that immediately starts refilling. Measure DATA_FREE and act on it, do not cron it.

"Enable the query cache." It was removed in MySQL 8.0. If you are on an older version where it exists, it serialises writes on the tables it caches and is usually a net loss on anything busy.

When it is still broken

1. Throughput matches your IOPS cap times 16KB. Then you have found the answer and it is commercial, not technical: provisioned IOPS, a different volume type, or moving the working set to local NVMe. No configuration change beats a hard cap.
2. Something else is competing for the disk. Check whether a backup, a replica catching up, or an analytics job is running in the same window. A nightly mysqldump overlapping with a report is the classic. The slow query log correlated with the clock is the fastest way to see it, and I go through reading that log properly in the slow query log playbook.
3. The table is simply too big for the question being asked. If a page needs a count or a summary, precompute it. Scanning 40GB to render a dashboard tile is a design problem, and the durable fix is archiving old rows out of the hot table, which I covered in what to archive when a database hits 40GB.
4. It is fast on a copy and slow in production. Compare innodb_buffer_pool_size, the volume type and the concurrency. A restore onto a fresh volume with no other traffic is not a fair comparison, and the difference between them is the actual finding.

Reference: MySQL InnoDB read-ahead documentation and the InnoDB buffer pool documentation.