Buffer Pool
The buffer pool caches database pages in memory, absorbing disk latency; hit ratio, dirty-page flushing, and replacement policy determine its effectiveness.
Definition
A buffer pool is the memory region where a database caches fixed-size pages read from disk. Queries operate on pages in the pool; a page miss triggers a read, and a modified page becomes dirty until it is written back. The replacement policy decides which pages to evict when space is needed.
Context and scope
Almost every database read is a buffer pool lookup. Understanding the pool explains why the first query after restart is slow, why a large scan evicts hot pages, and why memory sizing affects both latency and write throughput.
Common misconceptions
- A larger buffer pool always makes the database faster, regardless of working set or write pattern.
- The buffer pool and the operating system page cache are interchangeable, so double caching is harmless.
Anti-patterns
- Sizing the pool to consume all host memory and leaving none for connections, sorting, or the OS.
- Running large sequential scans during peak hours without considering cache pollution.
Related concepts
Related examples
Authoritative source
https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html
How it works
The pool is an array of frames, each holding a page and metadata: page ID, dirty flag, pin count, and access information. A read hashes the page ID, finds a frame, and either returns it or loads the page from disk. A write modifies the in-memory page and records a write-ahead log record before the page is flushed. Dirty pages are written back by background flush or checkpoint activity.
Replacement is usually a variant of LRU or clock. Naive LRU performs poorly under full scans because a large sequential read evicts the entire hot set. Engines add scan resistance through midpoint insertion, frequency sketching, or separate pools for different page types. Some allow pinning small hot tables.
When to use this knowledge
Use it when tuning memory, investigating I/O spikes, or explaining latency after a restart. A working set that fits in the pool serves reads from memory; one that does not will show repeated disk reads. Write-heavy workloads also depend on the pool because dirty pages must be flushed without stalling commits.
Trade-offs
A larger pool improves hit ratio but competes with per-query memory, connections, and the OS. A smaller pool leaves more room for the OS page cache, which may cache the same files and cause double buffering. Aggressive flushing smooths checkpoints but increases I/O; lazy flushing risks a spike when the pool fills with dirty pages. Hit ratio alone can mislead if the misses are cheap sequential reads.
Example and misconception
A analytics query scans a 200 GB table on a 16 GB pool. It evicts hot index pages, and the next transactional query slows down. Adding a separate replica for analytics, or limiting the scan’s buffer usage, protects the operational workload.
The misconception is that a 99 percent hit ratio means the pool is healthy. If the remaining 1 percent are random reads on the critical path, latency can still be terrible. Measure both hit ratio and the cost of misses.