How InnoDB Buffer Pool Manages Pages and Flush Lists for Optimal Performance

1. Introduction to InnoDB Buffer Pool

Buffer Pool is a large memory area in InnoDB that caches table and index data. Accessing memory is much faster than disk, so Buffer Pool uses three types of pages and linked lists to keep hot data from being evicted.

This article focuses on the three page types and their lists.

2. Three Page Types

Free Page

A page not used, located in the Free list.

Clean Page

A used page that has not been modified, located in the LRU list.

Dirty Page

A used page that has been modified, data differs from disk. After being flushed to disk it becomes clean; dirty pages appear in both LRU and Flush lists.

3. Three Linked Lists

LRU List

LRU (least recently used) list is managed by the LRU algorithm. It is divided into Young (New Sublist) and Old (Old Sublist) parts, each with a head and tail.

By default Old list occupies 3/8 of LRU, controlled by innodb_old_blocks_pct (default 37). New pages are inserted at the midpoint between Young tail and Old head.

Frequent access moves pages toward the head of the Young list; pages read by prefetch and not accessed soon move toward the Old tail and may be evicted.

If a page is in the Young list, it moves to the head only after it passes roughly one‑quarter of the Young list length, reducing LRU modifications. innodb_old_blocks_time controls how long a page must stay in the Old list before it can be moved back to Young after access, protecting the Young list from short‑lived pages.

During full table or index scans many pages are inserted at the midpoint and quickly become cold; adjusting innodb_old_blocks_time helps keep truly hot pages in Young. innodb_old_blocks_time is in milliseconds, default 1000. Larger values make moving pages from Old to Young harder, causing more pages to age and be evicted.

When the buffer pool cannot hold all pages, reducing innodb_old_blocks_pct limits the proportion of single‑read pages (e.g., setting it to 5% limits them to 5% of the pool). For frequent small‑table scans, a larger value such as 50% may be appropriate.

Flush List

Stores dirty pages; also appears in LRU list.

Ordered by oldest_modification, larger values at the head.

When a page is modified, it is added to the Flush list via a mini‑transaction.

If the page is already dirty, it is not added again.

Page Cleaner thread scans from the tail, writes dirty pages to disk, advancing the checkpoint.

Free List

Contains free pages allocated at startup.

During SQL execution, if a free page is needed and the Free list is insufficient, pages are flushed from LRU and Flush lists to create free pages; otherwise a page is taken from the Free list and added to LRU.

4. Differences Between LRU and Flush Lists

LRU flush was triggered by user threads before MySQL 5.6.2; Flush list flush is handled by the InnoDB background thread (Page Cleaner).

LRU flush writes dirty pages at the tail to free pages quickly; Flush list flush advances the checkpoint for faster crash recovery.

LRU flush moves flushed pages from LRU to Free list; Flush list flush does not change the page’s position in LRU.

LRU flush writes a relatively small, fixed number of pages; Flush list flush size varies with workload.

Every page in Flush list is also in LRU list, but not vice versa.

5. Conditions That Trigger Dirty‑Page Flush

Redo log is nearly full; to avoid loss of unflushed dirty data, pages are taken from Flush list.

Page Cleaner needs free pages; if the tail page is dirty it must be flushed first.

Too many dirty pages ( innodb_max_dirty_pages_pct default 75%); when exceeded, MySQL forces a flush, with low‑water mark innodb_max_dirty_pages_pct_lwm controlling pre‑flush.

Normal shutdown flushes all dirty pages to disk.

InnoDB aims to occupy as much memory as possible; when a needed page is not in the Buffer Pool, a free page is allocated, possibly evicting a page from the LRU tail. Clean pages can be reused directly; dirty pages must be flushed first.

Performance can suffer if many pages must be evicted for a query or if checkpoint cannot keep up with write load.

The innodb_io_capacity variable defines the I/O capacity for background tasks such as dirty‑page flushing and change‑buffer merging.

6. Summary

The three page types and their linked lists ensure hot data stays in memory while cold data is evicted, improving query speed; the flushing strategies also speed up recovery and protect data integrity.