26 Dec 2014 Pre-loading the InnoDB buffer pool MySQL 5.6
InnoDB Buffer Pool
InnoDB buffer pool is one of the most important options for improving InnoDB performance. InnoDB buffer pool is a space reserved in memory which is used to save data and indexes that are frequently accessed. When MySQL server receives a query, if the data blocks and indexes required for that query are stored in the InnoDB buffer pool, then The data will be served directly from memory, thus there is no need to go to the disk to return the results.
This process improves the response time, as the memory can be accessed faster than the disk. It also decreases the disk I/O, as reading data from disk will occur once and then it will be accessed from memory.
The InnoDB buffer pool also stores the data inserted or modified, so that all changes are grouped together then write it to the disk sequentially, this improves the performance significantly.
The default size of InnoDB buffer pool is 8M. The larger the buffer pool, the less disk I/O is needed to access the data and the more InnoDB acts like memory database.
Warming Up
Warming up MySQL server means filling buffer pool and the other regions in memory with data as they would be under normal conditions. Warming up period means how long it will take the server to reach this state so that the server can be subjected to the normal workload.
After restarting MySQL server, data in InnoDB buffer pool is lost, and MySQL server starts with empty buffer pool. It takes some time to bring the disk pages that were in the buffer pool back to the memory (warm-up period). But with large sizes of buffer pool, warming up can take long time. This affects the throughput of MySQL server, so the server cannot be used in production for longer time.
The old method to warm up the server was by running queries in order to load data from disk to the memory
e.g. SELECT COUNT(*) FROM db_name.tb_name, that loads the table data and the primary key into the buffer.
And if there are secondary keys, then we need to run a SELECT COUNT(*) FROM db_name.tb_name FORCE INDEX(key_name) for each secondary key.
That was until MySQL 5.6 came up with a solution, which is “Pre-loading InnoDB buffer pool”.
Pre-loading InnoDB buffer pool
Pre-loading the InnoDB buffer pool is a new feature that has been added to MySQL 5.6 to avoid long warm-up periods after restarting the server. It saves the state of the buffer pool at shutdown by saving the addresses of pages that were in the buffer pool in a compact file, and restore them in buffer pool at startup. It allows dumping and restoring the buffer pool at anytime we want, in addition to shortening the warm-up period as the pages are immediately loaded to memory at startup. This feature was introduced in previous versions of MariaDB and Percona server.
In order to use this feature, Make sure that MySQL 5.6 server installed successfully.
Note: In case of using a micro instance, the installation of MySQL 5.6 server may fail, because the default MySQL 5.6 configuration requires more memory than it can get in the micro instance. Solving this problem can be done by increasing the memory of the micro instance, or by creating a swap file.
To change the size of InnoDB buffer pool:
innodb_buffer_pool_size= 2048M
Making the buffer pool larger can improve performance significantly, but just make sure of leaving enough memory for other processes on the server.
To save the state of InnoDB buffer pool at server shutdown:
mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON
By enabling this option, the next time you stop MySQL server, the state of the InnoDB will be dumped to a file named ib_buffer_pool which will be created under the MySQL datadir.
#ls -l /var/lib/mysql/ib_buffer_pool -rw-rw---- 1 mysql mysql 1440 Dec 22 07:43 /var/lib/mysql/ib_buffer_pool
This file contains the tablespace IDs and page IDs, and the name of this file can be changed using innodb_buffer_pool_filename variable.
To restore the state of InnoDB buffer pool at server startup:
mysql> SET GLOBAL innodb_buffer_pool_load_at_startup = ON
By enabling this option, the next time you start MySQL server, MySQL will read this file and load the data pages into the buffer pool.
In order to see the progress of dumping or loading data in the buffer pool:
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
After loading the Innodb buffer pool, the result will be like:
+----------------------------------+-------------------------------------------------- | Variable_name | Value | +----------------------------------+-------------------------------------------------- | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 141115 11:42:38 | +----------------------------------+---------------------------------------------------
To dump/restore the state of the InnoDB buffer pool right now:
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;