InnoDB buffer pool: the cold-start problem & dump/load cureMySQL 8.4 • MariaDB 11.4

Same query, run twice: why the first run hits disk and the second runs from RAM — plus the restart story.

Parameters (dump at shutdown)

About this example
Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

Example query this animation executes

SELECT u.id, u.email, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE o.created_at > NOW() - INTERVAL 7 DAY

Same query, two runs back-to-back. Act 1 reads from disk; Act 2 reads from RAM. The third act shows what happens when the server restarts — warm, not cold.

What you'll see

  • Cells = buffer-pool slots, each one 16 KiB page labelled table:page (U:1001 is users page 1001). Red-ish = just read from disk (slow). Green = pool hit (fast). Yellow = listed in ib_buffer_pool at shutdown.
  • The line under the pool says what the last page read did; the readout below counts hits, disk reads and I/O time for the current run.
  • Act 3 shuts the server down and restarts it: the file box shows the page IDs InnoDB saved, and startup reads those pages back in the background. MySQL saves the most recently used 25% of the cached pages; MariaDB saves up to 25% of the pool's size. The parameters above change the percentage and switch between the two rules.
  • The hit/miss ratio is what decides whether a repeat run is milliseconds (warm) or seconds (cold). The latencies here are illustrative: 6 ms per disk read, 0.06 ms per hit.
  • Next step plays one page read, or one stage of the restart, at a time; the simplified code beside the drawing highlights the lines each step runs.

Live cache stats

Phase

—

Pool hits ?Pages found in the buffer pool — no disk I/O.

—

Pool misses ?Pages not in the pool — read from storage and inserted.

—

I/O time this run ?Sum of per-page latencies for the current run: 6 ms for each page read from disk, 0.06 ms for each pool hit. Illustrative numbers: a spinning disk takes milliseconds per read, an SSD about a tenth of a millisecond, and memory far less; the gap between disk and memory is what matters.

—

Learn more — the dump & load mechanism

A cold-start disaster is the first thing a DBA learns to avoid: restart the server, and the first hour of queries all go to disk instead of RAM because the buffer pool is empty. InnoDB ships with two defaults that make this a solved problem — but only if you know they exist.

Dump on shutdown

  • innodb_buffer_pool_dump_at_shutdown (ON by default) writes the identities of the most recently used pages, read from the young end of the LRU list, to a small file named ib_buffer_pool in the data directory. innodb_buffer_pool_dump_pct = 25 by default: MySQL lists 25% of the pages in each buffer pool's LRU list, MariaDB at most 25% of the pool's size. A shutdown with innodb_fast_shutdown = 2 skips the dump in both.
  • Only page IDs are dumped, not page data — the file is tiny (a few MB for a 10 GB pool).

Load on startup

  • innodb_buffer_pool_load_at_startup (ON) reads ib_buffer_pool and issues reads for those pages. The load runs in the background (MySQL's dump/load thread, MariaDB's dump/load task); queries don't block on it. Both sort the list by tablespace and page first, and MySQL keeps the load to about innodb_io_capacity reads per second while other work is running.
  • SET GLOBAL innodb_buffer_pool_load_now = ON triggers the same load immediately at runtime. The statement returns fast — the actual reads run async (verified in storage/innobase/buf/buf0dump.cc's buf_load_start()).

Common sizing mistake

If innodb_buffer_pool_size is small relative to your working set, no amount of warmup helps — pages you loaded at startup get evicted before real queries reach them. Size the pool to the working set first; then worry about warmup.