Boost MySQL 5.7 Restarts with InnoDB Buffer Pool Pre‑Load

MySQL 5.7 introduced a default feature that saves the contents of the InnoDB buffer pool on shutdown and reloads them on startup, eliminating the need for manual configuration that existed in earlier versions such as MySQL 5.6 or Percona Server 5.6.

Why the feature matters

On rotating hard drives, warming up the buffer pool after a restart could take hours, severely degrading performance. With modern SSDs and NVMe storage, a system typically reaches 90% of its optimal performance within ten minutes, making the automatic pre‑load both fast and largely transparent.

Controlling how much is saved

By default MySQL retains only the most frequently accessed 25% of pages in the buffer pool. Administrators can adjust this with the innodb_buffer_pool_dump_pct variable; setting it to 100 forces MySQL to keep all dirty pages in memory, which is useful when InnoDB is used as an in‑memory database.

Monitoring load progress

During startup MySQL accepts network connections while the buffer pool is still loading, and the loading process can affect overall performance. The progress can be observed via the GLOBAL STATUS variable, for example:

1 | Innodb_buffer_pool_load_status | Loaded 403457/419487 pages

When loading completes you will see a line similar to:

1 | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 161123 9:18

Multi‑node considerations

In replicated environments or Percona XtraDB clusters, it is advisable to wait until the buffer‑pool load finishes before routing traffic to the node, ensuring that queries benefit from the pre‑loaded data.

Performance on fast storage

Tests on high‑performance NVMe drives under read‑only workloads have shown pre‑load rates exceeding 400 MB/s, though real‑world results may be lower if the storage subsystem does not issue enough parallel I/O requests.

Behavior after crashes

If MySQL crashes, it still attempts to pre‑load the buffer pool, but only the pages saved from the last clean shutdown (stored in ib_buffer_pool) are restored, which may include data irrelevant to the current workload. Regularly dumping the buffer pool mitigates this issue.

Manually dumping the buffer pool

To force an immediate dump of the current buffer‑pool contents, execute: SET GLOBAL innodb_buffer_pool_dump_now=ON; This command writes the current page list to ib_buffer_pool, ensuring that the next restart can reload the most recent state.