Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 311.7Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Preloading the InnoDB Buffer Pool for Faster Restart

14.3.3.5 Preloading the InnoDB Buffer Pool for Faster Restart

To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools, you can save the InnoDB buffer pool state at server shutdown and restore the buffer pool to the same state at server startup.

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). The ability to restore the buffer pool to the pre-shutdown state shortens the warmup period as it allows you to immediately reload 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 and restore the buffer pool state at any time, while the server is running. 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 is tiny by comparison. Only tablespace IDs and page IDs necessary to locate the appropriate pages are saved to disk. This information is derived from the INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA table. By default, tablespace ID and page ID data is saved in a file named ib_buffer_pool, which is saved to the InnoDB data directory. The file name can be modified using the innodb_buffer_pool_filename configuration parameter.

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

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

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 during the course of DML operations. Because decompression is a CPU-intensive process, it is more efficient for concurrency to perform the operation in one of the connection threads rather than in the single thread that performs the buffer pool restore operation.

Saving the Buffer Pool State

To save the state of the InnoDB buffer pool at server shutdown, issue the statement:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

To save the state of the InnoDB buffer pool while MySQL server is running, issue the statement:

SET GLOBAL innodb_buffer_pool_dump_now=ON;
Restoring the Buffer Pool State

To restore the InnoDB buffer pool state at server startup, specify the --innodb_buffer_pool_load_at_startup option when starting the server:

mysqld --innodb_buffer_pool_load_at_startup=ON;

To restore the InnoDB buffer pool state while MySQL is running, issue the statement:

SET GLOBAL innodb_buffer_pool_load_now=ON;
Displaying Buffer Pool Dump Progress

To display progress when saving the InnoDB buffer pool state to disk, use one of the following options:

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

If the operation has not yet started, not started is returned. If the operation is complete, the completion time is printed (e.g. Finished at 110505 12:18:02). If the operation is in progress, status information is provided (e.g. Dumping buffer pool 5/7, page 237/2873).

Displaying Buffer Pool Load Progress

To display progress when loading the InnoDB buffer pool, use one of the following options:

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

If the operation has not yet started, not started is returned. If the operation is complete, the completion time is printed (e.g. Finished at 110505 12:23:24). If the operation is in progress, status information is provided (e.g. Loaded 123/22301 pages).

Aborting a Buffer Pool Load

To abort a buffer pool load operation, issue the statement:

SET GLOBAL innodb_buffer_pool_load_abort=ON;
14.3.3.5.1 Monitoring Buffer Pool Load Progress Using Performance Schema

As of MySQL 5.7.6, you can monitor buffer pool load progress using Performance Schema.

The following example demonstrates how to enable the stage/innodb/buffer pool load stage event instrument and related consumer tables to monitor buffer pool load progress.

For information about buffer pool dump and load procedures used in this example, see Section 14.3.3.5, “Preloading the InnoDB Buffer Pool for Faster Restart”. For information about Performance Schema stage event instruments and related consumers, see Section 22.9.5, “Performance Schema Stage Event Tables”.

  1. Enable the stage/innodb/buffer pool load instrument:

    mysql> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/buffer%';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  2. Enable the stage event consumer tables, which include events_stages_current, events_stages_history, and events_stages_history_long.

    mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
  3. Dump the current buffer pool state by enabling innodb_buffer_pool_dump_now.

    mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
    Query OK, 0 rows affected (0.00 sec)
  4. Check the buffer pool dump status to ensure that the operation has completed.

    mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
    *************************** 1. row ***************************
    Variable_name: Innodb_buffer_pool_dump_status
            Value: Buffer pool(s) dump completed at 150202 16:38:58
  5. Load the buffer pool by enabling innodb_buffer_pool_load_now:

    mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
    Query OK, 0 rows affected (0.01 sec)
  6. Check the current status of the buffer pool load operation by querying the Performance Schema events_stages_current table. The WORK_COMPLETED column shows the number of buffer pool pages loaded. The WORK_ESTIMATED column provides an estimate of the remaining work, in pages.

    mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
    +-------------------------------+----------------+----------------+
    | EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
    +-------------------------------+----------------+----------------+
    | stage/innodb/buffer pool load |           5353 |           7167 |
    +-------------------------------+----------------+----------------+

    The events_stages_current table returns an empty set if the buffer pool load operation has completed. In this case, you can check the events_stages_history table to view data for the completed event. For example:

    mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;
    +-------------------------------+----------------+----------------+
    | EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
    +-------------------------------+----------------+----------------+
    | stage/innodb/buffer pool load |           7167 |           7167 |
    +-------------------------------+----------------+----------------+
Note

You can also monitor buffer pool load progress using Performance Schema when loading the buffer pool at startup using innodb_buffer_pool_load_at_startup. In this case, the stage/innodb/buffer pool load instrument and related consumers must also be enabled at startup. For more information, see Section 22.2.2, “Performance Schema Startup Configuration”.


User Comments
Sign Up Login You must be logged in to post a comment.