MySQL dump & load InnoDB Buffer Pool

For performance, having a warm InnoDB Buffer Pool is very important. What does that mean ?

A warm buffer pool means that the most used pages (working set) required by the production workload are already loaded in memory (in the buffer pool). If so, MySQL doesn’t need to read the pages from disk every time it requires the most used page and speeds up the process when the needed data is already in memory.

When you start MySQL, by default the InnoDB Buffer Pool is cold and the warm up process can even take days sometimes…

So, you can already deduce that restarting mysqld is a source of having a cold Buffer Pool as it will start empty. Another reason to have a non optimal Buffer Pool is to load it unnecessary pages. This can happen during a logical dump or load. If you regularly do a mysqldump for example (don’t forget that MySQL Shell dump & load is better if you do logical dumps, but introduces also the same effect).

But of course, in MySQL you also have to possibility to deal with such problem: dump & load of the InnoDB Buffer Pool !

There are multiple ways to work on the InnoDB Buffer Pool dump and load, let me explain some scenarios. Of course MySQL’s manual also covers this topic.

Dump at shutdown, load at boot

The first option we have to deal with the buffer pool is to dump its content when we stop MySQL. We don’t really dump the full buffer pool, but the addresses of the pages that are in it when mysqld is properly stopped.

In fact, this is the default now in MySQL, innodb_buffer_pool_dump_at_shutdown is enabled. And of course, when MySQL starts, it also loads that content as innodb_buffer_pool_load_at_startup is also enabled.

We will also see later in this post something related to this… but let’s wait a bit…

Dump and load on demand

In MySQL, we also have the possibility to dump the buffer pool’s content on demand using innodb_buffer_pool_dump_now. That will dump the current content on disk. Then this can be also loaded on demand using innodb_buffer_pool_load_now.

This is also useful in case of a crash of mysqld, as it was not a clean shutdown, the buffer pool’s content was not properly dumped to disk, but the latest one done manually will be loaded.

The main purpose of such manual dump & load of the InnoDB Buffer Pool, is when there will be a different workload than the usual one that can fill the buffer pool with data other than the working set. For example dumping a complete large old schema.

Dumping regularly

It’s possible to dump the buffer pool’s content at some intervals. This is useful in case of a crash to avoid to load an old dump.

To achieve this, you need to create an EVENT like this:

CREATE EVENT automatic_buffer_pool_dump
       ON SCHEDULE EVERY 1 HOUR
       DO
          SET global innodb_buffer_pool_dump_now=ON;

Let’s have a look how to play with this and how to control it:

Test & example

The first thing we do, we control the content of the Buffer Pool as explained in this older post:

mysql> SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2), '%') BufferPoolFull
       FROM (
         SELECT variable_value num FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
         (SELECT variable_value num FROM performance_schema.global_status
          WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
 +----------------+
 | BufferPoolFull |
 +----------------+
 | 75.29%         |
 +----------------+
 1 row in set (0.01 sec

We can even verify which schemas are loaded in the Buffer Pool:

mysql> SELECT object_schema, allocated, data 
FROM sys.innodb_buffer_stats_by_schema;
+---------------+-----------+------------+
| object_schema | allocated | data       |
+---------------+-----------+------------+
| sbtest        | 53.22 MiB | 46.97 MiB  |
| mysql         | 5.36 MiB  | 2.72 MiB   |
| query_rewrite | 16.00 KiB |    0 bytes |
+---------------+-----------+------------+

And now we dump the content:

mysql> SET global innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.00 sec)

In the error log we can see these messages:

mysql> select LOGGEND, DATA from performance_schema.error_log order by 1 desc limit 2;
+-------------+---------------------------------------------------------+
| LOGGED      | DATA                                                    |
+-------------+---------------------------------------------------------+
| 2021-06-16. | Buffer pool(s) dump completed at 210616 14:25:39        |
| 2021-06-16. | Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool |
+-------------+---------------------------------------------------------+

And on the filesytem:

[root@imac mysql]# ls -lh ib_buffer_pool
 -rw-r----- 1 mysql mysql 20K Jun 16 14:25 ib_buffer_pool

We restart MySQL and we check again:

+----------------+
| BufferPoolFull |
+----------------+
| 26.12%         |
+----------------+

mmm… what’s happening ? this is not what I expected…

In fact, it’s my fault, I didn’t read carefully enough the documentation and my colleague Marcin Babij pointed me the reason of this: innodb_buffer_pool_dump_pct.

By default the value of innodb_buffer_pool_dump_pct is 25 !

To make it work as expected, if you want to dump and load it completely, you need to set it to 100.

+----------------+
| BufferPoolFull |
+----------------+
| 99.10%         |
+----------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_buffer_pool_dump_pct = 100;

[root@imac ~]# ls -lh /var/lib/mysql/ib_buffer_pool 
-rw-r----- 1 mysql mysql 74K Jun 20 08:32 /var/lib/mysql/ib_buffer_pool

Then I restart MySQL (I disabled the load of the buffer pool at start):

+----------------+
| BufferPoolFull |
+----------------+
| 18.70%         |
+----------------+
 1 row in set (0.00 sec)

mysql> SET global innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.00 sec)

+----------------+
| BufferPoolFull |
+----------------+
| 99.94%         |
+----------------+
1 row in set (0.01 sec)

Perfect !

So now you know how to deal with MySQL InnoDB Buffer Pool warmup and how to proceed is you plan to do unusual queries that could affect badly your working set in memory.

Links to the documentation:

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_dump_now
• innodb_buffer_pool_dump_pct
• innodb_buffer_pool_filename
• innodb_buffer_pool_load_abort
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_load_now