A Deep Dive into the InnoDB Buffer Pool

To reduce disk IO and improve performance, the InnoDB storage engine performs all DML operations inside the in-memory BufferPool, with an asynchronous flushing strategy writing the modified data to disk later. This post walks through the BufferPool's data structures, how it manages pages, and its eviction and flushing strategies.

Official BufferPool documentation:

MySQL :: MySQL 8.0 Reference Manual :: 15.5.1 Buffer Pool

Why design a BufferPool at all? Anyone with a computer science background knows memory and disk are worlds apart in speed — from the CPU's perspective, disk transfer and response times are painfully slow. So MySQL designed the BufferPool to mirror the real data on disk, managing and manipulating data entirely in memory, which makes DML execution take off. As for how the data in the BufferPool survives crashes and power loss (physical damage is another story — if the disk itself is toast, nothing can help), I covered that in an earlier post:

The MySQL Execution Pipeline

Buffer Pool Overview

InnoDB accesses data in units of Pages, each 16KB by default, and the Buffer Pool exists to manage and cache these Pages. InnoDB carves out a contiguous block of memory for the Buffer Pool and splits it into multiple Buffer Pool Instances for better management. Each Instance is the same size, and an algorithm guarantees that a given Page always lands in one specific Instance. This multi-Instance design improves the Buffer Pool's concurrency.

Each Buffer Pool Instance maintains its own set of management structures. InnoDB reads data from disk files into memory in 16KB Pages and caches them via an LRU List: frequently accessed Pages sit near the front of the LRU List, rarely accessed ones near the back. When accessing a Page, InnoDB first looks in the Buffer Pool; on a miss it reads the disk data file, then puts the fetched Page into the LRU List. When an Instance has no free Pages left, Pages on the LRU List get evicted.

The Buffer Pool also contains a fair amount of Page compression logic — compressing the actual 16KB Pages down to 8KB, 4KB, 2KB, or 1KB. We'll skip that here and stick with the default 16KB Pages to trace the main logic.

So the BufferPool is a layered structure:

• The BufferPool consists of multiple Buffer Pool Instances.
• Each Buffer Pool Instance consists of multiple Buffer Chunks (128MB each by default); a Buffer Chunk manages a contiguous memory region called Buffer Chunk Memory.
• Each Buffer Chunk consists of multiple Buffer Pages (Pages for short, 16KB by default).

This layering exists to reduce mutex contention under high concurrency.

This post focuses on the BufferPool's data structures, how it manages pages, and its eviction and flushing strategies. Start with the official LRU data-caching diagram:

The Buffer Pool's default total size is 128M, adjustable via innodb-buffer-pool-size = xxxxxxx as needed (the official recommendation for a dedicated database server is to give it most of the physical memory). It is divided into a New Sublist and an Old Sublist — sound like the JVM's young and old generations? The idea is indeed similar: the New Sublist holds the most active data, the Old Sublist holds infrequently accessed data, and the Old Sublist takes up 3/8 of the BufferPool by default.

Since memory is scarce, you can't load every table on disk into memory — an eviction strategy is required. The BufferPool uses the LRU algorithm plus MySQL's own variant logic to evict in-memory data, also known as hot/cold data handling: frequently accessed data drifts toward the New Sublist, infrequently accessed data toward the Old Sublist, and when memory pressure is high, data in the Old Sublist is evicted first. A newly read page is placed at the Head of the Old Sublist; if it gets accessed often it moves into the New Sublist, otherwise it stays in the Old Sublist.

The BufferPool also contains a region called the ChangeBuffer, as shown:

The ChangeBuffer caches DML changes whose target pages are not in the BufferPool, sparing every modification from first pulling the page off disk. When the corresponding page is later read into memory, the buffered changes get merged in, reducing random IO. By default the ChangeBuffer takes 25% of the BufferPool, adjustable up to a maximum of 50%.

A fairly complete picture of the BufferPool's structure (a few lists are omitted, such as the compression lists):

Before we start, a few prerequisite concepts:

