MySQL 8.0.23 Snapshot Restoration and Buffer Pool

Question

The company I work for recently had a production outage. This outage occurred because we restored a snapshot of our MySQL database, and the MySQL buffer pool needed to be refreshed. The database had diminished performance until the buffer pool was able to warm up.

Is there a way to restore the previous state of the buffer pool upon a snapshot restoration? If so, what steps do I follow in order to create a backup of the buffer pool, and what steps do I follow to restore the buffer pool?

Answer 1

See these for how to prepare to "warm up" the buffer_pool:

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_at_shutdown

https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

Note these in that last link:

SET GLOBAL innodb_buffer_pool_dump_now=ON;
SET GLOBAL innodb_buffer_pool_load_now=ON;

[mysqld]
innodb_buffer_pool_dump_pct=40;

Be aware that dump/load takes some time. That is, it is not truly instantaneous.

I don't know how the PCT is applied. If I were designing it, would start with either "recently used" pages and/or "non-leaf nodes.