MySQL 5.7 でブロックポジショニング DDL の問題を解決する

前回の記事「MySQL テーブル構造の変更、メタデータロックを知っておく必要があります」では、MDL 導入の背景と基本概念を紹介し、「道」レベルから MDL とは何かを学びました。次に、「技術的な」観点から MDL 関連の問題を特定する方法を見てみましょう。

MySQL 5.7 では、MDL に新しいテーブル performance_schema.metadata_locks が導入されました。このテーブルには、オブジェクト、タイプ、保留待機ステータスなど、MDL の関連情報を表示できます。

MDLインストルメンテーションを有効にする

ただし、関連するインストルメントが有効になっていません (MySQL 8.0 はデフォルトで有効になっています)。有効にするには、次の 2 つの方法があります。

一時的な効果

performance_schema.setup_instrume nts表変更されますが、インスタンスを再起動するとデフォルト値に復元されます。

performance_schema.setup_instruments を更新し、ENABLED = 'YES'、TIMED = 'YES' に設定します。
WHERE NAME = 'wait/lock/metadata/sql/mdl';

永続

設定ファイルで設定

[mysqld]
パフォーマンス スキーマ インストルメント = 'wait/lock/metadata/sql/mdl=ON'

テストシナリオ

以下は、MySQL 5.7 で DDL 操作のブロッキング問題を特定する方法を示した簡単なデモです。

セッション1> 開始;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
session1> slowtech.t1 から id=2 を削除します。
クエリは正常、1 行が影響を受けました (0.00 秒)
session1> slowtech.t1 から * を選択します。
+------+------+
| ID | 名前 |
+------+------+
| 1 | へ |
+------+------+
セット内の 1 行 (0.00 秒)
session1> slowtech.t1 を更新し、name='c' を設定し、id=1 とします。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0
session2> alter table slowtech.t1 add c1 int; ##ブロックされました session3> show processlist;
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
| 2 | ルート | ローカルホスト | NULL | スリープ | 51 | | NULL |
| 3 | root | localhost | NULL | クエリ | 0 | 開始 | プロセスリストを表示 |
| 4 | root | localhost | NULL | クエリ | 9 | テーブル メタデータ ロックを待機中 | alter table slowtech.t1 add c1 int |
+----+------+-----------+--------+-------+-------+---------------------------------+------------------------------------+
セット内の 3 行 (0.00 秒)
セッション3> performance_schema.metadata_locks から object_type、object_schema、object_name、lock_type、lock_duration、lock_status、owner_thread_id を選択します。
+-------------+--------------------+---------------------+---------------+------------+----------------+
| オブジェクト タイプ | オブジェクト スキーマ | オブジェクト名 | ロック タイプ | ロック期間 | ロック ステータス | 所有者スレッド ID |
+-------------+--------------------+---------------------+---------------+------------+----------------+
| テーブル | slowtech | t1 | SHARED_WRITE | トランザクション | 許可 | 27 |
| グローバル | NULL | NULL | INTENTION_EXCLUSIVE | ステートメント | 許可 | 29 |
| スキーマ | slowtech | NULL | INTENTION_EXCLUSIVE | トランザクション | 許可 | 29 |
| テーブル | slowtech | t1 | SHARED_UPGRADABLE | トランザクション | 許可 | 29 |
| テーブル | slowtech | t1 | 排他 | トランザクション | 保留中 | 29 |
| テーブル | パフォーマンス スキーマ | メタデータ ロック | 共有読み取り | トランザクション | 許可 | 28 |
+-------------+--------------------+---------------------+---------------+------------+----------------+
セット内の 6 行 (0.00 秒)

ここで、lock_status に注目してください。「PENDING」はスレッドが MDL を待機していることを意味し、「GRANTED」はスレッドが MDL を保持していることを意味します。

ブロックの原因となっているセッションを見つける方法

owner_thread_id と組み合わせると、スレッド 29 がスレッド 27 の MDL を待機していることがわかります。この時点で、スレッド 52 は強制終了できます。

ただし、owner_thread_id はスレッド ID のみを提供し、show processlist の ID は提供しないことに注意してください。スレッドに対応するプロセスリスト ID を検索する場合は、performance_schema.threads テーブルをクエリする必要があります。

session3> performance_schema.threads から * を選択 where thread_id in (27,29)\G
************************** 1. 行 ****************************
   スレッドID: 27
    名前: スレッド/sql/one_connection
    タイプ: フォアグラウンド
  プロセスリストID: 2
 PROCESSLIST_USER: ルート
 PROCESSLIST_HOST: ローカルホスト
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: スリープ
 プロセスリスト_時間: 214
 プロセスリスト状態: NULL
 PROCESSLIST_INFO: NULL
 親スレッドID: 1
    役割: NULL
  計測済み: はい
   履歴: はい
 CONNECTION_TYPE: ソケット
  スレッド_OS_ID: 9800
