Why?! To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools.
Things to be added to /etc/mysq/my.cnf:
# Shortened warm-up times with a preloaded InnoDB buffer pool innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=ON
Basically when MySQL receives the TERM signal will dump the InnoDB buffer pool /data/db/mysql/ib_buffer_pool and it will load it automatically when it will start. However, the buffer pool(s) dump can be done at any time when MySQL is running by doing:
SET GLOBAL innodb_buffer_pool_dump_now=ON;
By analogy the buffer pool(s) can be loaded manually by:
SET GLOBAL innodb_buffer_pool_load_now=ON;
Additionally the progress of the buffer pool(s) dump (although very quick) and load (not so quick) can be monitored by:
SHOW STATUS LIKE 'innodb_buffer_pool_dump_status'; SHOW STATUS LIKE 'innodb_buffer_pool_load_status';
The load operation can be interrupted by:
SET innodb_buffer_pool_load_abort=ON;
Recent Comments