## Monitoring Buffer Pool Load Progress Using Performance Schema 

SET GLOBAL innodb_buffer_pool_load_abort=ON; 

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 15.8.3.6, “Saving and Restoring the Buffer Pool State” . For information about Performance Schema stage event instruments and related consumers, see Section 27.12.5, “Performance Schema Stage Event Tables” . 

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

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = ’YES’ WHERE NAME LIKE ’stage/innodb/buffer%’; 

2. Enable the stage event consumer tables, which include events_stages_current , events_stages_history , and events_stages_history_long . 

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = ’YES’ WHERE NAME LIKE ’%stages%’; 

3. Dump the current buffer pool state by enabling innodb_buffer_pool_dump_now . 

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON; 

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; 

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 performance_schema.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 performance_schema.events_stages_history; +-------------------------------+----------------+----------------+ | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | +-------------------------------+----------------+----------------+ | stage/innodb/buffer pool load | 7167 | 7167 | +-------------------------------+----------------+----------------+ 

