Buffer Pool Churn

A buffer pool caches hot pages in memory so most reads never touch disk. When the working set the workload actually touches exceeds the buffer pool size, pages are evicted and re-fetched repeatedly: churn. Nothing crashes and no error is logged, but query latency degrades and read I/O rises, because reads that used to be memory hits become disk reads on a recurring basis.

A buffer pool, PostgreSQL's shared_buffers or InnoDB's buffer pool (innodb_buffer_pool_size), holds a bounded set of pages in memory so repeated access to the same data does not repeatedly cost a disk read. It works as intended as long as the working set, the set of pages the workload actually touches within a typical time window, fits inside it. When the working set grows past the buffer pool size, the engine must evict a page to make room for a new one, and if the evicted page is one the workload will need again shortly, the next access to it is a disk read that would previously have been a memory hit. This is churn: pages cycling in and out of the pool faster than the workload's natural access pattern would otherwise require. It shows up as degraded query latency and increased disk read I/O even though nothing in the system is misconfigured or broken in the ordinary sense; the buffer pool is doing exactly what it is supposed to do with too little room to do it.

Churn is distinct from disk_io_saturation. Disk I/O saturation is a capacity ceiling: the storage device cannot sustain the I/O volume being asked of it, regardless of why that I/O is being generated. Buffer pool churn is a cache dynamics problem: the I/O volume itself is elevated because pages that should be cache hits are becoming cache misses, and it can degrade latency well before the underlying disk is anywhere near its IOPS or throughput ceiling. Fixing disk_io_saturation means adding I/O capacity or reducing I/O demand; fixing buffer pool churn means either growing the cache, shrinking the working set, or protecting the cache from being swept by a single operation, three different levers.

The specific, common trigger is OLTP/analytics interference on a single instance. A large analytical scan, a reporting query, a batch export, an ad hoc aggregation, reads far more distinct pages than a typical OLTP transaction, often close to an entire table. If the engine treats that scan like any other read and lets it occupy buffer pool slots on the same terms as everything else, one pass of the scan can evict most or all of the resident working set of hot OLTP pages, indexes and heap pages that ordinary transactional queries hit on nearly every request. Immediately after the scan, those OLTP queries stop being cache hits and start being disk reads, producing a latency spike for traffic that has nothing to do with the scan itself and started well after it. The scan finishes in bounded time; the OLTP latency degradation continues until the working set re-warms under normal traffic, which can take longer than the scan did.

Both major engines defend against exactly this, though by different mechanisms. PostgreSQL uses a buffer access strategy, informally the buffer ring strategy, for large sequential scans, bulk reads, and VACUUM: rather than letting such an operation claim pages across all of shared_buffers, the engine cycles it through a small, bounded ring of buffers, reusing the same handful of pages instead of sweeping the whole pool. The mechanism is real and documented in the PostgreSQL source (the BufferAccessStrategy machinery in the buffer manager); exact ring sizes are version-dependent internals and should be verified against the deployed version rather than treated as a fixed constant. InnoDB's defense is a young/old sublist split within its buffer pool LRU list. A page read into the buffer pool first lands in the old sublist and is only promoted to the young sublist, the end that survives eviction pressure, if it is accessed again after innodb_old_blocks_time milliseconds (default 1000ms) have passed since the first read; the old sublist itself reserves innodb_old_blocks_pct percent of the LRU (default 37) as of current MySQL versions. A large sequential scan reads each page once, so its pages never clear the innodb_old_blocks_time bar and are evicted from the old sublist without ever displacing the young, frequently-reused pages that OLTP traffic depends on. Both mechanisms exist for the same reason: to keep a single large, mostly-one-shot scan from acting as an eviction bomb against a cache that many unrelated queries depend on.

These defenses reduce the blast radius but do not remove the underlying tension. A workload whose true combined working set (OLTP hot set plus whatever the analytical query legitimately needs to keep re-reading) exceeds the buffer pool will still churn, scan-resistance mechanisms only stop a single one-shot scan from behaving worse than its own footprint requires. The durable fix is workload separation or a larger cache, not a tuning knob.