Parameters (dump at shutdown)
About this exampleAbout 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_poolat 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 namedib_buffer_poolin 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 withinnodb_fast_shutdown = 2skips 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) readsib_buffer_pooland 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 aboutinnodb_io_capacityreads per second while other work is running.SET GLOBAL innodb_buffer_pool_load_now = ONtriggers the same load immediately at runtime. The statement returns fast — the actual reads run async (verified instorage/innobase/buf/buf0dump.cc'sbuf_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.