Why InnoDB Needs a Buffer Pool and How It Works

1. Why a Buffer Pool is Needed

InnoDB stores all data (indexes, system tables) on disk as 16 KB pages. Reading a row requires loading the whole page, but disk I/O is orders of magnitude slower than CPU. Caching pages in memory avoids repeated disk reads; the memory area used for this cache is called the Buffer Pool.

2. Composition of the Buffer Pool

The Buffer Pool is a contiguous memory region allocated at MySQL startup. The default size is 128 MiB and can be changed with innodb_buffer_pool_size (minimum 5 MiB). The region consists of three parts:

Control blocks : metadata for each cached page (tablespace ID, page number, list position). They occupy the first half of the region.

Data pages : the actual 16 KB pages that hold table and index data, stored in the second half.

Fragment : unused space that remains when a control block cannot be paired with a data page.

Each control block consumes about 5 % of a data page, so the actual memory allocated is slightly larger than the value of innodb_buffer_pool_size .

3. Three Linked Lists that Drive Management

Free list : holds pages that are currently empty; new pages are taken from here.

Hash table : maps tablespace ID + page number to a cached page, providing O(1) lookup.

Flush list : records pages that have been modified (dirty pages) and need to be written back to disk.

4. LRU List and Eviction Policy

When the pool fills, InnoDB evicts pages using an LRU (Least Recently Used) algorithm. The naïve LRU moves every accessed page to the head of the list, but two problems arise.

Problem 1 – Read‑ahead

InnoDB performs linear and random read‑ahead. Correct predictions speed up scans, but mispredictions load unused pages into the head, pushing hot pages toward the tail.

Problem 2 – Full‑table scans

A query without a suitable index loads all pages of a large table, displacing hot pages.

Solution: split the LRU list into young and old regions, controlled by innodb_old_blocks_pct (default 37 %). Example: with 10 000 pages, roughly 3 700 belong to the old region and 6 300 to the young region.

When a page is first loaded, it is placed at the head of the old region.

A page moves from old to young only if the time between its first and most recent access exceeds innodb_old_blocks_time (default 1000 ms).

This prevents one‑off scans from polluting the young region.

Further optimization

Only nodes in the last three‑quarters of the young region are moved to the head on access; the first quarter stay put, reducing list‑adjustment overhead.

5. When Dirty Pages Are Flushed

Three flushing mechanisms run in background threads:

BUF_FLUSH_LRU : periodically scans the tail of the LRU list up to innodb_lru_scan_depth and writes dirty pages.

BUF_FLUSH_LIST : periodically flushes a batch from the flush list; the rate depends on server load.

BUF_FLUSH_SINGLE_PAGE : when no free buffers exist, a user thread is forced to flush a single dirty page, causing the request to wait for disk I/O.

The first two happen silently; the third is a useful diagnostic signal because it makes a client thread wait for I/O.

6. Multiple Instances and Chunk Allocation for Large, High‑Concurrency Pools

Large pools increase lock contention on internal lists. Setting innodb_buffer_pool_instances splits the pool into independent instances, each with its own lists.

[server]
innodb_buffer_pool_instances = 8

If innodb_buffer_pool_size is less than 1 GiB, the setting is ignored and a single instance is used.

Since MySQL 5.7.5, the pool size can be changed at runtime in increments of innodb_buffer_pool_chunk_size (default 128 MiB). The size must be an integer multiple of chunk_size × instances. MySQL automatically adjusts mismatched values, as shown by the following command‑line examples.

# Scenario 1: size not an integer multiple, rounded up
# chunk_size default 128M, instances = 8 → product = 1G
mysqld --innodb-buffer-pool-size=3.5G --innodb-buffer-pool-instances=8
# 3.5G is not a multiple of 1G, server rounds up to 4G

# Scenario 2: chunk_size × instances exceeds size, chunk_size reduced
mysqld --innodb-buffer-pool-size=1G --innodb-buffer-pool-instances=8 \
       --innodb-buffer-pool-chunk-size=256M
# 256M × 8 = 2G > 1G, so chunk_size is adjusted to 1G/8 = 128M

7. Inspecting Real‑Time Status

The command SHOW ENGINE INNODB STATUS displays Buffer Pool metrics:

Buffer pool size (pages)

Free buffers (free list length)

Database pages (total LRU nodes)

Old database pages (old region nodes)

Modified db pages (dirty pages, flush list length)

Pending reads / writes (pages waiting for disk I/O)

Pages made young (old → young promotions)

Buffer pool hit rate (e.g., 998 / 1000 means 99.8 % hit)

A hit rate below 85 % indicates insufficient pool size or frequent full‑table scans.

Conclusion

Slow disk versus fast CPU forces caching; limited cache requires eviction; naïve LRU is polluted by read‑ahead and scans, so InnoDB splits LRU into young and old regions and adds a time‑window filter. Parameters innodb_old_blocks_pct and innodb_old_blocks_time directly address those specific performance issues.