MariaDB Metadata lock 유발 쿼리 쉽게 찾기

Tech Note 정보
카테고리: [ MariaDB ]
조회수: 13084

 

Mysql 5.7 버전 이상 부터는 performance_schema를 이용하여 메타데이타 락을 유발하는 쿼리를 쉽게 찾을 수 있으나

Maria DB에서는 현재까지 metadata plugin 을 이용하여 조회하는 information_schema.metadata_lock_info , processlist를 조회하여

metadata 유발 쿼리를 조회합니다. 이 경우 , MDL 관련 thread 가 많아지면 어떤 쿼리가 유발 쿼리인지 찾기 힘든 점이 있어

MDL 유발 thread 를 쉽게 찾는 쿼리를 공유드립니다.

 

 
 
-1. SESSION A ( DML - HOLDER 1)
 
MariaDB [(none)]> SELECT * FROM TEST.MDL_TEST;
+------+
| NUM  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

MariaDB [(none)]> UPDATE TEST.MDL_TEST SET NUM=5 WHERE NUM=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
-2. SESSION B ( DML - HOLDER 2)
 
MariaDB [(none)]> UPDATE TEST.MDL_TEST SET NUM=6 WHERE NUM=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
-3. SESSION C  ( DML - HOLDER 3)
 
MariaDB [(none)]> UPDATE TEST.MDL_TEST SET NUM=7 WHERE NUM=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
-4. SESSION D  ( DDL - WAITING )
 
MariaDB [(none)]> DROP TABLE TEST.MDL_TEST;
 
=> MDL 대기중
 
 
-. MDL 유발 쿼리 조회
 
MariaDB [(none)]> SELECT DISTINCT
    -> CONCAT('THREAD ',PL.ID,' SQL "',PL.INFO,'" IS LOCKED BY THREAD ',MDL.THREAD_ID)
    -> FROM INFORMATION_SCHEMA.PROCESSLIST PL,
    -> INFORMATION_SCHEMA.METADATA_LOCK_INFO MDL
    -> WHERE LOCATE(lcase(MDL.LOCK_TYPE), lcase(PL.STATE))>0
    -> AND LOCATE(LCASE(MDL.TABLE_NAME),LCASE(PL.INFO))>0;
 
+---------------------------------------------------------------------------------+
| CONCAT('THREAD ',PL.ID,' SQL "',PL.INFO,'" IS LOCKED BY THREAD ',MDL.THREAD_ID) |
+---------------------------------------------------------------------------------+
| THREAD 2849 SQL "DROP TABLE TEST.MDL_TEST" IS LOCKED BY THREAD 2799             |
| THREAD 2849 SQL "DROP TABLE TEST.MDL_TEST" IS LOCKED BY THREAD 2800             |
| THREAD 2849 SQL "DROP TABLE TEST.MDL_TEST" IS LOCKED BY THREAD 2810             |
+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
 
=> MDL 대기중인 Thread ID 와 해당 쿼리 및 홀더 쓰레드를 한번에 보여줌