Faster Restart by Preloading the InnoDB Buffer Pool


After you restart a busy server, there is typically a warmup period with steadily increasing throughput, as disk pages that were in the InnoDB buffer pool are brought back into memory as the same data is queried, updated, and so on. Once the buffer pool holds a similar set of pages as before the restart, many operations are performed in memory rather than involving disk I/O, and throughput stabilizes at a high level.

This feature shortens the warmup period by immediately reloading disk pages that were in the buffer pool before the restart, rather than waiting for DML operations to access the corresponding rows. The I/O requests can be performed in large batches, making the overall I/O faster. The page loading happens in the background, and does not delay the database startup.

In addition to saving the buffer pool state at shutdown and restoring it at startup, you can also save or restore the state at any time. For example, you might save the state of the buffer pool after reaching a stable throughput under a steady workload. You might restore the previous buffer pool state after running reports or maintenance jobs that bring data pages into the buffer pool that are only needed during the time period for those operations, or after some other period with a non-typical workload.

Although the buffer pool itself could be many gigabytes in size, the data that InnoDB saves on disk to restore the buffer pool is tiny by comparison: just the tablespace and page IDs necessary to locate the appropriate pages on disk. This information is derived from the information_schema table innodb_buffer_page_lru.

Because the data is cached in and aged out of the buffer pool the same as with regular database operations, there is no problem if the disk pages were updated recently, or if a DML operation involves data that has not yet been loaded. The loading mechanism skips any requested pages that no longer exist.

This feature involves the configuration variables:

and the status variables:

To save the current state of the InnoDB buffer pool, issue the statement: 

SET innodb_buffer_pool_dump_now=ON;

The underlying mechanism involves a background thread that is dispatched to perform the dump and load operations.

By default, the buffer pool state is saved in a file ib_buffer_pool in the InnoDB data directory.

Disk pages from compressed tables are loaded into the buffer pool in their compressed form. Uncompression happens as usual when the page contents are accessed in the course of DML operations. Because decompression is a CPU-intensive process, it is more efficient for concurrency to perform that operation in one of the connection threads rather than the single thread that performs the buffer pool restore operation.

Example 13.1. Examples of Dumping and Restoring the InnoDB Buffer Pool

Triggering a dump of the buffer pool manually: 

SET innodb_buffer_pool_dump_now=ON;

Specifying that a dump should be taken at shutdown: 

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';

Display progress of dump: 

mysql> SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';

or: 

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: 

SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

or: 

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:

Retornar