Mysql中USE DB堵塞故障分析

144次閱讀

沒有評論

共計 8865 個字符，預計需要花費 23 分鐘才能閱讀完成。

這篇文章主要講解了“Mysql 中 USE DB 堵塞故障分析”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“Mysql 中 USE DB 堵塞故障分析”吧！

一、故障信息提取

歸納一下語句類型如下：

1、CREATE TABLE A AS SELECT B
其 STATE 為 sending data

2、DROP TABLE A
其 STATE 為 Waiting for table metadata lock

3、SELECT * FROM A
其 STATE 為 Waiting for table metadata lock

4、SHOW TABLE STATUS[like A]
其 STATE 為 Waiting for table metadata lock

二、信息分析

要分析出這個案列其實不太容易因為他是 MYSQL 層 MDL LOCK 和 RR 模式 innodb row lock 的一個綜合案列，并且我們要對 schema.processlist 的 STATE 比較敏感才行。

本節關于 MDL LOCK 的驗證使用下面兩種方式：

方式 1 筆者在 MDL LOCK 源碼加鎖函數處加日志輸出，但是如果要分析各種語句加 MDL LOCK 的類型還只能用這種方式，因為 MDL LOCK 加鎖往往一閃而過，performance_schema.metadata_locks 沒有辦法觀察到

方式 2 處于堵塞情況下 performance_schema.metadata_locks 的輸出
打開如下：

UPDATE performance_schema.setup_consumers SET ENABLED = YES WHERE NAME = global_instrumentation 
 UPDATE performance_schema.setup_instruments SET ENABLED = YES WHERE NAME = wait/lock/metadata/sql/mdl 
 select * from performance_schema.metadata_locks\G

1、關于 CREATE TABLE A AS SELECT B 對 B 表 sending data 的分析

關于 sending data 這個狀態其實可以代表很多含義，從我現有的對的了解，這是 MYSQL 上層對 SELECT 類型語句的這類語句在 INNODB 層和 MYSQL 層進行數據交互的時候一個統稱，所以出現它的可能包含:

確實需要訪問數據量特別大，可能需要優化

由于 INNODB 層的獲取 row lock 需要等待，比如我們常見的 SELECT FOR UPDATE

同時我們還需要注意在 RR 模式下 SELECT B 這一部分加鎖方式和 INSERT…SELECT 是一致的參考不在熬述：
http://blog.itpub.net/7728585/viewspace-2146183/
從他反應的情況因為他在最后殺掉了一個長期的未提交的事物所以他因為是情況 2。并且整個 CREATE TABLE A AS SELECT B 語句由于 B 表上某些數據庫被上了鎖而不能獲取，導致整個語句處于 sending data 狀態下。

2、關于 SHOW TABLE STATUS[like A] Waiting for table metadata lock 的分析
這是本案例中最重要的一環，SHOW TABLE STATUS[like A]居然被堵塞其 STATE 為 Waiting for table metadata lock 并且注意這里是 table 因為 MDL LOCK 類型分為很多。我在 MDL 介紹的那篇文章中提到了 desc 一個表的時候會上 MDL_SHARED_HIGH_PRIO(SH)，其實在 SHOW TABLE STATUS 的時候也會對本表上 MDL_SHARED_HIGH_PRIO(SH)。

方式 1：

mysql  SHOW TABLE STATUS like  a  \G
2017-11-10T03:01:48.142334Z 6 [Note] (acquire_lock)**THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!**
2017-11-10T03:01:48.142381Z 6 [Note] (MDL PRINT) Thread id is 6: 
2017-11-10T03:01:48.142396Z 6 [Note] (- MDL PRINT) DB_name is:test 
2017-11-10T03:01:48.142409Z 6 [Note] (-- MDL PRINT) OBJ_name is:a 
2017-11-10T03:01:48.142421Z 6 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-10T03:01:48.142434Z 6 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH) 
2017-11-10T03:01:48.142447Z 6 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION

方式 2：

*************************** 7. row ***************************
 OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: test
 OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864665152
 LOCK_TYPE: SHARED_HIGH_PRIO
 LOCK_DURATION: TRANSACTION
 LOCK_STATUS: PENDING
 SOURCE: sql_base.cc:2821
 OWNER_THREAD_ID: 38
 OWNER_EVENT_ID: 1695

兩種方式都能觀察到 MDL_SHARED_HIGH_PRIO(SH)的存在并且我模擬的是處于堵塞情況下的。
但是 MDL_SHARED_HIGH_PRIO(SH) 是一個優先級非常高的一個 MDL LOCK 類型表現如下：

兼容性：

 Request | Granted requests for lock |
 type | S SH SR SW SWLP SU SRO SNW SNRW X |
 ----------+---------------------------------------------+
 SH | + + + + + + + + + - |