• Data page: a page that has been loaded with data from disk.
• Dirty page: a page that has been modified and needs to be flushed back to disk.
• Blank page: a page holding no data yet.
• Control block: the BufferPool page pointer stored in nodes of the various management lists.
• Buffer page: the general term for any page in the BufferPool (all pages are 16KB).

Configuring Multiple Buffer Pool Instances

When the MySQL server starts up, it requests the BufferPool's memory from the operating system. Under multithreading, every list requires locking, and when the BufferPool is very large and concurrent access is very heavy, a single BufferPool becomes a throughput bottleneck. So the BufferPool is split into several smaller BufferPools, called Buffer Pool Instances, each independently allocating memory and managing its own lists, so concurrent threads don't interfere with each other. The instance count is configurable:

# Create 2 buffer pool instances
innodb_buffer_pool_instances = 2

# Memory per instance = total size divided by instance count
# innodb_buffer_pool_size / innodb_buffer_pool_instances

Since creating and managing multiple instances carries its own overhead, MySQL stipulates: when innodb_buffer_pool_size is below 1G, there is only one instance by default and setting more has no effect; only above 1G is configuring multiple instances encouraged.

How the Buffer Pool Manages Pages

Data pages are not scattered haphazardly through the BufferPool — they are organized by a hash table and several lists to support the operations described earlier. The nodes in these lists hold the Page Descriptor:

1. page hash (fast access): all pages are organized by a hash table called the page hash, used to quickly locate a page in the buffer pool; the page hash key is the page id.
2. free list (page allocation): when loading a page into the buffer pool, a buf_block_t must be allocated as its page descriptor. How do you find a free, unoccupied buf_block_t? They all hang on the free list.
3. LRU list (page eviction): buffer pool space is finite; when there is no room for pages loaded from disk, some existing pages must be evicted, using the LRU algorithm.
4. flush list (dirty page writeback): when a page becomes dirty, the page cleaner thread must periodically write it back to disk; all dirty pages live on the flush list.

Managing the Free List

When the MySQL server first starts, the BufferPool must be initialized: memory for the BufferPool is requested from the OS and divided into pairs of control blocks and buffer pages. At this point no real disk pages have been cached in the BufferPool (nothing has needed them yet); as the program runs, pages from disk get cached in over time. Which raises a question: when reading a page from disk into the BufferPool, which buffer page slot should it go into? Put differently, how do we tell which buffer pages in the BufferPool are free and which are in use?

Best to record somewhere which buffer pages are available — and this is where each buffer page's control block earns its keep: the control blocks of all free buffer pages are linked into a list as nodes, called the Free list. In a freshly initialized BufferPool, every buffer page is free, so every buffer page's control block joins the Free list.

To manage the Free list better, a dedicated base node is defined, containing the list's head node address, tail node address, and the current node count, among other information. Note that the base node's memory is not part of the big contiguous allocation made for the BufferPool — it is a separately allocated chunk of memory.

With the Free list in place, things get easy: whenever a page needs to be loaded from disk into the BufferPool, take a free buffer page from the Free list, fill in its control block's information (the tablespace the page belongs to, the page number, and so on), and remove that control block from the Free list, marking the buffer page as in use. Be clear that what we actually take from the list is a control block — the real page is reached through the control block. Likewise, "iterating over the buffer pages in the BufferPool" really means "iterating over the buffer pages' control blocks."

In short: manage the Free list well and you always know which pages in the BufferPool are free.

Page Hash: Hashing the Buffer Pages

When data in a page needs to be accessed, the page is loaded from disk into the BufferPool; if it's already there, it can simply be used. But how do we know whether the page is in the BufferPool? Surely not by walking through every buffer page one by one — with that many buffer pages, a full scan would be exhausting.

Think about it: we actually locate a page by tablespace number + page number, so tablespace number + page number acts as a Key, and the buffer page's control block is the corresponding Value. Finding a Value quickly by Key — that's naturally a hash table.

So a hash table is built with tablespace number + page number as the Key and the buffer page control block's address as the Value. When a page's data is needed, first look up the hash table by tablespace number + page number: if a corresponding buffer page exists, use it directly; if not, pick a free buffer page from the Free list and load the corresponding page from disk into that slot.

