数据库

解决MySQL 5.7中定位DDL被阻塞的问题
数据库 2024/11/5 佚名
3 2

在上篇文章《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表，但实例重启后，又会恢复为默认值。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

永久生效

在配置文件中设置

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

测试场景

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

session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> delete from slowtech.t1 where id=2;
Query OK, 1 row affected (0.00 sec)
session1> select * from slowtech.t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
session1> update slowtech.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2> alter table slowtech.t1 add c1 int; ##被阻塞
session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host  | db | Command | Time | State       | Info        |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| 2 | root | localhost | NULL | Sleep | 51 |         | NULL        |
| 3 | root | localhost | NULL | Query | 0 | starting      | show processlist     |
| 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
3 rows in set (0.00 sec)
session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| object_type | object_schema  | object_name | lock_type   | lock_duration | lock_status | owner_thread_id |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| TABLE  | slowtech   | t1    | SHARED_WRITE  | TRANSACTION | GRANTED  |    27 |
| GLOBAL  | NULL    | NULL   | INTENTION_EXCLUSIVE | STATEMENT  | GRANTED  |    29 |
| SCHEMA  | slowtech   | NULL   | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED  |    29 |
| TABLE  | slowtech   | t1    | SHARED_UPGRADABLE | TRANSACTION | GRANTED  |    29 |
| TABLE  | slowtech   | t1    | EXCLUSIVE   | TRANSACTION | PENDING  |    29 |
| TABLE  | performance_schema | metadata_locks | SHARED_READ   | TRANSACTION | GRANTED  |    28 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
6 rows in set (0.00 sec)

这里，重点关注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表。

session3> select * from performance_schema.threads where thread_id in (27,29)\G
*************************** 1. row ***************************
   THREAD_ID: 27
    NAME: thread/sql/one_connection
    TYPE: FOREGROUND
  PROCESSLIST_ID: 2
 PROCESSLIST_USER: root
 PROCESSLIST_HOST: localhost
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
 PROCESSLIST_TIME: 214
 PROCESSLIST_STATE: NULL
 PROCESSLIST_INFO: NULL
 PARENT_THREAD_ID: 1
    ROLE: NULL
  INSTRUMENTED: YES
   HISTORY: YES
 CONNECTION_TYPE: Socket
  THREAD_OS_ID: 9800
*************************** 2. row ***************************
   THREAD_ID: 29
    NAME: thread/sql/one_connection
    TYPE: FOREGROUND
  PROCESSLIST_ID: 4
 PROCESSLIST_USER: root
 PROCESSLIST_HOST: localhost
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
 PROCESSLIST_TIME: 172
 PROCESSLIST_STATE: Waiting for table metadata lock
 PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
 PARENT_THREAD_ID: 1
    ROLE: NULL
  INSTRUMENTED: YES
   HISTORY: YES
 CONNECTION_TYPE: Socket
  THREAD_OS_ID: 9907
2 rows in set (0.00 sec)

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

SELECT
 a.OBJECT_SCHEMA AS locked_schema,
 a.OBJECT_NAME AS locked_table,
 "Metadata Lock" AS locked_type,
 c.PROCESSLIST_ID AS waiting_processlist_id,
 c.PROCESSLIST_TIME AS waiting_age,
 c.PROCESSLIST_INFO AS waiting_query,
 c.PROCESSLIST_STATE AS waiting_state,
 d.PROCESSLIST_ID AS blocking_processlist_id,
 d.PROCESSLIST_TIME AS blocking_age,
 d.PROCESSLIST_INFO AS blocking_query,
 concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
 performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G

*************************** 1. row ***************************
    locked_schema: slowtech
    locked_table: t1
     locked_type: Metadata Lock
  waiting_processlist_id: 4
     waiting_age: 259
    waiting_query: alter table slowtech.t1 add c1 int
    waiting_state: Waiting for table metadata lock
  blocking_processlist_id: 2
    blocking_age: 301
    blocking_query: NULL
sql_kill_blocking_connection: KILL 2
1 row in set (0.00 sec)

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

官方的sys.schematablelock_waits

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

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
    object_schema: slowtech
     object_name: t1
   waiting_thread_id: 29
     waiting_pid: 4
    waiting_account: root@localhost
   waiting_lock_type: EXCLUSIVE
  waiting_lock_duration: TRANSACTION
    waiting_query: alter table slowtech.t1 add c1 int
   waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
   blocking_thread_id: 27
    blocking_pid: 2
   blocking_account: root@localhost
   blocking_lock_type: SHARED_READ
  blocking_lock_duration: TRANSACTION
  sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
*************************** 2. row ***************************
    object_schema: slowtech
     object_name: t1
   waiting_thread_id: 29
     waiting_pid: 4
    waiting_account: root@localhost
   waiting_lock_type: EXCLUSIVE
  waiting_lock_duration: TRANSACTION
    waiting_query: alter table slowtech.t1 add c1 int
   waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
   blocking_thread_id: 29
    blocking_pid: 4
   blocking_account: root@localhost
   blocking_lock_type: SHARED_UPGRADABLE
  blocking_lock_duration: TRANSACTION
  sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
2 rows in set (0.00 sec)

具体分析下官方的输出，

只有一个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如下，

SELECT
 locked_schema,
 locked_table,
 locked_type,
 waiting_processlist_id,
 waiting_age,
 waiting_query,
 waiting_state,
 blocking_processlist_id,
 blocking_age,
 substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
 sql_kill_blocking_connection
FROM
 (
  SELECT
   b.OWNER_THREAD_ID AS granted_thread_id,
   a.OBJECT_SCHEMA AS locked_schema,
   a.OBJECT_NAME AS locked_table,
   "Metadata Lock" AS locked_type,
   c.PROCESSLIST_ID AS waiting_processlist_id,
   c.PROCESSLIST_TIME AS waiting_age,
   c.PROCESSLIST_INFO AS waiting_query,
   c.PROCESSLIST_STATE AS waiting_state,
   d.PROCESSLIST_ID AS blocking_processlist_id,
   d.PROCESSLIST_TIME AS blocking_age,
   d.PROCESSLIST_INFO AS blocking_query,
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  FROM
   performance_schema.metadata_locks a
  JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
  AND a.OBJECT_NAME = b.OBJECT_NAME
  AND a.lock_status = 'PENDING'
  AND b.lock_status = 'GRANTED'
  AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  AND a.lock_type = 'EXCLUSIVE'
  JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
  JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
 ) t1,
 (
  SELECT
   thread_id,
   group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
  FROM
   performance_schema.events_statements_history
  GROUP BY thread_id
 ) t2
WHERE
 t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
    locked_schema: slowtech
    locked_table: t1
     locked_type: Metadata Lock
  waiting_processlist_id: 4
     waiting_age: 294
    waiting_query: alter table slowtech.t1 add c1 int
    waiting_state: Waiting for table metadata lock
  blocking_processlist_id: 2
    blocking_age: 336
    blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
sql_kill_blocking_connection: KILL 2
1 row in set, 1 warning (0.00 sec)

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

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

总结

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

标签：
mysql,5.7定位dll,被阻塞,mysql,5.7定位dll
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“解决MySQL 5.7中定位DDL被阻塞的问题”

暂无“解决MySQL 5.7中定位DDL被阻塞的问题”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com