How MySQL InnoDB’s Prefetch and LRU Buffer Pool Boost Query Performance

1. Linear Prefetch

InnoDB uses linear prefetch by default to speed up data reads, loading the next extent (a contiguous group of 64 pages) into the Buffer Pool when about 56% of the current extent has been accessed. The behavior is controlled by the innodb_read_ahead_threshold variable.

<code>show variables like 'innodb_read_ahead_threshold'</code>

When the threshold is reached, MySQL assumes the next region will be accessed and asynchronously loads the entire next extent into the Buffer Pool, maximizing performance for sequential access patterns.

2. Random Prefetch

Random prefetch is disabled by default and can be toggled with the innodb_random_read_ahead variable. When enabled, if 13 consecutive pages (regardless of order) in an extent are read, the remaining pages of that extent are loaded into the Buffer Pool.

Random prefetch is effective for workloads where data is not stored sequentially or future page accesses cannot be predicted.

3. LRU List in the Buffer Pool

The Buffer Pool uses an LRU (Least Recently Used) list divided into an old generation (≈37% of the pool) for infrequently accessed pages and a new generation (≈63%) for hot pages. When a page is first loaded, its control block is placed at the head of the old‑generation list.

If a page remains in the old generation for more than the innodb_old_blocks_time (default 1 second) and is still accessed, it is promoted to the new generation, but only after it has been accessed beyond the first quarter of the new region, ensuring true hot pages stay near the LRU head.

Pages that are never reused (e.g., from a full table scan) stay in the old generation and are eventually evicted, preserving space for hot data in the new generation.

In summary, MySQL’s prefetch strategies and LRU buffer‑pool design work together to keep frequently accessed data in memory, improve buffer‑pool hit rates, and reduce I/O latency for both sequential and random workloads.