[MYSQL] optimize和MTS的死锁BUG
原创
导读
遇到一个mysql从库延迟一直涨的事件, 然后show processlist结果如下:
(root@127.0.0.1) [(none)]> show processlist;
+----+-------------+-----------------+------+---------+------+---------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+---------------------------------------------------+------------------+
| 24 | system user | | NULL | Connect | 1126 | Waiting for master to send event | NULL |
| 25 | system user | | NULL | Connect | 737 | Waiting for slave workers to process their queues | NULL |
| 26 | system user | | | Connect | 828 | Waiting for table metadata lock | NULL |
| 27 | system user | | NULL | Connect | 784 | Waiting for preceding transaction to commit | NULL |
| 28 | system user | | NULL | Connect | 784 | Waiting for preceding transaction to commit | NULL |
| 29 | system user | | NULL | Connect | 784 | Waiting for preceding transaction to commit | NULL |
| 30 | root | 127.0.0.1:41912 | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------------+------+---------+------+---------------------------------------------------+------------------+分析
我们可以看到使用了MTS(multithreaded slave), 是4个并发.
其中thread/sql/slave_sql线程处于Waiting for slave workers to process their queues状态,
其中一个worker线程(id=26)处于Waiting for table metadata lock状态, 即等待其它线程释放MDL,我们可以解析相关的binlog找到对应的SQL, (解析出来对应的sql为: optimize table db1.sbtest1);
剩下3个worker线程(id=27,28,29)处于Waiting for preceding transaction to commit, 即等待其它线程提交.
A state that occurs when the replica thread is waiting for older worker threads to commit if slave_preserve_commit_order is enabled.
哎, 我们从库没得其它线程, 也就是说26要等 27,28,29中某几个释放MDL; 而27,28,29要等26提交. 这不就是死锁了么.....
为啥不查询performance_schema.metadata_locks呢? 因为没开...
我们还可以解析binlog(relay log)验证下
我们可以看到拥有相同的last_committed=155189的事务中就包含了我们的optimize命令. 而这些事务(含optimize命令)是可以并行回放的, 也就可能出现上面的死锁的. 而出现此现象的条件就是: 主库大量DML,并且期间有该表的optimizze操作.
这种情况从逻辑上来讲应该不算少,所以随便搜索下就能看到相关BUG:https://bugs.mysql.com/bug.php?id=93761
处理方法
既然是死锁, 那处理方法就简单了, 直接重启, 并去掉MTS, 直接单线程回放, 就可以了. 就不演示了, 有个细节要注意下下: 主库的binlog可能会被清理, 需要提前关注下, 不然修复之后还会遇到1236错误.....
官方也有相关的文档(Doc ID 2623029.1)记录解决方法(也是重启,干掉MTS)
生产修复的时候先是修改的slave_parallel_workers=1, 但是不行; 后面修改slave_parallel_workers=0之后才过了的.
复现方法
准备一套主从环境, 主库开启大量事务
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=123456 \
--mysql-db=db1 \
--db-driver=mysql \
--tables=10 \
--table_size=2000000 \
--report-interval=10 \
--threads=8 --time=120 \
run # prepare, run, cleanup然后主库跑下optimize命令
optimize table db1.sbtest1;
然后观察从库的进程状态即可.
总结
本次optimize和MTS的组合技BUG表面看是死锁导致的, 但这个触发方式更值得分析: 为啥业务高峰期间能有optimize命令执行呢? 这些DDL操作不应该是业务低峰期执行吗?
其它ddl和其它版本(本次是5.7.44)的我就不验证了(主要是我的'竞价实例'被回收了...), 读者可自行验证.
参考:
https://dev.mysql.com/doc/refman/5.7/en/replica-io-thread-states.html
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