阻塞隊列優先級：

 Request | Pending requests for lock |
 type | S SH SR SW SU SNW SNRW X |
 ----------+---------------------------------+
 SH | + + + + + + + + |

其被堵塞的條件除了被 MDL_EXCLUSIVE(X)堵塞沒有其他的可能。那么這就是一個非常重要的突破口。

3、關于 CREATE TABLE A AS SELECT B 對 A 表的加 MDL LOCK 的分析
這一點也是我以前不知道的, 也是本案列中花時間最多的地方, 前文已經分析過要讓 SHOW TABLE STATUS[like A]這種只會上 MDL_SHARED_HIGH_PRIO(SH) MDL LOCK 的語句堵塞在 MDL LOCK 上只有一種可能那就是 A 表上了 MDL_EXCLUSIVE(X)。那么我開始
懷疑這個 DDL 語句在語句結束之前會對 A 表上 MDL_EXCLUSIVE(X)，然后進行實際測試不出所料確實是這樣的如下：

方式 1：

2017-11-10T05:38:16.824713Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-11-10T05:38:16.824727Z 4 [Note] (MDL PRINT) Thread id is 4: 
2017-11-10T05:38:16.824739Z 4 [Note] (- MDL PRINT) DB_name is:test 
2017-11-10T05:38:16.824752Z 4 [Note] (-- MDL PRINT) OBJ_name is:a 
2017-11-10T05:38:16.824764Z 4 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-10T05:38:16.824776Z 4 [Note] (---- MDL PRINT) Fast path is:(Y)
2017-11-10T05:38:16.824788Z 4 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED(S) 
2017-11-10T05:38:16.824799Z 4 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-11-10T05:38:16.825286Z 4 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-11-10T05:38:16.825312Z 4 [Note] (MDL PRINT) Thread id is 4: 
2017-11-10T05:38:16.825332Z 4 [Note] (- MDL PRINT) DB_name is:test 
2017-11-10T05:38:16.825345Z 4 [Note] (-- MDL PRINT) OBJ_name is:a 
2017-11-10T05:38:16.825357Z 4 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-10T05:38:16.825369Z 4 [Note] (----- MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X) 
2017-11-10T05:38:16.825381Z 4 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION

方式 2：

*************************** 1. row ***************************
 OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: test
 OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733998842016
 LOCK_TYPE: SHARED
 LOCK_DURATION: TRANSACTION
 LOCK_STATUS: GRANTED
 SOURCE: sql_parse.cc:6314
 OWNER_THREAD_ID: 36
 OWNER_EVENT_ID: 1553

這里比較遺憾在 performance_schema.metadata_locks 中并沒有顯示出 MDL_EXCLUSIVE(X)，而顯示為 MDL_SHARED(S) 但是我們在我輸出的日志中可以看到這里做了升級操作將 MDL_SHARED(S) 升級為了 MDL_EXCLUSIVE(X)。并且由前面的兼容性列表來看，只有 MDL_EXCLUSIVE(X)會堵塞 MDL_SHARED_HIGH_PRIO(SH)。所以我們應該能夠確認這里確實做了升級操作，否則 SHOW TABLE STATUS[like A] 是不會被堵塞的。

4、關于 SELECT * FROM A Waiting for table metadata lock 的分析
也許大家認為 SELECT 不會上鎖，但是那是在 innodb 層次，在 MYSQL 層會上 MDL_SHARED_READ(SR) 如下：

方式 1：

 select * from a;
2017-11-10T03:31:31.209772Z 6 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-11-10T03:31:31.209824Z 6 [Note] (MDL PRINT) Thread id is 6: 
2017-11-10T03:31:31.209851Z 6 [Note] (- MDL PRINT) DB_name is:test 
2017-11-10T03:31:31.209870Z 6 [Note] (-- MDL PRINT) OBJ_name is:a 
2017-11-10T03:31:31.209885Z 6 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-10T03:31:31.209965Z 6 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED_READ(SR) 
2017-11-10T03:31:31.209985Z 6 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION

方式 2：

 OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: test
 OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864625136
 LOCK_TYPE: SHARED_READ
 LOCK_DURATION: TRANSACTION
 LOCK_STATUS: PENDING
 SOURCE: sql_parse.cc:6314
 OWNER_THREAD_ID: 38
 OWNER_EVENT_ID: 1764

可以看到確實有 MDL_SHARED_READ(SR)的存在，當前處于堵塞狀態

其兼容性如下：

 Request | Granted requests for lock |
 type | S SH SR SW SWLP SU SRO SNW SNRW X |
 ----------+---------------------------------------------+
 SR | + + + + + + + + - - |

顯然 MDL_SHARED_READ(SR) 和 MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。

