WL#5712: InnoDB: preload buffer pool
Status: Complete — Priority: Medium
This feature implements the possibility to dump and restore the contents of the InnoDB buffer pool(s) to/from disk. The main objective is to shorten the warm up times after the server has been restarted by loading the data that has been loaded before shutdown. This feature consists of two parts: == 1. Dumping the buffer pool or part of it on disk. Only tablespace id and page id are saved. Pages content is not saved. This is roughly saving the output of: SELECT space, page_number FROM information_schema.innodb_buffer_page_lru WHERE page_type = '...' AND table_name = '...' AND index_name = '...'; == 2. Loading the pages listed in a dump into the buffer pool. This is roughly calling buf_page_get() on all the pages from the list saved in the dump. It is possible to specify which tables/indexes to dump. (done) It is possible to trigger a dump manually at any time during runtime. (done) It is possible to specify that a dump should be done automatically during shutdown. (done) It is possible to specify that a dump should be loaded at startup (in background, not preventing normal operation). (done) It is possible to load a dump at any time during runtime. (done) It is possible to monitor the progress of the dump/load operations. (done) It is possible to abort a buffer pool(s) load. This worklog will satisfy the feature request under BUG#58819: preload InnoDB buffer pool option.
Triggering a dump of the buffer pool manually: mysql> SET innodb_buffer_pool_dump_now=ON; Specifying that a dump should be taken at shutdown: mysql> SET innodb_buffer_pool_dump_at_shutdown=ON; Specifying that a dump should be loaded at startup: mysql> SET innodb_buffer_pool_load_at_startup=ON; Trigger a load of the buffer pool manually: mysql> SET innodb_buffer_pool_load_now=ON; Specify which filename to use for storing the dump to and loading the dump from: mysql> SET innodb_buffer_pool_filename='filename'; Has a meaningful default value Display progress of dump: mysql> SHOW STATUS LIKE 'innodb_buffer_pool_dump_status'; or mysql> SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_DUMP_STATUS'; Outputs any of: not started, Dumping buffer pool 5/7, page 237/2873, Finished at 110505 12:18:02 Display progress of load: mysql> SHOW STATUS LIKE 'innodb_buffer_pool_load_status'; or mysql> SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_LOAD_STATUS'; Outputs any of: not started, Loaded 123/22301 pages, Finished at 110505 12:23:24 Abort a buffer pool load: mysql> SET innodb_buffer_pool_load_abort=ON; The followings are not implemented: * Specifying which tables/indexes to dump: * mysql> SET innodb_buffer_pool_dump_what= 'db1.t1.idx1,db1.t2.*,db2.*.*,db3.*.idx'; * Default is '*.*.*' which means everything.
A new background thread is introduced that is dedicated to both dump and restore. When "SET GLOBAL innodb_buffer_pool_load_now=ON;" or "SET GLOBAL innodb_buffer_pool_dump_now=ON;" is called, the corresponding "update" function sets a variable to indicate which action should be executed by the background thread and wakes it up. Dump is straight forward: for each buffer pool: iterate buf_pool->LRU and save the corresponding space id, page no and possibly zip size. Load is a little bit tricky because a non-existent page number or space id can be present in the dump file and we do not want to crash the server in this case (InnoDB would assert if we try to access a non-existent page). Thus a new function has been introduced: buf_read_page_async() which is analogous to buf_read_page(), with the following differences: 1. It calls buf_read_page_low() telling it to do async io 2. It calls buf_read_page_low() telling it not to crash on non-existent page, the "ignore errors" flag is ORed in the "mode" argument of buf_read_page_low(). Later it passes it to fil_io() which, when that flag is ON returns an error instead of crashing. During dump load we do not uncompress the pages and store them in the buffer pool in compressed-only form, leaving uncompression to happen in the threads that request the pages. A related snippet from IRC: 18:13 < inaam> but I think for preloading we don't need to do decompression ... 18:15 < inaam> it is ok for a page to be in just compressed form 18:15 < inaam> I think it is even better for preloading 18:16 < inaam> when the page is really needed buf_page_get_gen() will be called on it and it will get decompressed 18:16 < vd> so we will not have completely bring up the buffer pool in the state it was before restart 18:17 < vd> but if we assume reading the page from disk is much slower than decompressing it, then it is ok 18:17 < inaam> we don't know that, some of the pages may be only in compressed state 18:17 < inaam> some pages may be decompressed 18:19 < inaam> but that should be OK...by preloading we'll avoid sync IO which is the main bottleneck 18:19 < inaam> decompression is CPU intensive and it is better that it happens in the calling threads looking for the page instead of single threaded preloading
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.