Bug #68863 | Innodb buffer pool warmup configuration improvements | ||
---|---|---|---|
Submitted: | 4 Apr 2013 7:26 | Modified: | 17 Aug 2015 16:41 |
Reporter: | Simon Mudd (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
Version: | 5.6.10 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[4 Apr 2013 7:26]
Simon Mudd
[4 Apr 2013 7:50]
Kristian Koehntopp
I agree on Simons observation that the current buffer pool dump and load is of limited use for machines with a sufficiently large buffer pool - at the moment we have 96GB and 192GB machines, and a core dump of 192 GB on SSD takes 15 minutes to complete, 30 minutes on rotating rust. Something that dumps the part of the buffer pool that is needed for the machine to not drop out of the load balancer after bouncing it would be more useful. So a requirements specification that does not proscribe any particular implementation would probably look something like: 1. Dump (and load) a partial buffer pool, with the ability to select the percentage of the total buffer pool that is being dumped and loaded dynamically (i.e. 5%, 20% or 100% of the current state of the pool). 2. Choose the pages that are being dumped from the total pool in such a way that hot pages are being dumped. 3. On dump or load, arrange the page order so that the number of disk seeks on load is minimized.
[4 Apr 2013 12:48]
Simon Mudd
Also pointed out by one of my colleagues: - We clone servers a lot in order to build new systems. - Not all servers have the same memory size. - If not done already if the buffer pool list is larger than the size of the buffer pool on the server being started, or (if this makes a difference) the number of buffer pools is different, then the "warmup process" should just ignore some of the pages, but keeping the MRU ones.
[11 Jul 2013 11:49]
Simon Mudd
Further thoughts after using this a bit. I have also noticed that if you do a couple of successive shutdowns, perhaps due to a misconfiguration issue, you are likely to get mysqld to write out an incomplete buffer pool list as it hadn't had sufficient time to warm up completely. That throws away a previously good buffer pool file, which means you can not then use the feature. Some safeguard to avoid this (such as only write the file if the buffer pool is at least X% full) then you avoid the next warmup being incomplete. It's fine if the current behaviour is the default. I have no problem setting innodb_buffer_pool_dump_minimum_active_pct_size to 50. This has happened a couple of times while reconfiguring MySQL 5.6 boxes. It's not fatal, but it would be nice to avoid losing the data and thus having to warm up the server some other way. Perhaps too keep a number of copies of the saved file so that if something weird happens you may still have an old copy to use. so innodb_buffer_pool_dump_file_keep_copies = 1 (by default), and rotate to .1, .2, .3 when saving.
[9 Jan 2015 14:22]
Simon Mudd
5.7.5 has innodb_buffer_pool_dump_pct so that's good news.
[17 Aug 2015 16:41]
Simon Mudd
See: bug#76656
[25 Aug 2015 15:57]
Morgan Tocker
Referencing this in Bug #78210. Part of the FR could be handled by SHUTDOWN command.