The “Cold Cache” Nightmare After Every Maintenance
This scenario is likely familiar to SysAdmins: You just typed systemctl restart mysql to update a configuration. The database reports active (running) in seconds, but the real nightmare is just beginning. Over the next 15-30 minutes, CPU spikes from 10% to 80-90%. Disk I/O surges, and the application starts throwing timeout errors.
In a real-world case involving an e-commerce system I managed (a ~100GB DB running MySQL 8.0), every restart without preparation caused query latency to jump from 5ms to 500ms. This is the Cold Cache phenomenon. At this point, the Buffer Pool is empty. MySQL is forced to painstakingly read every data block from the disk instead of fetching it directly from RAM.
Why Does MySQL Lag After Startup?
To understand the solution, we need to look at the InnoDB Buffer Pool. This is the most critical memory area, storing frequently accessed data pages and indexes. When a SELECT query occurs, MySQL prioritizes searching the Buffer Pool (Cache Hit). If found, it returns the result instantly at lightning speed.
However, RAM is volatile memory. When MySQL shuts down, all this “hot” data evaporates. After a restart, the Buffer Pool is completely blank. The system needs a significant amount of time to reload critical data based on actual user requests.
Manual Methods: Slow and Risky
Many admins use “forceful” tactics like running SELECT COUNT(*) on large tables or using scripts to crawl all data immediately upon startup. This approach has three fatal flaws:
- Exhausts Disk I/O bandwidth at the system’s most sensitive moment.
- Loads “junk” data into RAM that isn’t actually needed.
- Difficult to control and high maintenance for scripts.
The Standard Solution: InnoDB Buffer Pool Dump and Load
Since version 5.6, MySQL has included a brilliant built-in feature: it records the list of data pages currently in RAM before shutting down and automatically reloads them upon startup.
The smart part is that MySQL doesn’t save tens of gigabytes of data to a file. It only saves the Space ID and Page ID (identifiers for the data pages). This dump file is usually only a few MBs, making reading and writing nearly instantaneous.
1. Check Feature Status
First, check if your server has this feature enabled using this SQL command:
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_at_shutdown';
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';If the result is OFF, you are wasting a powerful optimization tool.
2. Optimal Configuration in my.cnf
To make this setting permanent, open your configuration file (usually /etc/mysql/my.cnf). Add the following lines under the [mysqld] section:
[mysqld]
# Automatically save the page list on shutdown
innodb_buffer_pool_dump_at_shutdown = ON
# Automatically reload pages on startup
innodb_buffer_pool_load_at_startup = ON
# Dump 100% of hot pages instead of the 25% default
innodb_buffer_pool_dump_pct = 100
# Storage filename (default is in the data directory)
innodb_buffer_pool_filename = ib_buffer_poolAfter saving, restart MySQL. It might still be slow the first time, but from then on, you’ll see the system get “into shape” much faster.
3. Instant Dump/Load Without a Restart
If you are about to perform heavy maintenance and don’t want to shut down the server, proactively dump the list using these commands:
-- Dump current data to file immediately
SET GLOBAL innodb_buffer_pool_dump_now = ON;
-- Load data from file into RAM immediately
SET GLOBAL innodb_buffer_pool_load_now = ON;4. Monitor Data Loading Progress
The loading process happens in the background and won’t block MySQL’s startup process. To know when the system is fully “warmed up,” use this command:
SHOW STATUS LIKE 'innodb_buffer_pool_load_status';When you see the line Buffer pool(s) load completed, it means the database is ready to serve with maximum performance.
Practical Notes from Personal Experience
While this feature is excellent, you still need to keep two things in mind:
- Disk I/O: Background data loading still consumes disk resources. If you’re using an old HDD, this process might take a while and impact live queries. With NVMe SSDs, you have nothing to worry about.
- dump_pct Value: MySQL 8.0 defaults to dumping only 25% of pages. If you have plenty of RAM, feel free to set it to 100% to fully preserve the system’s optimal state.
Conclusion
With just 2 minutes of editing the configuration file, you can completely eliminate the worry of a sluggish system after maintenance. This is a “small but mighty” optimization step that every database admin should apply. Check your server today!
