Tag Archives: restart mysql

Reduce startup and shutdown time by using warm up innodb buffer pool

Reduce startup and shutdown time by using warm up innodb buffer pool

For database servers having large memory utilisation and have big innodb_buffer_pool_size , it takes longer to stop and start the instance. This is because more data and indexes stored in huge  innodb_buffer_pool. This data and indexes are used by queries running on the server prior to go to the disk  if not found in innodb_buffer_pool. So bigger the buffer pool more time it will take to shutdown. One way of reducing the time is using warm up innodb buffer pool by dumping the state of buffer and reusing it when starting up.

We can do it by turning innodb_buffer_pool_dump_at_shutdown on. In normal restart , innodb_buffer_pool gets empty and after restart it take time to warm up again.  By using innodb_buffer_pool_dump_at_shutdown we can use pre- warmed up innodb_buffer_pool which can reduce the time significantly. It is dynamic variable and can but turned on / off online and we can add it in the my.cnf file as permanent change.

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1 / ON

or we can add it in my.cnf as shown under

innodb_buffer_pool_dump_at_shutdown=ON

Now if we shutdown the server,  ib_buffer_pool dump file will be created under the MySQL datadir which can be configured to be created on different location and file name by using innodb_buffer_pool_filename. This file will store current state and data of innodb buffer pool.

Now we want server to use this dump file when it will start again. We can do it by using following entry in my.cnf file.

innodb_buffer_pool_load_at_startup=ON

After restarting the server we can check ib_buffer_pool file created in data directory and also we can view the contents as well as shown  below.

bash-4.2$ ls ib_buffer_pool
ib_buffer_pool

bash-4.2$ less ib_buffer_pool
0,278534
0,47
0,442406
0,278535
0,48
0,49
0,50
0,442407

I hope this will help you all, Don’t forget to like, share and comment

Thanks and Regards

Raja M Naveed