CONCEPT Cited by 2 sources

InnoDB buffer pool¶

The InnoDB buffer pool is an in-memory cache of InnoDB pages (default 16 KB each) sitting between MySQL query execution and the on-disk B+tree pages. When InnoDB needs to access any key, value, or row, it "loads the entire associated page from disk, even if that page contains other keys or values it does not need." (Source: sources/2024-09-09-planetscale-b-trees-and-database-indexes.)

Every page access goes through the buffer pool:

Look up the page in the buffer pool.
If present → read directly from memory.
If absent → fault the page in from disk, evict something else if full, then read.

Why it's the load-bearing cache¶

Without the buffer pool, every B+tree descent would page-fault to disk. A 3-level B+tree lookup would be 3 disk seeks (~0.1 ms on NVMe, ~10 ms on HDD). The buffer pool turns all-hit queries into memory-only operations.

The buffer pool drastically helps query performance. Without it, we'd end up doing significantly more disk I/O operations to handle a query workload.

— sources/2024-09-09-planetscale-b-trees-and-database-indexes

Why B+tree design choices still matter with a buffer pool¶

Even with infinite buffer pool, pages-visited-per-query is not free:

"There's still a (small) cost to looking up a page in the buffer pool." Hash-table lookup + latch acquisition + metadata bookkeeping.
"It helps reduce the number of buffer pool loads and evictions that need to take place." Each miss costs one page read + potentially one page write-back of a dirty evictee.

Shallow trees + clustered layout + sequential primary keys matter specifically because they minimise unique-pages- visited-per-query — which keeps the hot working set small enough to fit in buffer pool memory. See concepts/working-set-memory (the WiredTiger analogue).

Connection to primary-key choice¶

A UUID primary key "visit[s] unpredictable nodes per insert" — meaning any page in the tree may be the destination leaf for the next insert. Over sustained write load, the set of hot pages approaches the entire tree, so the buffer pool hit-rate collapses. A sequential primary key concentrates inserts on the right-most path, keeping the hot working set to ~O(depth × insert-rate × time-window) pages.

The buffer pool doesn't fix the random-PK problem; it just makes it visible as a miss-rate metric. See concepts/uuid-primary-key-antipattern.

Buffer pool sizing¶

innodb_buffer_pool_size is one of the most important MySQL tuning knobs. Rule of thumb: size it to hold the working set of the workload, typically 50–70% of RAM on a dedicated DB host. Pages are evicted via a variant of LRU (InnoDB uses a "midpoint insertion" LRU to avoid large scans polluting the hot portion).

Change buffer — deferred secondary-index writes.
Adaptive hash index — on-top hash cache for frequently accessed keys.
Doublewrite buffer — durability mechanism for partial-page-write protection, not a read cache.

Seen in¶

sources/2024-09-09-planetscale-b-trees-and-database-indexes — buffer pool introduced as the load-bearing mechanism by which B+tree design choices (shallow trees, sequential PKs) translate into throughput.
sources/2025-07-08-planetscale-caching — Ben Dicken positions the InnoDB buffer pool explicitly alongside Postgres's two-layer cache stack as the "MySQL does a similar thing with the buffer pool. Like Postgres, this is an internal cache to keep recently used data in RAM" instance; canonicalises the ACID-aware-caching framing — "more complex than a 'regular' cache as they also have to be able to operate with full ACID semantics and database transactions. Both databases have to take careful measures to ensure these pages contain accurate information and metadata as the data evolves." First wiki disclosure of the philosophical divergence from Postgres: InnoDB's single-layer design (with O_DIRECT bypassing the OS page cache in common configs) vs Postgres's explicit double-buffering over the OS page cache. See concepts/postgres-shared-buffers-double-buffering.
systems/innodb — the engine.