MYSQL LOCKS 监控

MYSQL 性能问题中,一定包含 LOCKS 的问题,我想没人反对,但如何监控他,其实说句实话,没有看到特别多的好的解决方法。有两个极端,一个是本身在MYSQL上的程序本身基础打得好,所以MYSQL 基本上很少有DEAD LOCKS , 另外一个,恐怕是根本使用MYSQL的人就不知道怎么监控DEAD LOCK ,所以没有意识到这个问题。

首先死锁的检测默认是被打开的,并且INNODB 是自动的检测事务的私说,自动的会馆或者自动终结私说,innodb会尝试选择较小的事务去进行roll back,根据 DML操作中的事务的尺寸来进行判断。

相对来说如果你的 innodb_table_locks = 1 并且autocommit = 0的情况下,那解决私说的问题就只能动用 innodb_lock_wait_timeout 这个参数了,或祈求GOD help.

当然如果你关闭了innodb_deadlock_detect 这个参数,那你的死锁也是无法被检测到的,另外 innodb_lock_wait_timeout 这个参数的设置,就可以看出掌握这个数据库系统的人员是否老道。

死锁的成因如果解释起来,简单的解释就是 session a 掌握了资源 a ,但在处理事务的时候,不光需要a ,还需要资源b, 而 session b 掌握了资源b,而处理时不光需要b ,还需要a, 但不幸的是,在同一个时间,session 掌握了a ,去提取b , 而 session b 掌握了b 去提取a ,也不知道是 session a 最后拿到了 资源b 而解锁,还是session b 拿到了 a而解锁,最后MYSQL系统滴滴答答吹喇叭。 好吧不要打我,最后的结果就是一个事务被回滚,系统记录错误信息。

所以监控dead locks 或 locks 其实是很重要的一件事

1 你知道那个语句和那个语句发生了死锁

2 你知道是否有挽救的可能性

如果入行久了,马上的反映就是 show engine innodb status 去查看死锁,是这就是mysql 5.x (5.6之前)的方法, 并且很好用。(不知道,那你还是百度一下吧)。

现在进行LOCKS(不光是死锁,包括类似blocked 的信息)的监控,一般都需要使用 performance_schema来进行,(什么性能损失,你是有多缺钱,多加一点CPU, 内存你会死吗,浪费5% -10%的性能,换你老板问你为什么的时候,你能痛快的写出报告,还是只能瞎编,你自己看着办)。

怎么做,很多种方法,我们先来说说传统的方法

1 innodb_print_all_deadlocks

这个参数你要打开哟,否则你真的只能傻乎乎去看 show engine innodb sttatus , 并且如果发生很多死锁,那你会遗漏的信息会很多。同时打开这个参数还可以在ERROR log 中看到相关的所有的死锁信息。

2 确认你的wait/lock/metadata/sql/mdl 要打开, 然后你就可以去查看

你的metadata_locks 表,当然如果你还知道 threads,那你可以联合这两个tables 来将那两个语句之间进行了,那个session 和 那个session之间的矛盾,以及相关的语句会一目了然。

SELECT

performance_schema.threads.PROCESSLIST_ID,

performance_schema.metadata_locks.*

FROM

performance_schema.threads,

performance_schema.metadata_locks

WHERE

performance_schema.threads.THREAD_ID =performance_schema.metadata_locks.OWNER_THREAD_ID;

3 PT 工具 或者自己写一个脚本,来自动记录可能发生的死锁。

例如我们可以通过 pt-deadlock-logger 来进行相关的死锁鉴定

4 动态的查看innodb的 deadlock 数,可以进行动态的监控,尤其在新上项目后,或者硬件变动后可以第一时间发现问题。当然你需要先将 innodb_locks 和 innodb_lock_waits 这两个表搞搞清楚

本文分享自微信公众号 - AustinDatabases(AustinDatabases)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券