mysql预热缓冲池
在数据库压力很大的情况下，重启完数据库，通过手工执行下列语句，把热数据加载到innodb_buffer_pool缓冲池中进行预热，从而避免早高峰连接数升高，程序报错。selectcount(*) from user;
selectcount(*) 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)
# service mysql stop
Shuttingdown MySQL............
# cd mysql/
# ll
......
-rw-rw----1 mysql mysql 3703 Aug8 14:24 ib_buffer_pool
-rw-rw----1 mysql mysql 616562688 Aug8 14:24ibdata1
-rw-rw----1 mysql mysql50331648 Aug8 14:24 ib_logfile0
-rw-rw----1 mysql mysql50331648 Aug8 14:20 ib_logfile1
......
# tail -100f ../log/gflinux.err
2014-08-0814:24:32 16207 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 InnoDB: Shutdown completed; log sequence number 27441703699
加载：
# service mysql start
StartingMySQL....
# tail -100f ../log/gflinux.err
......
2014-08-0814:28:13 16696 InnoDB: 128 rollback segment(s) are active.
2014-08-0814:28:13 16696 InnoDB: 5.6.17 started; log sequence number 27441703699
2014-08-0814:28:13 16696 Semi-sync replication initialized for transactions.
2014-08-0814:28:13 16696 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以上版本。
页:
[1]