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