Understanding MySQL Buffer Pool: Memory Structure, Page Types, and Management Strategies

Buffer Pool is a crucial component of MySQL where all insert, update, and delete operations are performed in memory before being flushed to disk.

Data stored on disk is first read into the Buffer Pool to avoid costly random I/O; subsequent queries can then access the data directly from memory.

The overall workflow is illustrated by a diagram showing data loading from disk to the Buffer Pool, processing, and eventual write‑back.

2.1 Data Page

MySQL stores rows logically in tables, but physically it works with fixed‑size data pages (default 16 KB). When a row is accessed, MySQL locates the containing data page and loads it into the Buffer Pool.

2.2 Cache Page

Each data page loaded into the Buffer Pool has a corresponding cache page of the same size, plus extra metadata (tablespace ID, page number, etc.). At MySQL startup, the Buffer Pool is allocated based on innodb_buffer_pool_size , and the memory region is divided into cache pages and their descriptors.

3.1 Page Types

Three page states exist in the Buffer Pool:

Free Page : not used, resides in the Free list.

Clean Page : used but unmodified, resides in the LRU list.

Dirty Page : used and modified, data differs from disk; appears in both LRU and Flush lists until flushed.

3.2 Linked Lists

3.2.1 Free List

The Free list is a doubly‑linked list of descriptors for unused cache pages. When a data page needs a cache slot, MySQL takes a descriptor from the Free list, loads the data, and removes the descriptor from the list.

A hash table (key = tablespace ID + page number, value = cache page address) determines whether a page is already cached.

3.2.2 Flush List

The Flush list records descriptors of dirty pages that need to be written back to disk. After a dirty page is flushed, its descriptor is removed from both Flush and LRU lists and returned to the Free list.

3.2.3 LRU List

The LRU (Least Recently Used) list orders cache pages by recent access. When a page is accessed, its descriptor moves to the head of the LRU list; the tail pages are candidates for eviction when the Buffer Pool is full.

MySQL also employs pre‑read to improve performance: linear pre‑read loads the next extent (64 pages) after a threshold of sequential accesses, while random pre‑read loads remaining pages of the current extent (deprecated since MySQL 5.5).

5.1 Issues with LRU

5.1.1 Pre‑read Inefficiency

If pre‑read loads pages that are never accessed, useful cache space can be wasted.

5.1.2 Buffer‑Pool Pollution

Large scans can fill the Buffer Pool with irrelevant pages, evicting useful data and degrading performance.

5.2 Hot‑Cold Separation LRU

MySQL splits the LRU list into a cold region and a hot region (controlled by innodb_old_blocks_pct , default 37%). New pages start in the cold region; after a configurable time ( innodb_old_blocks_time , default 1000 ms) and a subsequent access, they move to the hot region. Within the hot region, only pages in the last 75 % are promoted to the head on access.

7.1 Concurrency

Only one thread may operate on the Buffer Pool at a time; the pool is protected by a lock to ensure consistency. To improve scalability, MySQL allows multiple Buffer Pool instances (configured via innodb_buffer_pool_instance ), each with its own set of pages but sharing the same hash table for page lookup.

7.2 Dynamic Buffer‑Pool Resizing

MySQL uses a chunk mechanism to grow the Buffer Pool without requiring a single large contiguous memory allocation. A chunk (default 128 MB, configurable via innodb_buffer_pool_chunk_size ) is the basic allocation unit; adding or removing chunks adjusts the pool size dynamically.

When the pool is enlarged, MySQL simply allocates additional chunks; when shrunk, it releases unused chunks, avoiding costly data copying.

In summary, the Buffer Pool consists of Free, Flush, and LRU linked lists that manage page states, pre‑read strategies, hot‑cold separation, concurrency, and dynamic resizing to provide efficient in‑memory data handling for MySQL.