Shortened warm-up times with a preloaded InnoDB buffer pool
By Calvin Sun on Jul 25, 2011
Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Vasil Dimov.
Are you running an InnoDB installation with a many-gigabytes buffer pool(s)? Does it take too long before it goes back to speed after a restart? If yes, then the following will be interesting to you.
In the latest MySQL 5.6 Labs release we have implemented an InnoDB buffer pool(s) dump and load to solve this problem.
The contents of the InnoDB buffer pool(s) can be saved on disk before MySQL is shut down and then read in after a restart so that the warm up time is drastically shortened – the buffer pool(s) go to the state they were before the server restart! The time needed for that is roughly the time needed to read data from disk that is about the size of the buffer pool(s).
Lets dive straight into the commands to perform various dump/load operations:
The buffer pool(s) dump can be done at any time when MySQL is running by doing:
mysql> SET innodb_buffer_pool_dump_now=ON;
This operation is very quick and creates a file named ib_buffer_pool in the InnoDB data directory, by analogy with the default InnoDB data file ibdata1. The file contains only the space and page IDs and is about 500k per gigabyte of buffer pool. The same operation can also be done automatically during MySQL shutdown:
mysql> SET innodb_buffer_pool_dump_at_shutdown=ON;
By analogy the buffer pool(s) can be loaded manually by:
mysql> SET innodb_buffer_pool_load_now=ON;
and automatically during startup from the file ib_buffer_pool by setting
in my.cnf or my.ini.
The filename where the buffer pool(s) are dumped and later loaded from can be specified by changing the value of the global configuration variable innodb_buffer_pool_filename which defaults to ib_buffer_pool.
Additionally the progress of the buffer pool(s) dump (although very quick) and load (not so quick) can be monitored by:
mysql> SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';
mysql> SHOW STATUS LIKE 'innodb_buffer_pool_load_status';
And finally the load operation can be interrupted by:
mysql> SET innodb_buffer_pool_load_abort=ON;
Before loading the list is sorted so that pages will be read in as close to sequential order as possible. The load is asynchronous so it should not interfere with normal operations too much. The load is clever enough to handle different buffer pool sizes or pages that are missing during the load. A page will not be loaded if it is already in the buffer pool so there is no need to worry about problems with changed pages.
The dump file is plain text so it is possible to edit it, though we do not recommend this.