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

作者:iVictor 时间:2024-01-14 10:20:01 

在上篇文章《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被阻塞的问题网站的支持!

来源:https://www.cnblogs.com/ivictor/p/9460147.html

标签:mysql,dll,阻塞
0
投稿

猜你喜欢

  • Python交换字典键值对的四种方法实例

    2022-11-10 04:34:04
  • python利用pd.cut()和pd.qcut()对数据进行分箱操作

    2022-03-26 07:57:45
  • 使用Python实现二终端网络可靠度

    2021-03-25 17:39:40
  • 浅谈vue中使用编辑器vue-quill-editor踩过的坑

    2024-04-10 13:46:00
  • 利用Python脚本批量生成SQL语句

    2023-06-10 10:02:37
  • Python中转换角度为弧度的radians()方法

    2021-08-11 16:21:59
  • Oracle 触发器的使用小结

    2009-05-24 19:54:00
  • 讲解设计应用程序时避免阻塞的八个准则

    2009-01-04 14:16:00
  • Python实现灰色关联分析与结果可视化的详细代码

    2023-08-03 15:01:56
  • asp读取数据库中数据到数组的类

    2007-09-16 18:19:00
  • JavaScript基础知识学习笔记

    2024-05-13 10:35:15
  • python函数map()和partial()的知识点总结

    2023-10-04 14:58:11
  • Ubuntu 设置开放 MySQL 服务远程访问教程

    2024-01-29 07:45:39
  • 浅谈Python实现Apriori算法介绍

    2021-09-14 19:10:22
  • Python socket网络编程TCP/IP服务器与客户端通信

    2023-09-13 01:46:02
  • Javascript命名禁区[110407.updated]

    2011-04-28 09:48:00
  • 解决usageerror: line magic function "%%time" not found问题

    2022-06-16 15:53:29
  • python发布模块的步骤分享

    2023-08-07 11:29:25
  • Python中的推导式使用详解

    2022-10-30 00:42:49
  • python+flask实现API的方法

    2023-12-18 17:25:03
  • asp之家 网络编程 m.aspxhome.com