drop 和truncate table对高并发MySQL性能的影响

问题背景：

2019-08-24T23:47:09.361836+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 24915ms. The settings might not be optimal. (flushed=182 and evicted=0, during the time.)
2019-08-24T23:47:16.211740+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4849ms. The settings might not be optimal. (flushed=240 and evicted=0, during the time.)
2019-08-24T23:47:23.362286+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6151ms. The settings might not be optimal. (flushed=215 and evicted=0, during the time.)

在测试环境下模拟测试了drop 和truncate table对高并发MySQL性能的影响：

 

场景一、默认开启innodb_adaptive_hash_index，drop table操作

 

1. 在测试环境下创建了16张1500万条记录的测试表，单表容量约3.5G
2. 默认开启innodb_adaptive_hash_index，用olap压测模板运行一些时间，填充AHI使用内存
3. 17:34:08开始依次drop 三张3.5G的没有访问的表

4.用自定义脚本检测每秒Innodb_buffer_pool_pages_misc和Innodb_buffer_pool_pages_free指标的变化情况，可以看到3次drop table命令执行期间，Innodb_buffer_pool_pages_misc大量释放，Innodb_buffer_pool_pages_free值同时增长，释放和增加的内容总量基本一致，每秒约释放32M

5.可以看到执行drop table期间，QPS受到明显影响，从约7万的QPS掉到1万多，同时在17:34:11秒时没有采集到数据

场景二、关闭innodb_adaptive_hash_index，drop table 操作

 

1. 测试流程基本一致，唯一不同是关闭了AHI配置，set global innodb_adaptive_hash_index=OFF;
2. 压测一段时候，17:57:03开始drop 三张3.5G的没有访问的表
3. 由于关闭了AHI，在执行drop table期间Innodb_buffer_pool_pages_misc和Innodb_buffer_pool_pages_free指标变化非常小
4. QPS也几乎不受影响

 

场景三、开启innodb_adaptive_hash_index，truncate table操作

1. 测试流程基本一致，开启AHI的情况下，测试3张表的truncate table操作
2. 内存出现了集中释放，期间QPS也受短暂影响

【结论】

 

1. 在AHI默认开启的情况下，当drop 或truncate一张使用AHI空间较大的表时，调用执行AHI的清理动作，storage/innobase/buf/buf0lru.cc中的buf_LRU_drop_page_hash_for_tablespace函数，会导致buffer pool 可用空间的集中释放，期间会对MySQL服务器性能产生短暂影响（htlebookinginfo集群发生的情况是，drop 一张容量约在60G的表，释放内存3.7G，服务器hang死约1分钟）
2. 建议增加drop 或truncate清理的时间间隔，大圣已做过优化
3. 如果问题还会出现，可以在drop 或truncate表前关闭AHI

 

 

谢谢