[问题]
有台 MySQL 服务器不定时的会出现并发线程的告警, 从记录信息来看, 有大量 insert 的慢查询, 执行几十秒, 等待 flushing log, 状态 query end
[初步分析]
从等待资源来看, 大部分时间消耗在了 innodb_log_file 阶段, 怀疑可能是磁盘问题导致, 经过排查没有发现服务器本身存在硬件问题
后面开启线程上升时 pstack 的自动采集, 定位 MySQL 线程等待的位置.
[分析过程]
部署了 pstack 的自动抓取后, 出现过 6 次 thread concurrency>=50 的告警 (每次告警时会有大量的慢查询产生), 有 3 次抓到了现场.
并发线程升高时, 有 50 多个线程卡在 Stage_manager::enroll_for 函数, 处于 group commit 阶段
线程 0x519c5940 对应的 SQL 语句如下, 已经执行 18 秒
Stage_manager::enroll_for 函数的作用实现了多个线程在 flush_stage 阶段的排队. 简单来说, 对于一个分组的事务, 是被 leader 线程去提交的, 其他线程处于排队等待状态, 等待 leader 线程将该线程的事务提交完成.
如果第一个线程执行慢, 后面的线程都处于等待状态, 整组事务无法提交.
流程也可以理解如下,
Session A COMMIT--> 拿到锁 --> 进行 binlog 写 -->commit 完成
Session B COMMIT--> 等待锁 ---------------------------> 拿到锁 --> 进行 binlog 写 -->commit 完成
第一个线程为什么执行很慢, 分析了发生告警时间段的日志文件, 发现日志中存在 2 个 15M 和 20M 的大事务
查看日志明细, 存在 delete from 的大事务删除语句, 约包含 23W 条记录, ROW 模式下删除 23W 条记录, 会产生大约 20M 的日志文件, 刷盘时间较长, 阻塞了同一个分组下其他事务的提交.
事务的开始时间与告警时间吻合
积压的分组下事务集中刷盘, 反应到磁盘指标上可以看到在问题时间段的 disk_write_kbytes 指标出现明显的上升
[优化方案]
1, 建议开发避免使用 delete from 整表的大事务删除语句
[其他变通方案]
2, Binlog 记录的 ROW 模式下会产生大量的日志, 改为 MIXED 模式, 理论上也可以解决问题
3, 更换性能好的磁盘
来源: http://www.linuxidc.com/Linux/2018-10/154980.htm