MySQL Bugs: #68863: Innodb buffer pool warmup configuration improvements

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Warming up a database before putting it into service is an important task that we need to do on busy systems as otherwise performance will be less than optimal.

MySQL 5.6 provides a new feature to do this by optionally allowing the list of pages in the buffer pool to be saved on shutdown (or at any point in time) and then loaded on startup. Once the process has completed the server should be as warm as it was prior to it having been shutdown. This new functionality is good.

That said, the current functionality triggers the whole buffer pool to be loaded and on servers with a lot of memory this may take a long time. In fact for performance to be reasonable having a "25% warm server" may be enough and normal database activity will fill the remaining pages. Thus configuring the way the "warmup process" works would allow us to get to a "good enough state" more quickly.

How to repeat:
The current configuration parameters:

- innodb_buffer_pool_dump_at_shutdown
- innodb_buffer_pool_load_at_startup
- innodb_buffer_pool_dump_now
- innodb_buffer_pool_load_now

provide no control over the process. It's basically on or off.

Suggested fix:
So I would like to suggest a few improvements.
- provide an optional percentage of the buffer pool that should be loaded (or dumped if it's easier to store a smaller list). The buffer pool contents which are loaded should be the set of MRU first pages.
- provide an optional time after which the warm up process should stop
- I believe the current implementation uses a single thread to pull in the buffer pool pages. Potentially make the number of concurrent threads doing this I/O configurable to a larger number. [ I may be misunderstanding the implementation, as details in the documentation are not that clear on the implementation. ]
- Terminate the warmup process if the buffer pool is full. Other activity which may be taking place on the server may trigger pages to be pulled in. If the buffer pool fills up, and pages start to get evicted before the "warmup process" has completed, then you're throwing away useful pages. This should be avoided if it can happen.

In the end the intention here is to have more control over the process, and if possible to:
- speed up the process
- to load in as much of the buffer pool as the DBA thinks is necessary
- to be able to limit this process from doing too much (unnecessary) work

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.

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.

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.

5.7.5 has innodb_buffer_pool_dump_pct so that's good news.

See: bug#76656

Referencing this in Bug #78210.  Part of the FR could be handled by SHUTDOWN command.