Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Warm-up InnoDB Buffer Pool
+4 Vote Up -0 Vote Down
Send to Kindle

As we know, 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 (it was presented on previous versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Now, the next time we stop MySQL, a file named ib_buffer_pool will be created under the MySQL datadir (you can change the file name by using the innodb_buffer_pool_filename config).

Next step is to tell MySQL to read the this file and load the actual page data into the buffer pool, we will do it by adding a new entry named innodb_buffer_pool_load_at_startup on my.cnf (and also add innodb_buffer_pool_dump_at_shutdown to it, this way it will automatically dump it every time we shutdown the server):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Now we can restart our mysql server, as mention before, it will create a file with a content similar to the above:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

When we restart our server, we can see 2 new messages being written to MySQL logs:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

This means that MySQL have wrote the file with the buffer pool pages.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

This means that MySQL have finished to reload the pages content back to the InnoDB buffer pool.

You can controller the dump / reload progress through the Innodb_buffer_pool_dump_status and Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

If you wish, you can tell MySQL to dump/reload the buffer pool right now using innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.

If you use InnoDB as storage engine of any of you tables, that is a very cool practice to put in place to minimize the side effects of a server/service restart.

Send to Kindle

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.