欢迎来到站长教程网!

MySQL

当前位置:主页 > 数据库 > MySQL >

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

时间:2020-09-17|栏目:MySQL|点击:

在上篇文章《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)

上一篇:关于case when语句的报错问题详解

栏    目:MySQL

下一篇:如何修改Mysql中group_concat的长度限制

本文标题:解决MySQL 5.7中定位DDL被阻塞的问题

本文地址:www.dtcnnet.com/MySQL/31940.html

广告投放 | 联系我们 | 版权申明

重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

联系QQ:584415406 | 邮箱:584415406#qq.com(#换成@)

Copyright © 2015-2020 小白站长网 版权所有 苏ICP备20040415号