极乐门资源网

解决MySQL 5.7中定位DDL被阻塞的问题
数据库 2024/12/23 佚名
2 0
极乐门资源网 Design By www.ioogu.com

在上篇文章《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
极乐门资源网 Design By www.ioogu.com
极乐门资源网 免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
极乐门资源网 Design By www.ioogu.com

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

暂无解决MySQL 5.7中定位DDL被阻塞的问题的评论...
www.ioogu.com 极乐门资源网
139,976影音资源
144,792福利资源
21,817软件资源
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
极乐门资源网 Design By www.ioogu.com