************************** 2. 行 ****************************
   スレッドID: 29
    名前: スレッド/sql/one_connection
    タイプ: フォアグラウンド
  プロセスリストID: 4
 PROCESSLIST_USER: ルート
 PROCESSLIST_HOST: ローカルホスト
  PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: クエリ
 プロセスリスト_時間: 172
 PROCESSLIST_STATE: テーブルメタデータのロックを待機しています
 PROCESSLIST_INFO: テーブル slowtech.t1 を変更し、c1 int を追加します
 親スレッドID: 1
    役割: NULL
  計測済み: はい
   履歴: はい
 CONNECTION_TYPE: ソケット
  スレッド_OS_ID: 9907
セット内の 2 行 (0.00 秒)

これら 2 つのテーブルを組み合わせて sys.innodb_lock_waits の出力を参照することで、MDL の待機関係を実際に直感的に表すことができます。

選択
 a.OBJECT_SCHEMA AS ロックされたスキーマ、
 a.OBJECT_NAME AS ロックテーブル、
 「メタデータ ロック」AS locked_type、
 c.PROCESSLIST_ID は、waiting_processlist_id として、
 c.PROCESSLIST_TIME は待機時間として、
 c.PROCESSLIST_INFO AS 待機クエリ、
 c.PROCESSLIST_STATE AS 待機状態、
 d.PROCESSLIST_ID AS ブロッキングプロセスリストID、
 d.PROCESSLIST_TIME AS ブロッキング年齢、
 d.PROCESSLIST_INFO AS ブロッキングクエリ、
 concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
から
 performance_schema.metadata_locks a
performance_schema.metadata_locks b を a.OBJECT_SCHEMA = b.OBJECT_SCHEMA に結合します。
かつ、a.OBJECT_NAME = b.OBJECT_NAME
かつ a.lock_status = 'PENDING'
b.lock_status = 'GRANTED' かつ
かつ、a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
かつ、a.lock_type = 'EXCLUSIVE'
performance_schema.threads c を a.OWNER_THREAD_ID = c.THREAD_ID に結合します。
performance_schema.threads d を b.OWNER_THREAD_ID = d.THREAD_ID\G に結合します。

************************** 1. 行 ****************************
    ロックされたスキーマ: slowtech
    ロックされたテーブル: t1
     ロックタイプ: メタデータロック
  待機プロセスリスト ID: 4
     待機年齢: 259
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
    waiting_state: テーブルメタデータのロックを待機中
  ブロッキングプロセスリストID: 2
    ブロッキング年齢: 301
    ブロッキングクエリ: NULL
sql_kill_blocking_connection: 2 を強制終了する
セット内の 1 行 (0.00 秒)

出力は一目でわかります。DDL 操作の MDL を取得する場合は、kill 2 を実行します。

公式 sys.schematablelock_waits

実際、MySQL 5.7 でも sys ライブラリに同様の機能が統合されています。同じシナリオでは、出力は次のようになります。

mysql> sys.schema_table_lock_waits\G から * を選択します
************************** 1. 行 ****************************
    オブジェクトスキーマ: slowtech
     オブジェクト名: t1
   待機スレッドID: 29
     待機中のpid: 4
    待機アカウント: root@localhost
   待機ロックタイプ: 排他的
  待機ロック期間: トランザクション
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
   待機クエリ秒数: 446
 影響を受けるクエリ行の待機: 0
 検査済みのクエリ行を待機中: 0
   ブロックスレッドID: 27
    ブロッキングpid: 2
   ブロッキングアカウント: root@localhost
   ブロッキングロックタイプ: SHARED_READ
  ブロックロック期間: トランザクション
  sql_kill_blocking_query: クエリ 2 を強制終了する
sql_kill_blocking_connection: 2 を強制終了する
************************** 2. 行 ****************************
    オブジェクトスキーマ: slowtech
     オブジェクト名: t1
   待機スレッドID: 29
     待機中のpid: 4
    待機アカウント: root@localhost
   待機ロックタイプ: 排他的
  待機ロック期間: トランザクション
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
   待機クエリ秒数: 446
 影響を受けるクエリ行の待機: 0
 検査済みのクエリ行を待機中: 0
   ブロッキングスレッドID: 29
    ブロッキングpid: 4
   ブロッキングアカウント: root@localhost
   ブロッキングロックタイプ: SHARED_UPGRADABLE
  ブロックロック期間: トランザクション
  sql_kill_blocking_query: クエリ 4 を強制終了する
sql_kill_blocking_connection: 4 を強制終了する
セット内の 2 行 (0.00 秒)

公式出力を具体的に分析すると、

