How to Pre-warm InnoDB buffer pool on reboot in MySQL

One of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.

To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6

innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup

The feature also provides you the ability, using MySQL stored procedures, to perform buffer-pool dumps and loads on demand.

Popular posts from this blog

How to delete / clear queue of PowerMTA