Warm-up InnoDB Buffer Pool

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
This entry was posted in InnoDB, MySQL, optimize, performance. Bookmark the permalink.

5 Responses to Warm-up InnoDB Buffer Pool

  1. Valerie Parham-Thompson says:

    This is good stuff.

    Have you tested moving this file to a new slave to warm up its buffer pool? If that works, this might be an easy way to do that with failover.

    Also wonder if you have used this on a high-volume production server, and if it causes performance issues (I imagine it’s not “cost-free”).

    Valerie

    • marceloaltmann says:

      Hi Valerie, thanks for your questions.
      I did a test but it turns out that it does not work to move the file from one instance to other, see bellow:

      master [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
      +----------+
      | COUNT(*) |
      +----------+
      | 13717 |
      +----------+
      1 row in set (0.22 sec)

      master [localhost] {msandbox} ((none)) > SET GLOBAL innodb_buffer_pool_dump_now = 1;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost] {msandbox} ((none)) > quit
      Bye
      [root@marcellodb rsandbox_5_6_15]# cp master/data/ib_buffer_pool node1/data/
      cp: overwrite `node1/data/ib_buffer_pool'? y
      You have mail in /var/spool/mail/root
      [root@marcellodb rsandbox_5_6_15]# node1/use
      slave1 [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
      +----------+
      | COUNT(*) |
      +----------+
      | 14 |
      +----------+
      1 row in set (0.16 sec)

      slave1 [localhost] {msandbox} ((none)) > SET GLOBAL innodb_buffer_pool_load_now=1;
      Query OK, 0 rows affected (0.00 sec)

      slave1 [localhost] {msandbox} ((none)) > SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
      +--------------------------------+--------------------------------------------------+
      | Variable_name | Value |
      +--------------------------------+--------------------------------------------------+
      | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140114 17:25:48 |
      +--------------------------------+--------------------------------------------------+
      1 row in set (0.00 sec)

      slave1 [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
      +----------+
      | COUNT(*) |
      +----------+
      | 14 |
      +----------+
      1 row in set (0.12 sec)

      slave1 [localhost] {msandbox} ((none)) >

      Regarding to the costs, I ran some tests with a 12gb instance in a server where iowait saturation is 4.16% (based on echo "( 1 / `cat /proc/cpuinfo | grep processor | wc -l`) * 100" | bc -l) using the innodb_buffer_pool_load_at_startup variable it took 1 minute and 16 seconds the iowait was around 2%, in comparison with normal warmup (SELECT * FROM TABLE AND SELECT * FROM TABLE FORCE INDEX(index)) it took 1 minute and 20 (not a big difference) but the iowait reached a max of 20%, so, yes, there is a cost (some IO), but is far less then a normal warm-up.

  2. sylar says:

    Good job! In mysql5.5, I often execute the query “select count(*) from tb_name” for each table to warm up the innodb buffer pool by a shell script ^.^
    thanks, it’s a nice pos!

Leave a Reply

Your email address will not be published. Required fields are marked *


six − 2 =

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