背景
实现方式:mysql通过保存buffer pool中的page的tablespaceid和pageid到磁盘来保存buffer pool的状态。这些数据保存在名为ib_buffer_pool的文件中,而ib_buffer_pool保存在innodb data directory中。数据文件的名字也可以通过修改参数innodb_buffer_pool_filename来修改。
一、有两种方式dump buffer pool状态:
1、shutdown的时候。在shutdown之前需要设置一下全局变量,如下:
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;2、server running的时候。在服务器还在运行的时候设置全局变量,如下:
SET GLOBAL innodb_buffer_pool_dump_now=ON;
二、同样,有两种方式load buffer pool状态:
1、server启动的时候。server启动的时候带上命令行选项,如下:
mysqld --innodb_buffer_pool_load_at_startup=ON;2、server运行的时候。server运行的时候设置下全局变量,如下:
SET GLOBAL innodb_buffer_pool_load_now=ON;
三、也可以停止buffer pool 的 loading,设置如下:
SET GLOBAL innodb_buffer_pool_load_abort=ON;四、监控buffer pool 的dump和load过程。
1、监视dump过程,可做如下设置:
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';或者
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).
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';或者
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).
五、mysql5.7.6开始,也可使用performance_schema来监控这两个过程