What's the impact of innodb_buffer_pool_dump_now in Running MySQL Instance?

Question

The parameter innodb_buffer_pool_dump_now dump the least recently used pages into the file ib_buffer_pool.

And the parameter innodb_buffer_pool_load_now restore that file into the buffer pool again.

Question is- Whenever we run the below command in running MySQL what will happen in the background ? Is dumping of buffer pool block all other connections ? Is dumping of buffer pool stop flushing of dirty pages and remain in that state ?

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

Similarly: When we load the dump file in running buffer using below command, what will happen with the existing buffer pool things ? Is it drop already loaded pages and use the ones that it got from the file ? Block the transactions or what ?

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

Answer 1

There are multiple things involved.

Caveat: The following is my interpretation of that is going on; it could be wrong.

• The "Change Buffer" contains index info that needs to be added to index blocks. This is normally done in the "background". In the case of an abort, the info can be recovered from the logs.

• "Dumping" the buffer_pool" is not what it sounds like -- all that is dumped is a pointer to each block. I think such a pointer is 20 bytes; compare that to 16384 bytes for a whole block. The point is that the dump is very fast.

• The corresponding "load" would probably notice that most of the blocks are already present and do very little I/O. If it does not load exactly what you had before the dump, no harm is done. It's just warning up the "cache".

• It would be very remiss of InnoDB to violate transactional integrity in order to provide those commands.

• The 'dump' was designed to be done just before a graceful shutdown, then followed by a 'load' after the restart. Hmmm... That says to me that the 'load' may not actually do anything except during startup after a restart.

So, what is your intent? You may not be able to achieve it with those settings.

More details on how it works, including how the "pointers" are stored on disk: https://mysqlserverteam.com/mysql-dumping-and-reloading-the-innodb-buffer-pool

This points out an advantage of periodically doing the dump_now: https://www.percona.com/blog/2016/11/30/using-innodb-buffer-pool-pre-load-feature-mysql-5-7/

If you need to do a shutdown, but what it to be brief.

-- before restart:
SET GLOBAL innodb_max_dirty_pages_pct = 0;
SET GLOBAL innodb_buffer_pool_dump_now=ON;
-- after restart:
SET GLOBAL innodb_buffer_pool_load_now=ON;

Examples:

• To change a global setting (and don't have 8.0's persistent settings)
• To do an upgrade