主頁(yè) > 知識(shí)庫(kù) > 解決MySQL 5.7中定位DDL被阻塞的問(wèn)題

解決MySQL 5.7中定位DDL被阻塞的問(wèn)題
熱門(mén)標(biāo)簽：陜西400電話如何申請(qǐng) 商家地圖標(biāo)注哪個(gè)好 承德電腦地圖標(biāo)注 深圳 德惠市地圖標(biāo)注 遵義地圖標(biāo)注app 合肥營(yíng)銷(xiāo)外呼系統(tǒng)收費(fèi) 地圖標(biāo)注賺錢(qián)真假 外呼系統(tǒng)從哪買(mǎi)

在上篇文章《MySQL表結(jié)構(gòu)變更，不可不知的Metadata Lock》中，我們介紹了MDL引入的背景，及基本概念，從“道”的層面知道了什么是MDL。下面就從“術(shù)”的層面看看如何定位MDL的相關(guān)問(wèn)題。

在MySQL 5.7中，針對(duì)MDL，引入了一張新表performance_schema.metadata_locks，該表可對(duì)外展示MDL的相關(guān)信息，包括其作用對(duì)象，類型及持有等待情況。

開(kāi)啟MDL的instrument

但是相關(guān)instrument并沒(méi)有開(kāi)啟（MySQL 8.0是默認(rèn)開(kāi)啟的），其可通過(guò)如下兩種方式開(kāi)啟，

臨時(shí)生效

修改performance_schema.setup_instrume nts表，但實(shí)例重啟后，又會(huì)恢復(fù)為默認(rèn)值。

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

永久生效

在配置文件中設(shè)置

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

測(cè)試場(chǎng)景

下面結(jié)合一個(gè)簡(jiǎn)單的Demo，來(lái)看看在MySQL 5.7中如何定位DDL操作的阻塞問(wèn)題。

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)

這里，重點(diǎn)關(guān)注lock_status，"PENDING"代表線程在等待MDL，而"GRANTED"則代表線程持有MDL。

如何找出引起阻塞的會(huì)話

結(jié)合owner_thread_id，可以可到，是29號(hào)線程在等待27號(hào)線程的MDL，此時(shí)，可kill掉52號(hào)線程。

但需要注意的是，owner_thread_id給出的只是線程ID，并不是show processlist中的ID。如果要查找線程對(duì)應(yīng)的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)

將這兩張表結(jié)合，借鑒sys.innodb_lock _waits的輸出，實(shí)際上我們也可以直觀地呈現(xiàn)MDL的等待關(guān)系。

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，執(zhí)行kill 2即可。

官方的sys.schematablelock_waits

實(shí)際上，MySQL 5.7在sys庫(kù)中也集成了類似功能，同樣的場(chǎng)景，其輸出如下，

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)

具體分析下官方的輸出，

只有一個(gè)alter table操作，卻產(chǎn)生了兩條記錄，而且兩條記錄的kill對(duì)象竟然還不一樣，對(duì)表結(jié)構(gòu)不熟悉及不仔細(xì)看記錄內(nèi)容的話，難免會(huì)kill錯(cuò)對(duì)象。

不僅如此，如果有N個(gè)查詢被DDL操作堵塞，則會(huì)產(chǎn)生N*2條記錄。在阻塞操作較多的情況下，這N*2條記錄完全是個(gè)噪音。

而之前的SQL，無(wú)論有多少操作被阻塞，一個(gè)alter table操作，就只會(huì)輸出一條記錄。

如何查看阻塞會(huì)話已經(jīng)執(zhí)行過(guò)的操作

但上面這個(gè)SQL也有遺憾，其blocking_query為NULL，而在會(huì)話1中，其明明已經(jīng)執(zhí)行了三個(gè)SQL。

這個(gè)與performance_schema.threads（類似于show processlist）有關(guān)，其只會(huì)輸出當(dāng)前正在運(yùn)行的SQL，對(duì)于已經(jīng)執(zhí)行過(guò)的，實(shí)際上是沒(méi)辦法看到。

但在線上，kill是一個(gè)需要謹(jǐn)慎的操作，畢竟你很難知道kill的是不是業(yè)務(wù)關(guān)鍵操作？又或者，是個(gè)批量update操作？那么，有沒(méi)有辦法抓到該事務(wù)之前的操作呢？

答案，有。

即Performance Schema中記錄Statement Event（操作事件）的表，具體包括

events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三個(gè)。

三者的表結(jié)構(gòu)完全一致，其中，events_statements_history又包含了events_statements_current的操作，所以我們這里會(huì)使用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中包含了會(huì)話1中當(dāng)前事務(wù)的所有操作，按執(zhí)行的先后順序輸出。

需要注意的是，默認(rèn)情況下，events_statements_history只會(huì)保留每個(gè)線程最近的10個(gè)操作，如果事務(wù)中進(jìn)行的操作較多，實(shí)際上也是沒(méi)辦法抓全的。

總結(jié)

以上所述是小編給大家介紹的解決MySQL 5.7中如何定位DDL被阻塞的問(wèn)題，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章:
  • MySQL8.0 如何快速加列
  • Mysql Online DDL的使用詳解
  • MySQL DDL 引發(fā)的同步延遲該如何解決
  • 詳解MySQL8.0原子DDL語(yǔ)法
  • MySQL在線DDL工具 gh-ost的原理解析
  • MySQL ddl語(yǔ)句的使用
  • Mysql DDL常見(jiàn)操作匯總
  • 解析MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL
  • MySQL數(shù)據(jù)定義語(yǔ)言DDL的基礎(chǔ)語(yǔ)句
  • MySQL8.0 DDL原子性特性及實(shí)現(xiàn)原理
  • MySQL在線DDL gh-ost使用總結(jié)
  • MySQL8.0新特性之支持原子DDL語(yǔ)句
  • MySQL曝中間人攻擊Riddle漏洞可致用戶名密碼泄露的處理方法
  • MySQL 8.0 Online DDL快速加列的相關(guān)總結(jié)

標(biāo)簽：貴州 新余 三門(mén)峽 揚(yáng)州 巴中 南陽(yáng) 贛州 商丘

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《解決MySQL 5.7中定位DDL被阻塞的問(wèn)題》，本文關(guān)鍵詞  解決,MySQL,5.7,中,定位,DDL,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《解決MySQL 5.7中定位DDL被阻塞的問(wèn)題》相關(guān)的同類信息！
  • 本頁(yè)收集關(guān)于解決MySQL 5.7中定位DDL被阻塞的問(wèn)題的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章