mysql5.6 load_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 f

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 .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/.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