首页 / Mysql / 解决MySQL 5.7中定位DDL被阻塞的问题

解决MySQL 5.7中定位DDL被阻塞的问题

在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况。

开启MDL的instrument

但是相关instrument并没有开启(MySQL 8.0是默认开启的),其可通过如下两种方式开启,

临时生效

修改performance_schema.setup_instrume nts表,但实例重启后,又会恢复为默认值。

永久生效

在配置文件中设置

测试场景

下面结合一个简单的Demo,来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。

这里,重点关注lock_status,”PENDING”代表线程在等待MDL,而”GRANTED”则代表线程持有MDL。

如何找出引起阻塞的会话

结合owner_thread_id,可以可到,是29号线程在等待27号线程的MDL,此时,可kill掉52号线程。

但需要注意的是,owner_thread_id给出的只是线程ID,并不是show processlist中的ID。如果要查找线程对应的processlist id,需查询performance_schema.threads表。

将这两张表结合,借鉴sys.innodb_lock _waits的输出,实际上我们也可以直观地呈现MDL的等待关系。

输出一目了然,DDL操作如果要获得MDL,执行kill 2即可。

官方的sys.schematablelock_waits

实际上,MySQL 5.7在sys库中也集成了类似功能,同样的场景,其输出如下,

具体分析下官方的输出,

只有一个alter table操作,却产生了两条记录,而且两条记录的kill对象竟然还不一样,对表结构不熟悉及不仔细看记录内容的话,难免会kill错对象。

不仅如此,如果有N个查询被DDL操作堵塞,则会产生N*2条记录。在阻塞操作较多的情况下,这N*2条记录完全是个噪音。

而之前的SQL,无论有多少操作被阻塞,一个alter table操作,就只会输出一条记录。

如何查看阻塞会话已经执行过的操作

但上面这个SQL也有遗憾,其blocking_query为NULL,而在会话1中,其明明已经执行了三个SQL。

这个与performance_schema.threads(类似于show processlist)有关,其只会输出当前正在运行的SQL,对于已经执行过的,实际上是没办法看到。

但在线上,kill是一个需要谨慎的操作,毕竟你很难知道kill的是不是业务关键操作?又或者,是个批量update操作?那么,有没有办法抓到该事务之前的操作呢?

答案,有。

即Performance Schema中记录Statement Event(操作事件)的表,具体包括

events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三个。

解决MySQL 5.7中定位DDL被阻塞的问题

三者的表结构完全一致,其中,events_statements_history又包含了events_statements_current的操作,所以我们这里会使用events_statements_history。

终极SQL如下,

从上面的输出可以看到,blocking_query中包含了会话1中当前事务的所有操作,按执行的先后顺序输出。

需要注意的是,默认情况下,events_statements_history只会保留每个线程最近的10个操作,如果事务中进行的操作较多,实际上也是没办法抓全的。

总结

以上所述是小编给大家介绍的解决MySQL 5.7中如何定位DDL被阻塞的问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我是攻城狮网站的支持!

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/4630.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