The Page Cache of the Engine
The buffer pool is the database engine memory area that caches disk pages. Reads and writes go through it, so a page already cached avoids a disk trip. Because memory is finite, adding a new page sometimes requires evicting another.
Naive LRU and Its Flaw
A simple least recently used policy evicts the page untouched for the longest time. It works until a large scan sweeps through many pages once. That scan touches every page recently, evicting genuinely hot pages and polluting the cache with data used once.
Scan Resistant Policies
Real engines use policies that resist this.
- A two segment LRU splits the pool into a probationary part and a protected part. A page entering the first time goes to probation, and only a second access promotes it to protected.
- This way a one time scan parks pages in probation and they leave quickly, sparing the hot pages in the protected segment.
- Variants track frequency as well as recency so often used pages survive.
Dirty Pages Complicate Eviction
A page that has been modified is dirty and cannot simply be dropped. It must first be written to disk, so eviction interacts with checkpointing and the WAL.
Key idea
The buffer pool caches pages and uses scan resistant eviction such as two segment LRU so a one time scan cannot flush out genuinely hot pages.