mysql: preload InnoDB buffer pool

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;

Leave a Reply

Your email address will not be published. Required fields are marked *