5、關于 DROP TABLE A Waiting for table metadata lock 的分析
這一點很好分析因為 A 表上了 X 鎖而 DROP TABLE A 必然上 MDL_EXCLUSIVE(X)鎖它當然和 MDL_EXCLUSIVE(X)不兼容。如下：

方式 1：

 drop table a;
2017-11-09T10:58:28.673015Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-11-09T10:58:28.673030Z 3 [Note] (MDL PRINT) Thread id is 3: 
2017-11-09T10:58:28.673042Z 3 [Note] (- MDL PRINT) DB_name is:test 
2017-11-09T10:58:28.673054Z 3 [Note] (-- MDL PRINT) OBJ_name is:t10 
2017-11-09T10:58:28.673067Z 3 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-09T10:58:28.673094Z 3 [Note] (----- MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X) 
2017-11-09T10:58:28.673109Z 3 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION

方式 2：

 OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: test
 OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733864625472
 LOCK_TYPE: EXCLUSIVE
 LOCK_DURATION: TRANSACTION
 LOCK_STATUS: PENDING
 SOURCE: sql_parse.cc:6314
 OWNER_THREAD_ID: 38
 OWNER_EVENT_ID: 1832

其中 EXCLUSIVE 就是我們說的 MDL_EXCLUSIVE(X)它確實存在當前處于堵塞

6、為何 use db 也會堵塞？
如果使用 mysql 客戶端不使用 - A 選項 (或者 no-auto-rehash) 在 USE DB 的時候至少要做如下事情：

1、對 db 下每個表上 MDL (SH) lock 如下（調用 MDL_context::acquire_lock 這里給出堵塞時候的信息）：
方式 1：

use test
2017-11-10T03:46:50.223628Z 5 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-11-10T03:46:50.223666Z 5 [Note] (MDL PRINT) Thread id is 5: 
2017-11-10T03:46:50.223696Z 5 [Note] (- MDL PRINT) DB_name is:test 
2017-11-10T03:46:50.223714Z 5 [Note] (-- MDL PRINT) OBJ_name is:a 
2017-11-10T03:46:50.223725Z 5 [Note] (--- MDL PRINT) Namespace is:TABLE 
2017-11-10T03:46:50.223735Z 5 [Note] (----- MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH) 
2017-11-10T03:46:50.223755Z 5 [Note] (------ MDL PRINT) Mdl duration is:MDL_TRANSACTION

方式 2：

*************************** 7. row ***************************
 OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: test
 OBJECT_NAME: a
OBJECT_INSTANCE_BEGIN: 140733797429008
 LOCK_TYPE: SHARED_HIGH_PRIO
 LOCK_DURATION: TRANSACTION
 LOCK_STATUS: PENDING
 SOURCE: sql_base.cc:2821
 OWNER_THREAD_ID: 37
 OWNER_EVENT_ID: 187

可以看到 USE DB 確實也因為 MDL_SHARED_HIGH_PRIO(SH) 發生了堵塞。

2、對每個表加入到 table cache，并且打開表(調用 open_table_from_share())
那么這種情況就和 SHOW TABLE STATUS[like A]被堵塞的情況一模一樣了，也是由于 MDL 鎖不兼容造成的。

三、分析梳理

有了前面的分析那么我們可以梳理這個故障發生的原因如下：

1、有一個在 B 表上長期未提交的 DML
語句會在 innodb 層對 B 表某些數據加 innodb row lock。

2、由步驟 1 引起了 CREATE TABLE A AS SELECT B 的堵塞
因為 RR 模式下 SELECT B 必然對 B 表上滿足的數據上鎖，因為步驟 1 已經加鎖所以觸發等待，STATE 為 sending data。

3、由步驟 2 引起了其他語句的堵塞
因為 CRATE TABLE A AS SELECT B 在 A 表建立完成之前會上 MDL_EXCLUSIVE(X), 這把鎖會堵塞其他全部的關于 A 表的語句，包括 DESC/SHOW TABLE STATUS/USE DB(非 -A) 這種只上 MDL_SHARED_HIGH_PRIO(SH)MDL LOCK 的語句。STATE 統一為 Waiting for table metadata lock。

四、模擬測試

測試環境：

5.7.14

GITD 關閉

RR 隔離級別

使用腳本：

create table b (id int);
insert into b values(1);
set global innodb_lock_wait_timeout=1000;
UPDATE performance_schema.setup_consumers SET ENABLED =  YES  WHERE NAME = global_instrumentation 
UPDATE performance_schema.setup_instruments SET ENABLED =  YES  WHERE NAME = wait/lock/metadata/sql/mdl 
select * from performance_schema.metadata_locks\G
(請重新連接讓參數生效)

感謝各位的閱讀，以上就是“Mysql 中 USE DB 堵塞故障分析”的內容了，經過本文的學習后，相信大家對 Mysql 中 USE DB 堵塞故障分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完