首页 > 代码库 > mysql预热缓冲池
mysql预热缓冲池
在数据库压力很大的情况下，重启完数据库，通过手工执行下列语句，把热数据加载到innodb_buffer_pool缓冲池中进行预热，从而避免早高峰连接数升高，程序报错。
| select count(*) from user; select count(*) from ***; ...... |
在mysql5.6里为了解决上述问题，提供了一个新特性来快速预热buffer_pool缓冲池，只需在my.cnf中加入如下命令：
| 参数 | 注释 |
innodb_buffer_pool_dump_at_shutdown=1 | 关闭时dump热数据到本地磁盘 |
innodb_buffer_pool_dump_now=1 | 采用手工方式把热数据dump到本地磁盘 |
innodb_buffer_pool_load_at_start=1 | 在启动时把热数据加载到内存 |
innodb_buffer_pool_load_now=1 | 采用手工方式把热数据加载到内存 |
mysql>show variables like ‘innodb_buffer_pool%‘;
+-------------------------------------+----------------+
|Variable_name |Value |
+-------------------------------------+----------------+
|innodb_buffer_pool_dump_at_shutdown | OFF |
|innodb_buffer_pool_dump_now |OFF |
|innodb_buffer_pool_filename |ib_buffer_pool |
|innodb_buffer_pool_instances |8 |
|innodb_buffer_pool_load_abort |OFF |
|innodb_buffer_pool_load_at_startup |OFF |
| innodb_buffer_pool_load_now | OFF |
|innodb_buffer_pool_size |786432000 |
+-------------------------------------+----------------+
8 rows inset (0.01 sec)
[root@gflinuxmysql]# service mysql stop
Shuttingdown MySQL............ [ OK ]
[root@gflinuxmysql]# cd mysql/
[root@gflinuxmysql]# ll
......
-rw-rw----1 mysql mysql 3703 Aug 8 14:24 ib_buffer_pool
-rw-rw----1 mysql mysql 616562688 Aug 8 14:24ibdata1
-rw-rw----1 mysql mysql 50331648 Aug 8 14:24 ib_logfile0
-rw-rw----1 mysql mysql 50331648 Aug 8 14:20 ib_logfile1
......
[root@gflinuxmysql]# tail -100f ../log/gflinux.err
2014-08-0814:24:32 16207 [Note] InnoDB: Starting shutdown...
2014-08-0814:24:32 62ad2b90 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-08-0814:24:32 62ad2b90 InnoDB: Buffer pool(s) dump completed at 140808 14:24:32
2014-08-0814:24:34 16207 [Note] InnoDB: Shutdown completed; log sequence number 27441703699
加载：
[root@gflinuxmysql]# service mysql start
StartingMySQL.... [ OK ]
[root@gflinuxmysql]# tail -100f ../log/gflinux.err
......
2014-08-0814:28:13 16696 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-0814:28:13 16696 [Note] InnoDB: 5.6.17 started; log sequence number 27441703699
2014-08-0814:28:13 16696 [Note] Semi-sync replication initialized for transactions.
2014-08-0814:28:13 16696 [Note] Semi-sync replication enabled on the master.
2014-08-0814:28:13 62adab90 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
2014-08-0814:28:13 62adab90 InnoDB: Buffer pool(s) load completed at 140808 14:28:13
在关闭mysql时，会把内存中的热数据保存在磁盘的ib_buffer_pool文件中，该文件位于数据目录下。
在启动mysql时，会自动加载热数据到buffer_pool缓冲池里。
注意在正常关闭mysql服务或者pkill mysql时，才会把热数据从内存导出，机器宕机或者pkill -9 mysql是不会导出的。
注意：仅适用于mysql5.6以上版本。
mysql预热缓冲池