MySQL 5.6 dump/load buffer pool

Using MySQL Preloading Buffer Pool for fast restart.

什么场景下，会使用该功能。

当你的数据库系统较大，比如有128G物理内存，那么对应的buffer pool大小一般也在100G左右，那么数据库在正常运行的时候会在buffer pool中会有几十G的数据，在系统估计后，所有的数据都会被flush到数据文件。

那么当数据库在次启动的时候，之前已经在buffer pool的数据如要需要继续被访问，就不得不从新通过读数据文件，消耗I/O来放到buffer pool中。那么这时，buffer pool的dump/load就很有用。

 

另外注意，buffer pool中存储的数据(pages)，但是dump出来的只是tablesapce id 和 page id，并不是真实的数据，所以即使数据有几十G，也不会太慢。下面是官方说明:

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.

root@localhost:mysql.sock  16:29:43 [(none)]>show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |  在shutdwon mysql的时候是否开启dump功能
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |  确定dump file的名字
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |  在mysql启动的时候是否开启从dump文件load内容
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 104857600      |

修改配置文件,增加参数(可以动态全局设置SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;)

innodb_buffer_pool_dump_at_shutdown=on
innodb_buffer_pool_load_at_startup=on

 

重启mysql 服务

# /etc/init.d/mysql.server start

查看状态

root@localhost:mysql.sock  16:29:43 [(none)]>show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 104857600      |
+-------------------------------------+----------------+
8 rows in set (0.07 sec)

root@localhost:mysql.sock  16:29:49 [(none)]>SHOW STATUS LIKE 'innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 160630 16:29:39 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.12 sec)

 

[root@centospython data]# ll
total 359432
-rw-rw---- 1 mysql mysql 56 Mar 17 21:34 auto.cnf
-rw-rw---- 1 mysql mysql 2411 Jul 1 2016 ib_buffer_pool  生成的dump文件文件