メタデータロックの調べ方
メタデータとメタデータロックとは
メタデータは何ぞや？と思う方も多いと思いますが、マニュアルから引用すると「データに関するデータ」だそうです。
メタデータは「データに関するデータ」です。 データベースについて記述しているすべてのものがメタデータであり、データベースの内容ではありません。 したがって、カラム名、データベース名、ユーザー名、バージョン名、および SHOW の文字列結果のほとんどがメタデータです。 INFORMATION_SCHEMA 内のテーブルは定義上、データベースオブジェクトに関する情報を含んでいるので、これは、このテーブルの内容にも当てはまります。
10.2.2 メタデータ用の UTF-8
大雑把にいうと、DML(Data Manipulation Statements)で操作するデータ以外のデータ、主にDDL(Data Definition Statements)などで操作するデータなどを指しています。
普段はあまり気にすることはありませんが、トランザクションの途中でテーブル定義が変わってしまったなんてことが起きないように、これらのデータもトランザクション中にロックの取得と解放が自動的に行われています。
クエリチューニングを行っているとあるテーブルにIndexを追加をしようとすると、別の誰かがトランザクションの中でIndexを追加したいテーブルのメタデータロックを取得していてIndex追加が待たされることが時々発生します。
そのような場合に SHOW PROCESSLIST を見ると、StatusがWaiting for table metadata lock となって、メタデータのロック解放待ちになっていつまでも待たされています。(ID 74の一番下のクエリがメタデータロックの解放待ち)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> show processlist; +----+-----------------+-----------+-----------+---------+---------+---------------------------------+-----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+-----------+---------+---------+---------------------------------+-----------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 2830256 | Waiting on empty queue | NULL | | 53 | root | localhost | target_db | Query | 0 | init | show processlist | | 64 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 65 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 66 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 67 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 68 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 69 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 70 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 71 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 72 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 73 | target_user | localhost | target_db | Sleep | 257 | | NULL | | 74 | root | localhost | target_db | Query | 12 | Waiting for table metadata lock | alter table target_table comment 'test' | +----+-----------------+-----------+-----------+---------+---------+---------------------------------+-----------------------------------------+ 13 rows in set, 1 warning (0.00 sec) |
なので、このメタデータロックを行っているDBコネクションを探す必要が出てきます。
メタデータのロックを取得しているコネクションの調べ方
メタデータのロック情報は、performance_schemaデータベースにあるmetadata_locks テーブルから取得できます。
https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-metadata-locks-table.html
ただ、こちらのテーブルにある情報だけでは、そのメタデータロックを取得しているDBコネクションの情報に行きつかないため、threads テーブルのthread_idと結合する必要があります。
https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-threads-table.html
目的のテーブルのメタデータロックを取得しているDBコネクションを探し出すには、以下のようなクエリを実行することで、ロックを保持しているDBコネクションを探し出せます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT PROCESSLIST_ID, -- KILL ステートメントで指定するID PROCESSLIST_USER, -- DB接続ユーザー名 PROCESSLIST_HOST, -- DB接続ホスト名 OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE FROM performance_schema.metadata_locks AS MDL INNER JOIN performance_schema.threads AS TH ON MDL.OWNER_THREAD_ID = TH.thread_id WHERE OBJECT_TYPE = 'TABLE' AND -- 対象をテーブルに限定 OBJECT_SCHEMA = 'target_db' AND -- 対象のDB名 OBJECT_NAME = 'target_table'; -- 対象のテーブル名 |
上記クエリを実行すると以下のような結果が得られます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> SELECT -> PROCESSLIST_ID, -- KILL ステートメントで指定するID -> PROCESSLIST_USER, -- DB接続ユーザー名 -> PROCESSLIST_HOST, -- DB接続ホスト名 -> OBJECT_SCHEMA, -> OBJECT_NAME, -> OBJECT_TYPE -> FROM -> performance_schema.metadata_locks AS MDL -> INNER JOIN performance_schema.threads AS TH -> ON MDL.OWNER_THREAD_ID = TH.thread_id -> WHERE -> OBJECT_TYPE = 'TABLE' AND -- 対象をテーブルに限定 -> OBJECT_SCHEMA = 'target_db' AND -- 対象のDB名 -> OBJECT_NAME = 'target_table'; -- 対象のテーブル名 +----------------+------------------+------------------+---------------+--------------+-------------+ | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_TYPE | +----------------+------------------+------------------+---------------+--------------+-------------+ | 64 | target_user | localhost | target_db | target_table | TABLE | | 65 | target_user | localhost | target_db | target_table | TABLE | | 66 | target_user | localhost | target_db | target_table | TABLE | | 67 | target_user | localhost | target_db | target_table | TABLE | | 68 | target_user | localhost | target_db | target_table | TABLE | | 69 | target_user | localhost | target_db | target_table | TABLE | | 70 | target_user | localhost | target_db | target_table | TABLE | | 71 | target_user | localhost | target_db | target_table | TABLE | | 72 | target_user | localhost | target_db | target_table | TABLE | | 73 | target_user | localhost | target_db | target_table | TABLE | | 74 | root | localhost | target_db | target_table | TABLE | | 74 | root | localhost | target_db | target_table | TABLE | +----------------+------------------+------------------+---------------+--------------+-------------+ 12 rows in set (0.00 sec) |
ALTER 文を実行中のConnection ID 74以外の64～73までの10本のDBコネクションが問題のメタデータロックを解放していない原因のDBコネクションだとわかります。
メタデータロックの外し方
基本的にはメタデータロックを外すには、該当のDBコネクションの持ち主に解放(トランザクションの終了など)をしてもらうのが正当な手順です。
ただ、開発環境など処理の途中のDBコネクションを切断しても影響がコントロール可能な場合には、DBコネクションを強制的に切断してしまって解放する方法もあります。
DBコネクションを強制的に切断する方法にKILLステートメントを利用します。
https://dev.mysql.com/doc/refman/8.0/ja/kill.html
上記の例だと、10回繰り返しKILLコマンドを実行する必要があります。
|
1 |
KILL 64; -- 64 ～ 73 まで10回繰り返し実行 |
たくさんのDBコネクションを切断するのは面倒
Killコマンドでも1つ、2つのぐらいならコネクションIDを特定して1個づつKILLするも簡単ですが、プログラムのバグ等で大量のDBコネクションをKILLする必要が出てきた場合には面倒です。
なので、KILLステートメントはそのままではクエリに組み込めないので、ラップするFunctionを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
USE utils; DELIMITER // CREATE DEFINER='root'@'localhost' FUNCTION pkill(c_id INTEGER ) RETURNS int NO SQL BEGIN DECLARE result INT; -- Error number: 1094; Symbol: ER_NO_SUCH_THREAD; SQLSTATE: HY000 -- https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_no_such_thread DECLARE CONTINUE HANDLER FOR 1094 BEGIN -- エラーが発生した場合は0を返す SET result = 0; END; -- 成功した場合は1を返す SET result = 1; BEGIN -- Connection IDを指定してKILL kill c_id; END; RETURN result; END // DELIMITER ; |
下記のクエリで、対象テーブルのメタデータロックを行っているのDBコネクションを切断できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT PROCESSLIST_ID, utils.pkill(PROCESSLIST_ID), -- utils.pkill 関数にPROCESSLIST_IDを渡して対象を連続してKillする locked_user FROM (SELECT PROCESSLIST_ID, -- Kill 対象 connection_id MAX(PROCESSLIST_USER) AS locked_user FROM performance_schema.metadata_locks AS MDL INNER JOIN performance_schema.threads AS TH ON MDL.OWNER_THREAD_ID = TH.thread_id WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_SCHEMA = 'target_db' AND OBJECT_NAME = 'target_table' AND PROCESSLIST_ID NOT IN (74) -- KILL 対象から外す connection_id GROUP BY PROCESSLIST_ID -- PROCESSLIST_IDの重複避けをして一度KillしたPROCESSLIST_IDを複数回Killするのを避ける ) AS SQ001; |
実行例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> SELECT -> PROCESSLIST_ID, -> utils.pkill(PROCESSLIST_ID), -> locked_user -> FROM -> (SELECT -> PROCESSLIST_ID, -- Kill 対象 connection_id -> MAX(PROCESSLIST_USER) AS locked_user -> FROM -> performance_schema.metadata_locks AS MDL -> INNER JOIN performance_schema.threads AS TH -> ON MDL.OWNER_THREAD_ID = TH.thread_id -> WHERE -> OBJECT_TYPE = 'TABLE' AND -> OBJECT_NAME = 'target_table' AND -> PROCESSLIST_ID NOT IN (74) -- KILL 対象から外す connection_id -> GROUP BY -> PROCESSLIST_ID) AS SQ001; +----------------+-----------------------------+-------------+ | PROCESSLIST_ID | utils.pkill(PROCESSLIST_ID) | locked_user | +----------------+-----------------------------+-------------+ | 64 | 1 | target_user | | 65 | 1 | target_user | | 66 | 1 | target_user | | 67 | 1 | target_user | | 68 | 1 | target_user | | 69 | 1 | target_user | | 70 | 1 | target_user | | 71 | 1 | target_user | | 72 | 1 | target_user | | 73 | 1 | target_user | +----------------+-----------------------------+-------------+ 10 rows in set (0.01 sec) |
ロックが解放されると、ALTER 文の実行が終了しています。
|
1 2 3 |
mysql> alter table target_table comment 'test'; Query OK, 0 rows affected (5 min 54.83 sec) Records: 0 Duplicates: 0 Warnings: 0 |
終わりに
普段はあまり気にしないメタデータロックですが、クエリチューニングでIndexの作成等を行おうとすると時々メタデータのロック待ちで待たされることがあります。
この記事がクエリチューニング中のトラブル対処の一助になれば幸いです。
