Buffer pool

The buffer pool is the database's own cache of pages. A read asks the pool for a page. If the page is there, the read is a memory access. If not, the pool reads it from disk, which costs far more. When the pool is full, it must evict a page to make room.

The database manages this itself instead of relying on the operating system's page cache. It knows which pages are about to be needed, whether a page holds uncommitted changes, and when it must be written back. That control is why serious databases don't just use mmap and trust the kernel. The OS cache is covered in page cache and fsync.

The big idea

A buffer pool has to answer two questions well:

1. Which page to evict? Ideally the one that won't be needed for the longest time. The optimal rule isn't computable, so engines approximate it.
2. How to avoid evicting the wrong pages? A single large scan can read millions of pages once each, and push out the hot pages that point lookups use every second.

Walking through it

The classic approximation is LRU (least recently used): evict the page untouched for the longest time. It works well until a scan arrives. A full table scan touches each page once, so each new page is "recent" and pushes out a hot page that was used a moment earlier.

Engines handle this in two common ways:

• Clock sweep. Pages sit on a ring with a "recently used" bit. The sweep hand clears bits as it passes. A page is evicted only when its bit is already clear. It approximates LRU cheaply, with no list reordering on every access.
• Midpoint insertion. The list is split into a "young" hot region and an "old" region. A page read from disk enters at the midpoint, not the head. It must be touched again to move to the young region. A scan's pages therefore never reach the hot region. MySQL InnoDB uses this scheme.

Dirty pages add another rule. A page modified in memory isn't written to disk until the log has been flushed for its changes, as covered in the write-ahead log. Eviction therefore can require a disk write first, and a pool full of dirty pages stalls reads.

Sizing the pool

Set the pool to hold the hot working set, not the whole database. Measure the hit ratio, which is the share of page requests served from memory. Compare the cost of the two paths, using rough figures for a memory hit and a disk read on NVMe:

memory hit   ≈ 0.1 µs
disk read    ≈ 10 µs

At a 99 percent hit ratio, the average read costs about 0.1 × 0.99 + 10 × 0.01 ≈ 0.2 µs. At 90 percent it costs about 0.1 × 0.9 + 10 × 0.1 ≈ 1.1 µs, which is about ten times as slow. Small drops in hit ratio hurt more than they seem. Spinning disks make each miss far worse.

Common mistakes

• Sizing the pool at a fraction of RAM without checking the hit ratio.
• Running a large batch export on the primary database. Its scan evicts the hot pages, and latency spikes for everyone.
• Assuming the OS page cache is enough, and counting the same memory twice in a container.

Check yourself

• Why does plain LRU suffer under a full table scan? Answer: every scanned page is new and "recent", so it pushes out hot pages that were used a moment earlier.
• Why does a page with changes need a log flush before eviction? Answer: the log must reach disk before the page, so a crash can replay the change.

Key takeaways

• The buffer pool caches pages, and a read is only as fast as its hit ratio.
• Clock sweep and midpoint insertion keep large scans from evicting hot pages.
• A dirty page must be logged before it can be written and evicted.
• Size the pool to the hot working set, and watch the hit ratio.

Further learning

Next, see the write-ahead log, which makes the cached changes durable.