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:(none) 08:52:07]> 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        | 8              |
| 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             | 134217728      |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

1. To save the state of the InnoDB buffer pool at server shutdown, issue the statement:
[root@localhost:(none) 08:52:14]> SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
Query OK, 0 rows affected (0.00 sec)

2. Stop MySQL service.
[root@localhost:(none) 08:54:06]> exit
Bye
[mysql@server mysql]$ /etc/init.d/mysql.server stop
Shutting down MySQL..                                      [  OK  ]

3. Check Dump file.
[mysql@slc4-ra0002pxe159 data]$ ls -l
total 176188
-rw-rw---- 1 mysql mysql       56 Jan 13 20:56 auto.cnf
drwx------ 2 mysql mysql     4096 Jan 14 00:18 db01
-rw-rw---- 1 mysql mysql     1774 Jan 14 20:55 ib_buffer_pool
-rw-rw---- 1 mysql mysql 79691776 Jan 14 20:55 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jan 14 20:55 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jan 13 20:51 ib_logfile1
drwx------ 2 mysql mysql     4096 Jan 13 20:51 mysql
drwx------ 2 mysql mysql     4096 Jan 13 20:51 performance_schema
-rw-r----- 1 mysql mysql    31591 Jan 14 20:55 slc4-ra0002pxe159.stratus.slc.ebay.com.err
drwxr-xr-x 2 mysql mysql     4096 Jan 13 20:38 test

4. To restore the InnoDB buffer pool state at server startup with --innodb_buffer_pool_load_at_startup option.
[mysql@server1 mysql]$ mysqld_safe --innodb_buffer_pool_load_at_startup=ON
150114 20:56:18 mysqld_safe Logging to '/usr/local/mysql/data/slc4-ra0002pxe159.stratus.slc.ebay.com.err'.
150114 20:56:18 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

5. Login mysql and check retore status.
[root@localhost:(none) 08:56:41]> SHOW STATUS LIKE 'innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 150114 20:56:18 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

6. Add below contents to configuration file to enable automatic dump/load acorss mysql restart.
innodb_buffer_pool_dump_at_shutdown=on
innodb_buffer_pool_load_at_startup=on