目录
一.故障现象... 1
二.初步分析... 2
三.排障过程... 2
1.排查是否QPS或insert并发请求上升导致问题发生... 2
2.排查是否锁资源等待或block导致了insert变慢... 3
3.排查是否表上无用索引导致的写入时间较长... 5
4、人工抓取perf,排查CPU上升期间的资源消耗... 5
5、疑似触发MySQL BUG,进一步分析... 6
四.优化过程... 8
1.初步优化方案... 8
2.删除一批无用索引,将服务器内存升级到80G.. 9
3.未达预期,还需继续优化... 11
4.热表索引分析... 11
5.随机GUID建立索引的性能测试... 13
6.热表索引优化方案... 14
7.前缀索引的性能测试... 14
8.删除热表上非顺序的二级索引... 16
五.最终优化方案... 17
六.总结... 18
一.故障现象
有台生产服务器间歇性CPU飙升,出现大量insert语句的慢查询,相关业务的响应时间随之大幅上升
二.初步分析
从监控报告来看,这台服务器的负载并不高
消耗时间高的SQL是insert系列语句
三.排障过程
1.排查是否QPS或insert并发请求上升导致问题发生
排查并发请求没并有突然升高,反而在问题时间段先大幅下降再小幅上升,这个现象说明MySQL在问题时间段的处理能力发生了下降
表的insert并发频率并没有大的波动
2.排查是否锁资源等待或block导致了insert变慢
以一句慢查询insert into为例,查询SQL执行的明细记录,这句SQL的执行时间在异常时间点达到12秒,对应locktime只有63微秒,排除了表锁等待,排查问题发生过程中,rowlock相关指标没有大幅上升,排除rowlock等待,也没有明显的block产生,正常执行时<3毫秒。
3.排查是否表上无用索引导致的写入时间较长
我们都知道表上大量的无用索引不仅浪费存储空间,也会增加数据写入的成本,因此在测试环境新建了相同的表,保留索引不变,测试索引维护成本的消耗
看到这句insert into正常执行时的各阶段的消耗,总体执行时间不到2ms
4、人工抓取perf,排查CPU上升期间的资源消耗
参考命令如下,
注意:下面命令在生产上执行时有较低概率会导致服务器hang死
#生成mysql进程10秒内资源消耗采样报告
sudo perf record -p `pidof mysqld` -g -o /tmp/perf.data sleep 10
#查看报告
sudo perf report -i /tmp/perf.data
CPU资源消耗占比较高的是ibuf_get_volume_buffered_count_func函数,它主要有2个功能,一是统计change buffer中对于同一page ,buffer了多少空间,二是在准备插入类型为IBUF_OP_DELETE的操作缓存时,会预估在apply完该page上所有的ibuf entry后还剩下多少记录。
5、疑似触发MySQL BUG,进一步分析
通过网上搜索,了解到有相关的BUG
下面是BUG描述
MySQL对每个表对象独立分配rw lock,当开启change buffer时,Innodb会频繁的创建dummy table(一种用于线程私有的简单的索引结构),这种dummy index事实上无需使用states_latch,因为他是线程私有的;但mysql没有做区分,在创建rw lock时,会加全局锁rw_lock_list_mutex来维护全局读写锁链表rw_lock_list。
也看到Ali关于这个问题的分析,同时也发现几个关联的BUG,直到MySQL5.7.6版本,问题才完全修复
四.优化过程
1.初步优化方案
1、 从以上分析来看,这个问题的产生与业务大量的二级索引频繁更新是有关系的,目前DB下共有5094个索引。因此我们决定先删除大量无用的索引,看效果是否明显
2、还有个导致问题的原因是内存远小于数据集,计划将innodb_buffer_pool_size从64G扩充到70G-90G之间
2.删除一批无用索引,将服务器内存升级到80G
将这台服务器的内存升到80G后,对比了这个集群昨天和今天的运行情况。
总的来看,增加内存后,CPU波动有所缓解,CPU的峰值和高消耗持续的时间有所降低,运行时的并发线程数也有降低,但问题并没有根本解决。
这台服务器(QPS约4000)的change buffer使用情况,缓存最大时达到23G(说明有大量的二级索引在写入时需要加入缓存进行合并),但是按照目前80G的buffer pool,change buffer的最大值只有20G,仍有瓶颈
对比另外写入频繁的(QPS约9000)一台服务器change buffer的使用情况,最大只有256K
3.未达预期,还需继续优化
评估下来后,需要继续删除热表的索引,下面对热表的索引情况做了进一步分析
4.热表索引分析
分析DB下热表的时候发现每次CPU飙升,逻辑IO消耗时间较长的表排名TOP 2都是固定的两张表,约占了所有表逻辑IO消耗时间的80%。
这两张表有什么特征会影响到逻辑IO的消耗时间,下面其中一张表为例
表结构中有一个row_key的字段,和开发确认,这个字段存储的是随机的GUID值,对应这个字段上建立了一个索引idx_row_key。
我们知道Innodb的聚集索引和二级索引都是一颗B+树,row_key字段建立索引,在插入数据维护索引时,以row_key值的大小做为页和记录的排序规则,随着大量并发随机GUID值的插入,
为了保持B+树的平衡,新插入的数据可能会带来大量的页拆分的操作,这时change buffer起到了关键的优化作用,将二级索引的操作缓存下来,并进行操作合并,减少二级索引的随机IO。
这两个表的容量使用情况,记录数达到3亿条,二级索引占用了较多的容量
5.随机GUID建立索引的性能测试
在测试环境中模拟类似的场景,使用sysbench并发256线程进行压测
结论:
1、 随着表的记录数的增多,当达到千万级以上的记录数时,随机GUID字段上的二级索引维护开销很明显,对插入性能的影响逐渐增大(从开始的30K的QPS下降到约3K)。
2、 删除GUID字段的二级索引后,QPS处理能力大幅上升,恢复到40K的QPS
6.热表索引优化方案
1、 从索引使用统计来看这张表上的idx_row_key索引实际并没有使用过,如果能直接删除,优化效果预计会比较明显
2、 如果业务逻辑上row_key的索引确实需要,折中的办法可以尝试创建前缀索引
对随机GUID值的前8个字符创建索引,这样只在B+树中存储字符串的前几个字符的编码,能节约一部分空间,减少字符串的比较时间,在一定程度上缓解排序和页拆分的问题,语法如下:
ALTER TABLE table1 ADD INDEX idx_row_key_prefix(row_key(8));
3、 业务上修改逻辑,将完全随机的GUID生成规则改为顺序的GUID生成规则
7.前缀索引的性能测试
下面测试表的数据约9000W,建立了两个随机GUID字段的前11个字符的前缀索引,QPS稳定在约10K左右。
8.删除热表上非顺序的二级索引
观察一天下来,CPU高消耗的问题基本消除。
当前change buffer的使用有较大幅度的减少,与删除索引前相比降低了约74%
五.最终优化方案
将两张大表改造为以时间字段为分区函数的分区表,分区表只保留最近30天的数据,改造完成后,从change buffer的使用来看,已经降低到16K
六.总结
对于记录数多的大表,表上如果存在随机的GUID字段或非顺序的字符串字段,如果这些类型上建立二级索引,对于频繁的增删改操作,会带来较高的维护成本。
当change buffer使用频繁,空间很大时,服务器性能也会出现大幅下降。这时我们可以通过删除热表的二级索引,改造分区表,清理大表数据,OPTIMIZE TABLE等操作来进行优化。