Bug #95603 Change innodb_buffer_pool_dump_pct to relate to Innodb_buffer_pool_pages_total
Submitted: 3 Jun 2019 0:15 Modified: 12 Jun 2019 13:04
Reporter: Yoseph Phillips Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7.24 EE OS:Any
Assigned to: CPU Architecture:Any

[3 Jun 2019 0:15] Yoseph Phillips
Description:
Change innodb_buffer_pool_dump_pct to relate to Innodb_buffer_pool_pages_total.

Currently when MySQL is restarted it is only dumping a percentage of the most recently used pages (based on the value of innodb_buffer_pool_dump_pct which is 25% by default).

When MySQL is restarted a second time before the buffer pool has been filled up (and the pages were dumped on shutdown) then it is again taking 25% of the most recently used pages.

One of our Enterprise Edition clients did a second restart after a few hours and ended up with it just trying to load about 6.25% (25% of 25%) of the original pages which caused huge performance issues while users were using the system.

This RFE is simply to change innodb_buffer_pool_dump_pct to mean a percentage of the Innodb_buffer_pool_pages_total instead.

That way by default after the first restart it will by dumping 25% of the total number of pages.
After another restart it will again try to dump that same number (which might be all of the pages that were loaded after the first restart).

How to repeat:
Load a large percentage of pages into the InnoDB Buffer Pool.
Restart MySQL.
Check the InnoDB Buffer Pool Status for the number of pages getting loaded.
After they have finished loading, Restart MySQL for a second time.
Check the InnoDB Buffer Pool Status for the number of pages getting loaded.
You will notice that it is just a small fraction of the original number of pages, and not the 25% as one would logically expect.

Suggested fix:
Change innodb_buffer_pool_dump_pct to mean a percentage of the Innodb_buffer_pool_pages_total instead.
[12 Jun 2019 13:04] MySQL Verification Team
Hi Mr. Phillips,

Thank you for your bug report.

I consider that this would be a very welcome new feature.

Verified as reported.
[12 Jun 2019 13:07] MySQL Verification Team
I would like to let you know that we can not predict when will this feature be implemented. This is due to the fact that scheduling process is strictly internal for each team, so that we do not know the schedule. When this feature is implemented, it will be printed on this page.