1

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)
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81

1 Answers1

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
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • My intent : Buffer pool is limited area is we know.. So when we load the dumped data -> what will happen to already existing data in buffer pool ? All marked as dirty pages and ready for flush ? – Aman Aggarwal Apr 13 '21 at 07:08
  • @AmanAggarwal - No. The "dirtiness" is always an accurate indication of whether the block in the buffer_pool matches the corresponding block on disk. The "pointer" I refer to ties them together. And you have no way to modify the disk without first modifying the buffer_poll. The settings do not [I believe] let you load the buffer pool with different _data_, only with a different subset of pages. (And the latter is a bit sketchy.) Are you trying to "rollback"? Or simulate a "cold cache"? Or go back in history? Or what? – Rick James Apr 13 '21 at 15:24
  • I am trying to understand the use of these parameters. if we load the already dumped buffer pool - what will happen to existing buffer pool. I think this is very clear ask. – Aman Aggarwal Apr 15 '21 at 02:57
  • @AmanAggarwal - What gets saved is pointers to blocks on disk. What gets loaded is those blocks -- but not if they are already in the buffer_pool. In other words, nothing to worry about. – Rick James Apr 27 '22 at 21:34