MySQL预热InnoDB Buffer Pool缓冲池
5,705 total views, 1 views today
在生产中，重启MySQL后，会发现一段时间内SQL性能变差，然后最终恢复到原有性能。这是因为MySQL经常操作的热点数据都已经缓存到InnoDB Buffer Pool中，重启后，需要将热点数据逐渐缓存到 InnoDB Buffer Pool中，从磁盘读取数据自然没有从内存读取数据快。
MySQL重启后，将热点数据逐渐缓存到 InnoDB Buffer Pool的过程称为预热（warmup）。让应用系统自身慢慢通过SQL给InnoDB Buffer Pool预热成本很高，如果遇到高峰期极有可能带来一场性能灾难，业务卡顿不能顺利运营。
为了避免这种情况发生，MySQL 5.6 引入了数据预热机制。innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 两个参数控制了预热，不过默认都是关闭的，需要开启。MySQL 5.7则是默认开启。
一、InnoDB Buffer Pool 预热机制原理
1、关闭MySQL，执行导出 InnoDB Buffer Pool 数据到文件
- 在关闭MySQL时，将 InnoDB Buffer Pool 的数据保存到磁盘上，并且导出的文件是经过压缩的。
- ib_buffer_pool 是InnoDB Buffer Pool中数据保存到磁盘上的文件名，它的名字和路径受 innodb_buffer_pool_filename 控制。该文件默认保存在InnoDB的数据目录下。
- ib_buffer_pool 文件中保存了 tablespace IDs 和 page IDs 。
开启 “关闭MySQL导出 InnoDB Buffer Pool 数据”功能，如果需要永久执行，请加到my.cnf 。
|
1 |
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; #可动态修改 |
2、启动MySQL，执行ib_buffer_pool文件恢复到 InnoDB Buffer Pool
- 根据ib_buffer_pool 文件中的tablespace IDs 和 page IDs ，将数据恢复到 InnoDB Buffer Pool。tablespace IDs和page IDs信息来自INNODB_BUFFER_PAGE_LRU表。
- ib_buffer_pool 文件过旧没有关闭，MySQL会比对数据的新老，如果磁盘中page最近有过DML操作（如update），那么ib_buffer_pool中的数据不会加载到 InnoDB Buffer Pool中。
- 如果MySQL中有的page已经不存在了，那么加载机制会跳过这个page，不会把加载。
开启 “启动MySQL，InnoDB Buffer Pool历史数据导入”功能，建议直接加入到my.cnf 。
|
1 |
mysqld --innodb_buffer_pool_load_at_startup=ON; |
innodb_buffer_pool_load_at_startup不是动态变量，修改会报错，建议直接加入到my.cnf 。
|
1 2 |
mysql> set GLOBAL innodb_buffer_pool_load_at_startup=ON; ERROR 1238 (HY000): Variable 'innodb_buffer_pool_load_at_startup' is a read only variable |
3、Online保存和恢复InnoDB Buffer Pool数据
在MySQL运行时，可以将InnoDB Buffer Pool数据保存到磁盘，或者恢复到InnoDB Buffer Pool。
|
1 2 |
SET GLOBAL innodb_buffer_pool_dump_now=ON; SET GLOBAL innodb_buffer_pool_load_now=ON; |
4、查看执行保存、恢复 InnoDB Buffer Pool 的进展状态。主要用于Online保存和恢复场景。
<1>显示执行 InnoDB Buffer Pool 数据保存到磁盘的进展状态
|
1 2 3 4 5 6 7 |
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; +--------------------------------+--------------------------------------------------+ | Variable_name | Value | +--------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170112 17:26:02 | +--------------------------------+--------------------------------------------------+ 1 row in set (0.01 sec) |
或者
|
1 2 3 4 5 6 7 8 |
mysql> SELECT variable_value FROM information_schema.global_status -> WHERE variable_name = 'INNODB_BUFFER_POOL_DUMP_STATUS'; +--------------------------------------------------+ | variable_value | +--------------------------------------------------+ | Buffer pool(s) dump completed at 170112 17:26:02 | +--------------------------------------------------+ 1 row in set (0.00 sec) |
执行结果的状态值：
not started
Buffer pool(s) dump completed at 170112 17:26:02
<2>显示恢复 InnoDB Buffer Pool数据时的进展状态
|
1 2 3 4 5 6 7 |
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; +--------------------------------+--------------------------------------------------+ | Variable_name | Value | +--------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170112 17:31:22 | +--------------------------------+--------------------------------------------------+ 1 row in set (0.00 sec) |
或者
|
1 2 3 4 5 6 7 8 |
mysql> SELECT variable_value FROM information_schema.global_status WHERE -> variable_name = 'INNODB_BUFFER_POOL_LOAD_STATUS'; +--------------------------------------------------+ | variable_value | +--------------------------------------------------+ | Buffer pool(s) load completed at 170112 17:31:22 | +--------------------------------------------------+ 1 row in set (0.01 sec) |
执行结果的状态值：
not started
Buffer pool(s) load completed at 170112 17:31:22
5、终止 InnoDB Buffer Pool 恢复操作
|
1 |
SET GLOBAL innodb_buffer_pool_load_abort=ON; |
6、其他
如果MySQL 版本小于MySQL 5.6，可以手动对数据进行预热。对经常被使用的表进行count(*) 操作，也能起到数据预热的效果。
|
1 2 |
select count(*) t1; select count(*) t2; |
二、测试验证MySQL预热InnoDB Buffer Pool缓冲池
下面，就开启MySQL InnoDB Buffer Pool预热功能和不开启做一组比对实验。
1、环境
|
1 2 3 |
Server version: 5.6.32-log Source distribution Red Hat Enterprise Linux Server release 6.5 (Santiago) Monitor： zabbix |
2、不开启预热功能
MySQL重启后，InnoDB Buffer Pool中没有缓存任何业务数据。
执行sysbench压测，可以看到无论是tps、还是响应时间，都不乐观。随着压测的进行，热点数据换到到InnoDB Buffer Pool中，各项性能指标逐渐向好的趋势发展。这是因为数据库重启后，InnoDB Buffer Pool中的原有热点数据被刷出内存，执行sysbench后热点数据慢慢缓存到 InnoDB Buffer Pool中，而后tps、reads/s、writes/s 三项性能指标都变得越来越快，响应时间也变得越来越快，这都是InnoDB Buffer Pool中缓存的功劳。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[ 10s] threads: 128, tps: 106.09, reads/s: 1588.02, writes/s: 428.75, response time: 2432.00ms (99%) [ 20s] threads: 128, tps: 142.20, reads/s: 1981.78, writes/s: 566.29, response time: 2590.56ms (99%) [ 30s] threads: 128, tps: 174.70, reads/s: 2462.27, writes/s: 703.09, response time: 2464.98ms (99%) [ 40s] threads: 128, tps: 236.62, reads/s: 3301.88, writes/s: 943.48, response time: 2258.67ms (99%) [ 50s] threads: 128, tps: 342.10, reads/s: 4784.74, writes/s: 1367.38, response time: 1911.23ms (99%) [ 60s] threads: 128, tps: 546.79, reads/s: 7639.10, writes/s: 2187.37, response time: 1575.19ms (99%) [ 70s] threads: 128, tps: 831.39, reads/s: 11644.40, writes/s: 3325.54, response time: 1120.11ms (99%) [ 80s] threads: 128, tps: 1001.09, reads/s: 14035.39, writes/s: 4005.47, response time: 1089.36ms (99%) [ 90s] threads: 128, tps: 1065.38, reads/s: 14924.30, writes/s: 4260.51, response time: 967.30ms (99%) [ 100s] threads: 128, tps: 1163.39, reads/s: 16326.82, writes/s: 4679.38, response time: 763.82ms (99%) [ 110s] threads: 128, tps: 1430.38, reads/s: 20050.04, writes/s: 5738.73, response time: 264.96ms (99%) [ 120s] threads: 128, tps: 1202.41, reads/s: 16772.57, writes/s: 4766.02, response time: 395.83ms (99%) |
下图是执行压测前和压测后的InnoDB Buffer Pool的page分配情况，可以看出空闲page变少（黄色线），灌满数据的page越来越多（亮绿色线）。sysbench压测使用oltp脚本对数据做了修改，所以也有脏page的产生（紫色线）。
重启MySQL，观察InnoDB Buffer Pool中各项page的分配情况，InnoDB Buffer Pool中缓存的数据都被清理，见下图。
3、开启预热功能
|
1 |
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; |
关闭MySQL：自动将InnoDB Buffer Pool中的数据保存到磁盘上，MySQL关闭速度比不开启预热功能慢很多。在error日志中，记录了关闭过程。其中，ib_buffer_pool 是InnoDB Buffer Pool中数据导出到磁盘上的文件名。
|
1 2 3 4 5 6 7 |
2017-01-12 16:21:03 30379 [Note] InnoDB: Starting shutdown... 2017-01-12 16:21:03 7fd39e166700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool # 正在导出 2017-01-12 16:21:03 7fd39e166700 InnoDB: Buffer pool(s) dump completed at 170112 16:21:03 # 导出完毕 2017-01-12 16:22:04 30379 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool #清理buffer pool 2017-01-12 16:23:04 30379 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2017-01-12 16:24:04 30379 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2017-01-12 16:25:04 30379 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool |
导出的ib_buffer_pool 1.4M，其经过压缩。
|
1 2 |
# ll -th ib_buffer_pool -rw-rw---- 1 mysql mysql 1.4M 1月 12 16:21 ib_buffer_pool |
开启MySQL：下图中，pages free 、pages data都和关闭MySQL之前持平，说明关闭前的InnoDB Buffer Pool的数据都自动已经从ib_buffer_pool文件导入。
如何你没有zabbix监控，请使用命令查询InnoDB Buffer Pool各项page的使用情况
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show status like 'Innodb_buffer_pool_pages_%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | Innodb_buffer_pool_pages_data | 153801 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 3535 | | Innodb_buffer_pool_pages_free | 239414 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 393215 | +----------------------------------+--------+ 6 rows in set (0.01 sec) |
三、总结
MySQL InnoDB Buffer Pool的预热机制，在关闭MySQL时将InnoDB Buffer Pool中的数据保存到磁盘上的ib_buffer_pool文件里；在启动MySQL时，将ib_buffer_pool文件中的数据恢复到InnoDB Buffer Pool缓冲池中。这种机制解决了之前MySQL重启后SQL性能下降的问题。