alter table 操作は 1 つだけですが、2 つのレコードが生成され、2 つのレコードの kill オブジェクトが異なります。テーブル構造に精通しておらず、レコードの内容を注意深く読み取らないと、間違ったオブジェクトを kill してしまうことになります。

さらに、N 個のクエリが DDL 操作によってブロックされた場合、N * 2 個のレコードが生成されます。ブロック操作が多数ある場合、これらの N * 2 レコードは完全にノイズになります。

以前の SQL では、ブロックされた操作の数に関係なく、alter table 操作によって出力されたレコードは 1 つだけでした。

ブロックセッションによって実行された操作を表示する方法

しかし、上記の SQL にも残念な点があり、blocking_query が NULL であり、セッション 1 では明らかに 3 つの SQL が実行されています。

これは performance_schema.threads (show processlist と同様) に関連しており、現在実行中の SQL のみを出力します。実際には、すでに実行された SQL を確認することは不可能です。

しかし、オンラインでは、殺害は注意を要する操作です。結局のところ、殺害がビジネス上重要な操作であるかどうかを知ることは困難です。それともバッチ更新操作ですか?では、トランザクションの前に操作をキャプチャする方法はあるのでしょうか?

答えはイエスです。

これは、パフォーマンススキーマ内のステートメントイベント（操作イベント）を記録するテーブルです。これには、

events_statements_current、events_statements_history、events_statements_history_long、prepared_statements_instances。

最初の 3 つがよく使用されます。

3つのテーブル構造は全く同じです。このうちevents_statements_historyにはevents_statements_currentの操作も含まれているので、ここではevents_statements_historyを使用します。

最終的なSQLは次のようになります。

選択
 ロックされたスキーマ、
 ロックされたテーブル、
 ロックタイプ、
 待機プロセスリストID、
 待機年齢、
 待機クエリ、
 待機状態、
 ブロッキングプロセスリストID、
 ブロック年齢、
 substring_index(sql_text,"transaction_begin;" ,-1) AS ブロッキングクエリ、
 sql_kill_blocking_connection
から
 （
  選択
   b.OWNER_THREAD_IDはgranted_thread_idとして、
   a.OBJECT_SCHEMA AS ロックされたスキーマ、
   a.OBJECT_NAME AS ロックテーブル、
   「メタデータ ロック」AS locked_type、
   c.PROCESSLIST_ID は、waiting_processlist_id として、
   c.PROCESSLIST_TIME は待機時間として、
   c.PROCESSLIST_INFO AS 待機クエリ、
   c.PROCESSLIST_STATE AS 待機状態、
   d.PROCESSLIST_ID AS ブロッキングプロセスリストID、
   d.PROCESSLIST_TIME AS ブロッキング年齢、
   d.PROCESSLIST_INFO AS ブロッキングクエリ、
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  から
   performance_schema.metadata_locks a
  performance_schema.metadata_locks b を a.OBJECT_SCHEMA = b.OBJECT_SCHEMA に結合します。
  かつ、a.OBJECT_NAME = b.OBJECT_NAME
  かつ a.lock_status = 'PENDING'
  b.lock_status = 'GRANTED' かつ
  かつ、a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  かつ、a.lock_type = 'EXCLUSIVE'
  performance_schema.threads c を a.OWNER_THREAD_ID = c.THREAD_ID に結合します。
  performance_schema.threads d を b.OWNER_THREAD_ID = d.THREAD_ID に結合します。
 ) t1,
 （
  選択
   スレッド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
  から
   パフォーマンス スキーマ イベント ステートメント 履歴
  スレッドIDでグループ化
 ) t2
どこ
 t1.許可されたスレッドID = t2.スレッドID \G
************************** 1. 行 ****************************
    ロックされたスキーマ: slowtech
    ロックされたテーブル: t1
     ロックタイプ: メタデータロック
  待機プロセスリスト ID: 4
     待機年齢: 294
    待機クエリ: テーブル slowtech.t1 を変更し、c1 int を追加します
    waiting_state: テーブルメタデータのロックを待機中
  ブロッキングプロセスリストID: 2
    ブロック年齢: 336
    ブロッキングクエリ: slowtech.t1 から id=2 を削除し、slowtech.t1 から * を選択し、slowtech.t1 を更新して、id=1 で name='c' を設定します。
sql_kill_blocking_connection: 2 を強制終了する
セットに 1 行、警告 1 件 (0.00 秒)

上記の出力から、blocking_query にはセッション 1 の現在のトランザクションのすべての操作が含まれており、実行順に出力されていることがわかります。

デフォルトでは、events_statements_history は各スレッドの最新の 10 件の操作のみを保持することに注意してください。トランザクションに多数の操作がある場合、実際にはそれらすべてをキャプチャすることは不可能です。

要約する

上記は、MySQL 5.7 で DDL がブロックされる問題を特定する方法について紹介したものです。お役に立てば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。

以下もご興味があるかもしれません: