接到用户反馈,应用侧监控平台突然报出大量慢查询。比较诡异的事,这些sql平时执行非常快,现在每隔1分钟都会出现慢查询告警,极其规律。
下面记录了故障分析过程。
一、数据库信息
架构:mysql mgr单主模式
版本:mysql 5.7.31
二、排查原因
查看数据库状态
依次查看数据库相关指标
show processlist; #只有200多个,且大多为sleep状态
show engine innodb status; #正常,没发现明显问题,也没有锁
select * from innodb_trx; #查看长事务,也没发现问题
然后我们根据开发同事提供的告警时间点,在数据库复现了这个现象。主库每隔1分钟,就出现严重的性能抖动,正常秒级执行完成的sql耗时陡增到15秒。
查监控
发现mgr主备延迟严重,在其中一个secondary节点有大量事务堆积。
出现这个问题是因为备份的中继日志重放发生了异常。
然后登录备库,查看连接数(执行show processlist),发现备库有一个执行耗时超过13h的查询,阻塞了主从同步线程alter table语句, 有一个mdl锁等待,很明显,同步线程完全被卡主了。
与开发沟通,了解到他们给字段修改索引时,先删除旧索引再添加新索引,导致变更期间出现了大量的阻塞,也正是这个原因,引发了从库的查询sql突然没有索引而变的非常缓慢,进而阻塞了同步线程中的ddl语句。同步线程被阻塞后,新增索引无法第一时间同步到从库,酿成了本次惨案。
注:修改索引时,务必先新增索引,然后查看执行计划,确保走了新索引,然后再删除旧索引。
三、问题分析
对于60s性能抖动,首先想到的是mgr的流控功能,查看了mgr三个节点,都是关闭状态。
然后,进一步分析mgr备节点sql线程阻塞问题。 官方mgr是木桶原理,取决于最慢的节点,认证数据库会保持认证数据库信息,由最慢节点的gtid来删除过期的认证数据库信息,当备库复制线程卡住时,内存使用率会不断升高,甚至引发OOM。
此外,认证数据库数据堆积,会导致认证数据库清理出现卡顿,也会出现mgr里经典的60s性能抖动问题。
查阅资料看到的一段话
扩展:认证数据库清理逻辑
数据库认证信息主要存放每条dml的主键信息,如下示例
row1: 主键 GTID:1
row2: 主键 GTID:1-2
row3: 主键 GTID:1-3
row4: 主键 GTID:1-4
row5: 主键 GTID:1-5
row6: 主键 GTID:1-6
row6: 主键 GTID:1-7
假设主库保存的event为GTID:1-7,备份为GTID:1-2,那么根据mgr木桶原理,会清理GTID:1-2。当由于延迟堆积的event非常多是,就会影响认证数据库的清理成本。
由此可见,影响mgr 60s性能抖动的原因有2种。第一种,mgr开启流控功能;第二种,mgr备库复制延迟导致数据库认证信息堆积。
运维建议:
1、备库尽可能不对外提供服务;
2、减少长事务,它很容易引发锁等待,比如global read lock、table metadata lock、flush;
3、业务敏感的数据库,尽可能不开启流控,避免流控触发性能抖动;
4、重视mgr复制延迟,建议监控该指标。