The InnoDB Buffer Pool: Dirty Pages, LRU Eviction, and LSN Checkpointing

Suppose you are tuning a production MySQL server.

Your database host has 128 gigabytes of RAM. Your primary orders table contains 50,000,000 rows stored on disk.

Every time a user loads their dashboard, the server executes a query reading user records. If MySQL fetched 16KB page blocks from physical disk for every single query, your storage controllers would collapse under disk seek latency.

To maximize throughput, MySQL InnoDB allocates up to 80% of system RAM to a memory cache called the Buffer Pool.

Now consider what happens when a developer runs a night batch job:

SELECT COUNT(*) FROM historical_logs WHERE payload LIKE '%error%';

This single query scans 100,000,000 rows across cold table pages.

If InnoDB used a standard Least Recently Used (LRU) cache, that single table scan would sweep every active user page out of RAM, filling the cache with cold log pages that will never be read again.

How does InnoDB protect hot application data against Buffer Pool Pollution?

---

What the Buffer Pool Is Under the Hood

The Buffer Pool is an array of fixed-size 16KB memory page frames allocated in host RAM.

=====================================================================
                    INNODB BUFFER POOL ANATOMY
=====================================================================
[ Frame 0 (16KB) ]  [ Frame 1 (16KB) ]  [ Frame 2 (16KB) ]  ...
Page: Space 0, Pg 42 Page: Space 0, Pg 89 Page: Space 1, Pg 12
State: Dirty        State: Clean        State: Free
=====================================================================

Every page frame inside the Buffer Pool exists in one of three states:

1. Free Page: Unused memory frame ready to store data.
2. Clean Page: Caches a 16KB disk page that matches the byte data on disk.
3. Dirty Page: Caches a 16KB disk page modified by in-memory UPDATE/INSERT operations that has not yet been written back to physical disk storage.

---

The Three Management Lists

To track page frames, InnoDB maintains three internal doubly linked lists of control blocks:

1. FREE LIST:  [ Frame 5 ] <---> [ Frame 8 ] <---> [ Frame 12 ]
   (Tracks unallocated memory frames)

2. FLUSH LIST: [ Frame 0 (LSN: 1040) ] <---> [ Frame 3 (LSN: 1080) ]
   (Tracks dirty pages ordered by oldest modification LSN)

3. LRU LIST:   [ Head (Young 62%) ] <--- Midpoint ---> [ Tail (Old 38%) ]
   (Tracks page access frequency for memory eviction)

---

Buffer Pool Pollution & The Midpoint LRU Strategy

A standard LRU cache places newly read pages at the absolute head of the list.

If a query executes a full table scan, millions of cold pages flood the head of the list, pushing out hot user session pages.

InnoDB solves this using a Modified Midpoint LRU List.

                    INNODB MIDPOINT LRU LIST (100%)
                                 
  Head (Most Recently Used)                          Tail (Eviction Target)
+------------------------------------+---------------+---------------------+
| YOUNG SUBLIST (62% of List)        | MIDPOINT (38%)| OLD SUBLIST (38%)   |
| Hot pages accessed multiple times  | Insertion Pt  | Newly fetched pages |
+------------------------------------+---------------+---------------------+
                                             ^
                                             |
                              Newly fetched pages enter HERE!

The Insertion & Promotion Rules:

1. New Page Read: When a query reads a page from disk, InnoDB inserts the page at the Midpoint (the head of the Old Sublist, occupying 38% of the list), NOT at the head of the LRU list!
2. The Time Gate (innodb_old_blocks_time): A page sitting in the Old Sublist must be accessed again after a time threshold (innodb_old_blocks_time = 1000ms) before it can be promoted to the Young Sublist.
3. Scan Protection: Full table scans read cold pages sequentially within milliseconds. Because subsequent reads occur within the 1,000ms window, the pages are never promoted to the Young Sublist. They age out at the tail of the Old Sublist and get evicted without polluting hot cache memory.

---

Page Flushing & Fuzzy Checkpointing

How do dirty pages get written back to disk without locking transaction execution?

InnoDB uses asynchronous Page Cleaner Threads that perform Fuzzy Checkpointing.

BUFFER POOL (RAM)                              PHYSICAL DISK (.ibd files)
+------------------------+                     +-------------------------+
| Dirty Page 42          | --(Page Cleaner)--> | Page 42                 |
| (PageLSN: 10450)       |   Background Thread | (Updated to LSN 10450)  |
+------------------------+                     +-------------------------+

The Flushing Triggers:

1. Flush List Flushing: Page cleaner threads periodically flush dirty pages with the oldest modification LSNs from the Flush List to advance the checkpoint LSN.
2. LRU List Flushing: If free page frames drop below a threshold, cleaner threads flush dirty pages near the tail of the LRU list to make frames available.
3. High Dirty Page Ratio: If dirty page percentage exceeds innodb_max_dirty_pages_pct (default: 90%), flushing speeds up aggressively.

---

Production Configuration: Sizing the Buffer Pool

For dedicated MySQL database servers, set innodb_buffer_pool_size to 50% – 75% of total system RAM:

# /etc/my.cnf Configuration
[mysqld]
# Allocate 64GB of RAM on a 96GB server
innodb_buffer_pool_size = 68719476736

# Split Buffer Pool into 8 instances to reduce mutex lock contention
innodb_buffer_pool_instances = 8

# Protect against table scan pollution (1000ms delay before LRU promotion)
innodb_old_blocks_time = 1000

Multiple Buffer Pool Instances

When multiple CPU cores execute queries concurrently, acquiring a single lock on the Buffer Pool creates mutex contention.

Setting innodb_buffer_pool_instances = 8 splits the Buffer Pool into 8 independent memory regions, each with its own locks and LRU lists, allowing parallel thread access.

---

Quick Summary

• The Buffer Pool caches 16KB disk pages in RAM to eliminate random disk I/O.
• The Free List tracks empty page frames; the Flush List tracks dirty pages; the LRU List tracks eviction targets.
• Midpoint LRU insertion places new pages at the 38% mark, requiring a 1,000ms delay (innodb_old_blocks_time) before promotion to protect hot cache memory against full table scans.
• Asynchronous Page Cleaner threads write dirty pages back to disk via Fuzzy Checkpointing.

References & Further Reading

1. Mohan, C., Haderle, D., Lindsay, B., Pirahesh, H., & Schwarz, P. (1992). ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. ACM Transactions on Database Systems (TODS), 17(1), 94–162.
2. Gray, J., & Reuter, A. (1992). Transaction Processing: Concepts and Techniques (Chapter 10: Recovery Manager). Morgan Kaufmann.

---