MySQL Bugs: #76656: Please don't save the buffer pool if it's almost empty

Bug #76656	Please don't save the buffer pool if it's almost empty
Submitted:	10 Apr 2015 11:04	Modified:	17 Aug 2015 16:44
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	buffer_pool, innodb, upgrade

Description:
I've seen this before, but the issue comes up and is especially noticeable when doing upgrades. We're about to embark on a 5.7 upgrade spree soon so this is going to bite hard.

If you do 2 successive restarts on MySQL and have innodb_buffer_pool_dump_at_shutdown = 1 then MySQL will have saved an empty page list prior to the second restart which will make the warm up process not work.

You'll end up with a box that you have to warm up manually or have to take special action to prevent this happening.

How to repeat:
I want to upgrade 5.6.24 to 5.7.7, so I do the following:
(1) stop 5.6.24
(2) replace the binaries with 5.7.7 binaries and maybe adjust the config a bit
(3) start mysqld with --skip-slave-start --skip-networking (so I won't be bothered by connections)
(4) run mysql_upgrade
(5) restart mysqld normally

I notice that the server is cold and won't warm itself up.
Why? because the stop after running mysql_upgrade is done shortly after mysql has started up. The buffer pool is _almost emtpy_ (especially if no client access is happening and replication is disabled), when I shut down mysql again it saves an empty buffer pool list, and that's what is used for doing the normal startup. Basically it prevents 5.7.7 (in this case) warming up normally.

That's a huge pain and will affect everyone who does a major version upgrade as you can't really avoid the mysql_upgrade step, or the _required_ restart after completing that.

Suggested fix:
On a server I was looking at I had:

$ grep buffer_pool /etc/my.cnf
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_size = 76G
# innodb_buffer_pool_size defined from memorysize = 94 GB

I'd like to see an extra option:

innodb_buffer_pool_dump_threshold_minimum_pct = 25 # default value ?

That is prevent InnoDB from dumping a list if the buffer pool is to small (not full).

I guess I could go and change innodb_buffer_pool_dump_at_shutdown = 0, but I have to remember to do this every time. For upgrades and for other maintenance jobs. It's a recipe for not doing it when mysqld could be smart enough to help you.

So please consider adding this "small feature" to 5.7 prior to it going GA.

If you do this please adjust the logging:

Currently it says:

2015-04-10T10:34:12.475906Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 150410 12:34:12

2015-04-10T10:35:02.481751Z 0 [Note] InnoDB: Buffer pool(s) load completed at 150410 12:35:02

Add the size of the buffer pool that's been saved. Pages will do but maybe provide an equivalent value in MB/GB as that's easier to understand.
The 2nd timestamp is superfluous so remove it (and show how long it took). Something along the lines of:

2015-04-10T10:34:12.475906Z 0 [Note] InnoDB: Buffer pool(s) dump of XXX pages (YYY MB) completed in 9.999 seconds  # this will be fast

2015-04-10T10:35:02.481751Z 0 [Note] InnoDB: Buffer pool(s) load of XXX pages (YYY MB) completed in 999.9 seconds   # this will obviously take longer

As a workaround can one set innodb_buffer_pool_dump_pct=100 before the second restart to avoid reducing the existing 25% to 6.25%?

See also: bugs#68863

Sorry, See also: bug#68863

see also FR
https://bugs.mysql.com/bug.php?id=95603