解決MySQL 5.7中定位DDL被阻塞的問題
在上篇文章《MySQL表結(jié)構(gòu)變更，不可不知的Metadata Lock》中，我們介紹了MDL引入的背景，及基本概念，從“道”的層面知道了什么是MDL。下面就從“術(shù)”的層面看看如何定位MDL的相關(guān)問題。
在MySQL 5.7中，針對(duì)MDL，引入了一張新表performance_schema.metadata_locks，該表可對(duì)外展示MDL的相關(guān)信息，包括其作用對(duì)象，類型及持有等待情況。
開啟MDL的instrument
但是相關(guān)instrument并沒有開啟（MySQL 8.0是默認(rèn)開啟的），其可通過如下兩種方式開啟，
臨時(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，來看看在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)
這里，重點(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，無論有多少操作被阻塞，一個(gè)alter table操作，就只會(huì)輸出一條記錄。
如何查看阻塞會(huì)話已經(jīng)執(zhí)行過的操作
但上面這個(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í)行過的，實(shí)際上是沒辦法看到。
但在線上，kill是一個(gè)需要謹(jǐn)慎的操作，畢竟你很難知道kill的是不是業(yè)務(wù)關(guān)鍵操作？又或者，是個(gè)批量update操作？那么，有沒有辦法抓到該事務(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í)際上也是沒辦法抓全的。
總結(jié)
以上所述是小編給大家介紹的解決MySQL 5.7中如何定位DDL被阻塞的問題，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
相關(guān)文章
MySQL中使用JSON存儲(chǔ)數(shù)據(jù)的實(shí)現(xiàn)示例
本文主要介紹了MySQL中使用JSON存儲(chǔ)數(shù)據(jù)的實(shí)現(xiàn)示例,我們可以在MySQL中直接存儲(chǔ)、查詢和操作JSON數(shù)據(jù),具有一定的參考價(jià)值,感興趣的可以了解一下2023-09-09
MySQL數(shù)據(jù)庫(kù)中把int轉(zhuǎn)化varchar引發(fā)的慢查詢
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)中把int轉(zhuǎn)化varchar引發(fā)的慢查詢 的相關(guān)資料,非常不錯(cuò)具有參考借鑒價(jià)值，需要的朋友可以參考下2016-07-07
美團(tuán)DB數(shù)據(jù)同步到數(shù)據(jù)倉(cāng)庫(kù)的架構(gòu)與實(shí)踐
今天小編就為大家分享一篇關(guān)于美團(tuán)DB數(shù)據(jù)同步到數(shù)據(jù)倉(cāng)庫(kù)的架構(gòu)與實(shí)踐，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-02-02
MySql利用父id遞歸向下查詢子節(jié)點(diǎn)的方法實(shí)例
項(xiàng)目中遇到一個(gè)需求,要求查處菜單節(jié)點(diǎn)的所有節(jié)點(diǎn),在網(wǎng)上查了一下,大多數(shù)的方法用到了存儲(chǔ)過程,由于線上環(huán)境不能隨便添加存儲(chǔ)過程,所以自己寫一個(gè),這篇文章主要給大家介紹了關(guān)于MySql利用父id遞歸向下查詢子節(jié)點(diǎn)的相關(guān)資料,需要的朋友可以參考下2022-03-03
這篇文章主要給大家介紹了關(guān)于MySQL中表索引定義的方法與介紹，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-10-10
這篇文章主要介紹了MySQL如何修改字段類型和字段長(zhǎng)度，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-06-06
mysql實(shí)現(xiàn)事務(wù)的提交與回滾的實(shí)例詳解
在本篇文章中我們給大家分享一篇關(guān)于mysql實(shí)現(xiàn)事務(wù)的提交與回滾的實(shí)例內(nèi)容，有需要的朋友們可以參考學(xué)習(xí)下。2020-01-01
mysql千萬級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)
這篇文章主要介紹了mysql千萬級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
這篇文章主要介紹了在阿里面試中的一個(gè)問題MySQL死鎖問題的處理回答，對(duì)常見的死鎖案例進(jìn)行相關(guān)分析與探討，以及如何去盡可能避免死鎖給出一些建議2022-03-03
mysql安全啟動(dòng)腳本mysqld_safe詳細(xì)介紹
這篇文章主要介紹了mysql安全啟動(dòng)腳本mysqld_safe詳細(xì)介紹,mysqld_safe增加了一些安全特性,需要的朋友可以參考下2014-07-07