How would you decide how much memory to give a database's buffer pool on a dedicated 128 GB server, and what is 'double buffering' between the engine's page cache and the operating system's page cache?

## Why percentage rules of thumb are a starting point, not an answer 'Give it 75% of RAM' and 'give it 25% of RAM' are both real, defensible advice - for *different engines*, because they differ in whether the engine expects the kernel page cache to be a meaningful second tier. Quoting a percentage without knowing which philosophy the engine follows is the shallow answer. The reasoned version has three inputs: the working set, the competing memory consumers, and the I/O philosophy. ## Input 1: the working set The working set is the set of pages actually touched over a representative interval - not the database size. In a typical OLTP system it is a small fraction of total data: recent orders, active accounts, all the upper levels of the hot B-trees, small reference tables. Estimating it: - Sample which objects generate logical and physical reads over a busy hour. - Add index internal levels: even for a huge table, everything above the leaves is small and is touched by every lookup, so those pages are always worth caching. - Watch the marginal return: as the pool grows, physical reads fall steeply, then flatten. The knee of that curve is the useful size. Past it you are buying RAM for pages nobody reads twice. Three regimes follow. If the whole database fits, cache it all and the design question becomes write throughput instead. If the working set fits but the database does not - the common case - size to the working set with headroom for growth. If the working set is far larger than any affordable RAM, extra memory yields little; the leverage moves to indexing, partitioning, workload separation, and faster storage. ## Input 2: everything else that needs memory The buffer pool is not the only consumer, and the failure mode of over-allocating is severe. - **Per-connection memory.** Each session has stacks, buffers and caches. At 500 connections this is not a rounding error - and it is one of the strongest arguments for a connection pooler, which cuts both this and context-switch overhead. - **Per-operation workspace.** Sorts, hash joins and aggregates each take a workspace allocation, and a single query can open several such operators; multiply by concurrency for the realistic worst case. Under-provision it and operations spill to disk; over-provision it and a burst of concurrency exhausts RAM. - **Maintenance operations**: index builds, vacuum/purge, backups. - **The OS itself**, plus filesystem metadata and any agents on the box. Leave real headroom. The catastrophic outcome is **swapping**: the kernel pages out part of the buffer pool, so what the engine believes is a memory hit becomes a page fault - one disk read to bring the frame back, potentially one write to evict something else. Performance does not degrade gracefully; it collapses. On a database server the correct posture is to size so that swap is never touched (and to configure the kernel accordingly, and to avoid being killed by an out-of-memory reaper). ## Input 3: double buffering and the I/O philosophy With ordinary buffered file I/O, reading a page goes: disk -> kernel page cache -> copy into the buffer-pool frame. The page now occupies memory **twice**. That is double buffering. Its costs are wasted RAM, an extra memory copy per read, and split control - two independent replacement policies making decisions about the same data, neither aware of the other's intent. Writes are worse: the engine's write goes into kernel dirty pages, and *when* those reach the device is the kernel's decision until an fsync forces it, so the engine must fsync to enforce log-before-page ordering, and kernel writeback bursts can appear as latency the engine did not schedule. Two coherent stances exist: 1. **Buffered I/O with a deliberately modest pool.** The engine keeps a pool sized for the hottest pages and treats the kernel cache as a large, cheap second tier - a miss in the pool is often satisfied from RAM anyway. Cost: some duplication, less control. Benefit: memory is shared elastically with the rest of the system, and a restart re-warms from the still-populated OS cache. 2. **Direct I/O with a large pool.** The engine bypasses the kernel cache entirely, takes the majority of RAM, and owns caching and writeback timing end to end. Cost: it must implement its own read-ahead and write scheduling, and a misconfiguration is unforgiving. Benefit: one copy, one policy, predictable ordering. The anti-pattern is the middle: buffered I/O with a pool at ~50% of RAM, so a large fraction of memory holds duplicate copies while neither layer has enough to hold the working set. ## Validating the choice Sizing is a hypothesis; verify it. Watch physical reads and the eviction rate before and after a change, confirm latency percentiles moved, and check that peak memory (pool + connections + workspaces) stays clear of RAM with the machine never swapping. Re-derive after data growth or a workload change - a pool that was ample at 300 GB of data may not be at 3 TB. And remember the ceiling: a bigger pool only ever removes *read* I/O. If the bottleneck is log flushes at commit, checkpoint write bursts, or lock contention, more cache changes nothing.