Everyone knows MySQL ultimately has to persist data to disk. And compared with memory, disk access is slow—painfully slow, really. But in day-to-day work, SQL statements usually return much faster than that fact alone would suggest, whether you are reading or writing.
You might say, “That’s because of indexes.” Indexes do help, but index files also live on disk. Looking them up still involves disk I/O. And if the same row is accessed over and over, repeatedly going back to disk would be expensive.
So the obvious idea is: keep frequently used data in memory. That is exactly where the buffer pool comes in.
Getting to know the buffer pool
At its simplest, the buffer pool is a chunk of memory used by InnoDB as a cache. Its job is straightforward: avoid hitting the disk every single time a row needs to be read or modified.
If MySQL had to read from disk for every operation, performance would collapse quickly. The buffer pool exists to keep commonly used pages in memory so they can be reused at memory speed instead of disk speed.
From a high-level architecture view, the buffer pool sits between SQL execution and the underlying storage, absorbing a large share of reads and writes before they become physical disk operations.
Inside the buffer pool, it is not just data pages and index pages. It also contains several other internal structures used to manage cached content efficiently.
What the buffer pool does during SQL execution
The easiest way to understand the buffer pool is to look at what happens when a SQL statement runs.
Reads
For a read operation, if the page containing the target row is already in memory, MySQL can return the result directly from the buffer pool. No disk read is needed.
If that page is not in memory, MySQL loads the corresponding data page from disk into the buffer pool first, and then returns the result.
Writes
Writes work similarly. If the page containing the row to be modified is already in the buffer pool, MySQL updates it there and continues with the rest of the write process.
If the page is not in memory, MySQL first reads that page from disk into the buffer pool, then performs the modification.
That is the main reason SQL often feels much faster than raw disk speed would imply. The buffer pool cuts down heavily on disk I/O. When the needed page is already cached, MySQL can work directly in memory and respond much more quickly.
Read-ahead: reducing unavoidable I/O
Even with caching, some disk reads cannot be avoided. So the next question becomes: if I/O must happen, can it at least happen more efficiently?
That is where read-ahead comes in.
In InnoDB, read-ahead is an optimization that preloads multiple pages into the buffer pool before they are explicitly requested. The idea is simple: disk access happens in page-sized units. You can think of a page as a fixed-size block of data, commonly 16 KB. Since MySQL already reads data page by page, if upcoming requests are likely to need nearby pages, loading them in advance can reduce future disk I/O.
So if the next needed data happens to be in a page that was preloaded earlier, MySQL can skip another trip to disk.
The page size can be checked from the command line, as shown below:
Why the buffer pool cannot hold everything
It is tempting to ask: if memory is so much faster, why not just put all data in the buffer pool?
Because the buffer pool is limited in size. Memory is not infinite, and the buffer pool has to decide which pages deserve to stay and which ones should be removed when space runs out.
That turns buffer pool management into a cache replacement problem:
- how new pages should enter the pool
- how existing pages should be ordered
- which pages should be evicted when space is needed
- how to keep frequently used pages from being pushed out too easily
Traditional LRU and how it works
A classic way to manage cached pages is LRU, short for Least Recently Used.
The idea is familiar: pages used recently should stay, and pages not used for a long time should be candidates for eviction.
With an LRU list, page management works like this.
1. The page is already in the buffer pool
If the needed page is already cached, it is moved to the head of the LRU list. No disk read is required, and no other page needs to be evicted.
For example, if the data being accessed is in page 6, page 6 is simply moved to the front of the list.
2. The page is not in the buffer pool
If the needed page is not cached, MySQL must read it from disk, place it at the head of the LRU list, and evict a page from the tail if the buffer pool is full.
For example, if page 60 is requested and is not in the buffer pool, page 60 is loaded and inserted at the front, while the page at the end—say page 17—is evicted.
At first glance, that seems reasonable. But for the buffer pool, plain LRU has two major weaknesses.
Problem 1: useless read-ahead pages
Read-ahead can preload neighboring pages into memory. Imagine pages 20 and 21 are brought in together, but only page 20 is ever accessed while page 21 is never touched.
Under a simple LRU strategy, both pages still enter the list in a favorable position. To make room for them, pages at the end of the list may be evicted—even if those older pages were genuinely useful and accessed often.
That means preloaded pages that end up unused can displace pages that matter more. This is a classic case of read-ahead failure.
Problem 2: buffer pool pollution
Another bad case appears during large scans, especially full table scans.
A single SQL statement may read a large amount of data, causing many pages to flood into the buffer pool. Those pages can push out most or all of the pages that were already there.
If the scanned pages are not going to be reused, the buffer pool ends up filled with low-value data while genuinely hot pages get evicted. This is known as buffer pool pollution, and it can cause MySQL performance to drop sharply.
So while traditional LRU is easy to understand, it is not enough for the real behavior of a database cache.
MySQL’s answer: separating hot and cold data
To solve these issues, MySQL does not rely on a plain LRU list. Instead, InnoDB uses an LRU-based design that separates hot and cold data.
In practice, the LRU list is divided into two regions:
- a hot area
- a cold area
New pages enter the cold area first
When a page is loaded into the buffer pool for the first time, it does not go straight into the hot area. Instead, it is placed at the head of the cold region.
Only if that page is accessed again after 1 second—controlled by the innodb_old_blocks_time parameter—will it be promoted to the head of the hot region.
This delay matters.
If a page is loaded and touched once immediately, then never used again, promoting it to the hot area right away would waste valuable hot-cache space. By requiring a second access after a time threshold, MySQL can better distinguish truly useful pages from one-off pages introduced by read-ahead or large scans.
If such a page is never accessed again after that interval, there is no strong reason to keep it in the hot area. When space is tight, pages in the cold area can be evicted first.
Even hot pages are not moved blindly
There is another important optimization.
Suppose a page is already in the hot region. Should every access move it straight to the very front of the list?
That would be excessive. Hot pages are accessed frequently by definition. If every access triggered a full repositioning to the head, the hot region would churn constantly and create unnecessary overhead.
So InnoDB applies a more restrained rule: when a page in the back 3/4 of the hot region is accessed, it is moved to the head of the list. But if the page is already in the front 1/4, it is left where it is.
This reduces pointless movement while still keeping genuinely active pages near the front.
Why the buffer pool matters so much
The buffer pool is one of the main reasons MySQL can deliver fast SQL execution even though persistent storage lives on disk.
It works because:
- frequently used data pages can be served directly from memory
- writes can often modify in-memory pages first instead of repeatedly going to disk
- read-ahead helps reduce future I/O by bringing in nearby pages ahead of time
- improved LRU management prevents one-time reads and large scans from destroying cache quality
In short, the buffer pool is not just a memory cache. It is a carefully managed working area that decides which pages are worth keeping close and which ones can be pushed out without hurting performance.