Managing the LRU List

The LRU list is a way of managing the BufferPool's cache space. Think about it: if you keep loading data pages into the cache, sooner or later it fills up, and there must be a mechanism to release the pages nobody needs — that mechanism is the LRU (least recently used) algorithm.

You can picture the algorithm as a linked list split into two parts (MySQL's variant): one part stores very frequently used buffer pages — the hot data — called the Young region (New Sublist); the other stores infrequently used buffer pages — the cold data — called the Old region (Old Sublist). InnoDB splits the LRU list proportionally, and you can check the Old region's share via the innodb_old_blocks_pct parameter.

With these two regions, InnoDB's designers could optimize for BufferPool hit-rate scenarios:

• Optimizing for read-ahead pages that may never be accessed again. The rule is that when a page from disk is first loaded into a buffer page, its control block goes to the head of the Old region. That way, pages read ahead into the BufferPool but never subsequently accessed drift out of the Old region without disturbing the frequently used buffer pages in the Young region.
• Optimizing for full table scans that access a burst of low-frequency pages in a short time. During a full table scan, pages loaded for the first time do land at the head of the Old region, but they get accessed again immediately afterward, and each access would move the page to the head of the Young region — still squeezing out the genuinely hot pages. The designers reasoned that during a full table scan, even if a page holds many rows and each row read counts as one page access, the whole process takes very little time. So the rule is: on the first access to a buffer page in the Old region, record the access time in its control block; if subsequent accesses fall within a certain interval of that first access, the page does not move from the Old region to the head of the Young region — only accesses outside the interval move it. This interval is controlled by the system variable innodb_old_blocks_time.

Managing the Flush List

If you modify the data in a buffer page, it no longer matches the page on disk — such a buffer page is called a dirty page. You could of course flush it to the corresponding disk page immediately after every modification, but writing to disk that frequently would kill performance. So each modification does not trigger an immediate flush; the page is flushed at some point in the future instead.

But if you don't flush immediately, how do you later know which pages in the BufferPool are dirty and which have never been modified? Hence another list has to be created to hold dirty pages: the control block of every modified buffer page joins this list as a node. Since all these buffer pages need to be flushed to disk, it is called the Flush list. Its construction is much like the Free list. Also note: a free buffer page is definitely not dirty, and a dirty page is definitely not free — meaning a given buffer page's control block cannot be a node of both the Free list and the Flush list at once; it belongs to at most one of them.

In short: the Free list is simply the list of all free pages in the cache, while the Flush list is the list of all modified pages (dirty pages).

The Dirty Page Flushing Mechanism

Dirty pages are flushed back to disk in the following situations:

1. The Redo Log is full. As the durability guarantee, the Redo Log must record every update. If it runs out of space, pages must be flushed to sync data to disk, after which the Redo Log's CheckPoint pointer moves forward to free up space. If memory is not under pressure at that point, the flushed pages don't need to be evicted.

2. Memory can't hold any more data pages. Queries read data pages from disk into memory before returning; if memory runs out, the least recently used pages must be evicted, and if an evicted page is dirty, it must be flushed to disk first.

3. The database is idle. When the database considers itself idle, it flushes dirty pages; if memory is not under pressure, flushed pages are not evicted.

4. The database shuts down normally. On shutdown, pages are flushed as well, persisting prior modifications to disk.

Wrapping Up

The BufferPool is InnoDB's core design for trading memory for IO: data is cached in memory in 16KB Pages, and DML operations modify memory first, then land on disk asynchronously. To reduce lock contention under concurrency it is split into multiple Instances; internally, the Page Hash provides fast lookup, while the Free, LRU, and Flush lists handle free page allocation, hot/cold data eviction, and dirty page writeback respectively. The LRU's New/Old partitioning plus the innodb_old_blocks_time window solves the problem of read-ahead and full table scans washing out hot data. Once you understand how these lists cooperate, the BufferPool's overall machinery becomes clear.