前回の記事「MySQL テーブル構造の変更、メタデータ ロックを知っておく必要があります」では、MDL 導入の背景と基本概念を紹介し、「道」レベルから MDL とは何かを学びました。次に、「技術的な」観点から MDL 関連の問題を特定する方法を見てみましょう。 MySQL 5.7 では、MDL に新しいテーブル performance_schema.metadata_locks が導入されました。このテーブルには、オブジェクト、タイプ、保留待機ステータスなど、MDL の関連情報を表示できます。 MDLインストルメンテーションを有効にする ただし、関連するインストルメントが有効になっていません (MySQL 8.0 はデフォルトで有効になっています)。有効にするには、次の 2 つの方法があります。 一時的な効果
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 ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。 以下もご興味があるかもしれません:
|
<<: Node.jsがES6モジュールを処理する方法の詳細な説明
>>: Linux の一般的なテキスト処理コマンドと vim テキストエディタ
昨日、ある人のシステムのインストールを手伝ったのですが、自動パーティション分割をクリックするのを忘れ...
イベント委任を使用してメッセージ ボード機能を実装します。 <!DOCTYPE html>...
序文すでに Docker をインストールしており、Docker について簡単に理解しています。ここで...
目次プロトタイプチェーンプロトタイプチェーンに基づいてシンプルなJQueryライブラリを実装すること...
序文決まり文句です。ここでは、フロントエンドとバックエンドの分離についての私の理解についてお話ししま...
MIME タイプとは何ですか? 1. まず、ブラウザがコンテンツを処理する方法を理解する必要がありま...
サーバーの LNPM 環境をインストールして構成する場合、複数のバージョンの PHP の共存を考慮す...
簡単な説明エディターはデータベースのエンコードが間違っているために問題に遭遇することが多く、これは頭...
目次1. プロキシモジュールをインストールする2. プロキシを設定する1. プロキシモジュールをイン...
最近の問題としては、次のような現象があります。システムには、docker run コマンドを継続的に...
html2canvas は、HTML 要素からキャンバスを生成するライブラリです。描画されるキャンバ...
目次概要二分木からB+木へクラスター化インデックス非クラスター化インデックスジョイントインデックスと...
オペレーティング システム: Win7 64 ビット Ultimate Edition MySQL ...
2 つのテーブルを関連付けるときに、外部キーを作成できませんでした。このブログから、問題は、ポイント...
1. イベントバブリング: JavaScript イベント伝播のプロセスでは、要素でイベントがトリガ...