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;

Scrie si tu o vorbulita


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.