本文共 2192 字,大约阅读时间需要 7 分钟。
show full processlist发现大量的innodb表出现Waiting for table level lock,业务将近不可用
1 一开始当然是认为这是myisam引擎导致的,扫了一圈发现该db下确实有一个myisam表;
2 不过故障时无论是show processlist还是innodb_trx等结果看,都没有发现这个myisam表的记录;
3 排查了一圈又没有其他发现,于是又将怀疑的对象转到这个唯一存在的myisam表
4 鬼使神差地查了下information_schema.triggers表,发现了大量的触发器,触发器的意思大概是:无论什么表做什么更新,都将最后的时间戳写入到这个myisam表
5 基本断定原因就是它了,触发器更新myisam表,导致show processlist显示innodb处于Waiting for table level lock
1 sysbench准备一个表的数据,数据量很少即可,一个表即可
sysbench --report-interval=1 --num-threads=50 --max-time=2000 --test=sysbench/tests/db/oltp.lua --mysql-user='root' --mysql-password='mysql19-=' --oltp-table-size=50000 --mysql-host=10.9.158.2 --mysql-port=3306 --mysql-db=xx --max-requests=500000 --oltp-tables-count=1 prepare
2 准备完数据,登陆db创建1个myisam表
CREATE TABLE `tri_update` ( `tablename` char(64) NOT NULL DEFAULT '', `uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`tablename`)) engine =myisam;
3 创建1个更新触发器
delimiter $
mysql> CREATE trigger sbtest1_after_update after update on sbtest1 for each row begin update tri_update set uptime = now() where tablename = 'sbtest1'; end$
Query OK, 0 rows affected (0.00 sec)4 sysbench压测
sysbench --report-interval=1 --num-threads=50 --max-time=2000 --test=sysbench/tests/db/oltp.lua --mysql-user='root' --mysql-password='mysql19-=' --oltp-table-size=50000 --mysql-host=10.9.158.2 --mysql-port=3306 --mysql-db=xx --max-requests=500000 --oltp-tables-count=1 run
5 登陆db show processlist查看
可以看到故障就复现了,所有卡住的线程都是update SQL,并且显示的并不是myisam表中的tri_update,这无疑给故障排查带来了极大的困难。
6 我们进一步分析故障的原因,将引擎换成innodb,实际上性能也是存在问题的,但是效果会好很多(这个应该是buffer pool之类起的作用,行锁等待总比表锁好太多),因为这个触发器的功能是永远地更新同一条记录,存在行锁等待
alter table tri_update engine =innodb;
sysbench --report-interval=1 --num-threads=50 --max-time=2000 --test=sysbench/tests/db/oltp.lua --mysql-user='root' --mysql-password='mysql19-=' --oltp-table-size=50000 --mysql-host=10.9.158.2 --mysql-port=3306 --mysql-db=xx --max-requests=500000 --oltp-tables-count=1 run
但是这个时候可以看到,显示的SQL就是真正的瓶颈SQL了,即触发器中的update SQL
通过查看innodb_trx表 也能非常明显地得知当前处于锁等待
1 别用myisam,别用myisam!
2 业务层优化,避免同行更新,废弃这种逻辑,或者采用其他的方案,比如将一行拆成1000行,当A表更新时,随机更新B表的其中一行,避免大量的行锁竞争
3 算是一个小bug,为啥myisam情况下show出来的不是实际在执行的SQL