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