mysql MDLメタデータロックの詳細な分析

mysql MDLメタデータロックの詳細な分析

序文:

MySQL で SQL 文を実行すると、予想した時間内に文が完了しません。このような場合、通常は MySQL データベースにログインして問題がないか確認します。通常使用されるコマンドは、どのセッションが存在し、それらのセッションが何をしているかを確認するための show processlist です。テーブル メタデータ ロックを待機中と表示される場合は、MDL メタデータ ロックが発生しています。この記事では、MDL ロックの生成とトラブルシューティングのプロセスについて紹介します。

1. MDL ロックとは何ですか?

MDL はメタデータ ロックの略です。 MDL ロックの主な機能は、テーブル メタデータのデータ一貫性を維持することです。テーブルにアクティブなトランザクション (明示的または暗黙的) がある場合、メタデータを書き込むことはできません。そのため、テーブルのメタデータ情報を保護し、DDL 操作と DML 操作間の一貫性を解決または確保するために、MySQL バージョン 5.5 以降では MDL ロックが導入されました。

MDL の導入により、主に 2 つの問題が解決されます。1 つはトランザクション分離の問題です。たとえば、反復可能分離レベルでは、セッション A が 2 つのクエリ中にテーブル構造を変更すると、2 つのクエリ結果が矛盾し、反復可能読み取りの要件を満たすことができません。もう 1 つはデータ複製の問題です。たとえば、セッション A が複数の更新ステートメントを実行し、別のセッション B がテーブル構造を変更して最初に送信した場合、スレーブは最初に変更をやり直し、次に更新をやり直すため、複製エラーが発生します。

メタデータ ロックは、サーバー レイヤー ロック、テーブル レベルのロックです。実行される DML または DDL ステートメントごとに、MDL ロックが適用されます。DML 操作には MDL 読み取りロックが必要であり、DDL 操作には MDL 書き込みロックが必要です (MDL ロック プロセスはシステムによって自動的に制御され、直接介入することはできません。読み取りと読み取りは共有され、読み取りと書き込みは相互に排他的であり、書き込みと書き込みは相互に排他的です)。MDL ロックを申請する操作はキューを形成し、キュー内の書き込みロック取得の優先順位は読み取りロックよりも高くなります。書き込みロック待機が発生すると、現在の操作がブロックされるだけでなく、テーブルに対する後続のすべての操作もブロックされます。トランザクションが MDL ロックを適用すると、トランザクションが完了するまでロックは解除されません。 (ここでは特別なケースがあります。トランザクションに DDL 操作が含まれている場合、MySQL は DDL 操作ステートメントが実行される前に暗黙的にコミットし、DDL ステートメント操作が別のトランザクションとして存在することを確認し、メタデータ排他ロックが確実に解放されるようにします)。

注意: トランザクションをサポートする InnoDB エンジン テーブルとトランザクションをサポートしない MyISAM エンジン テーブルの両方で、メタデータ ロック待機現象が発生します。メタデータ ロック待機現象が発生すると、テーブルへの後続のすべてのアクセスがこの待機中にブロックされ、接続が蓄積され、ビジネスに影響が生じます。

2. MDLロックをシミュレートして見つける

MDL ロックは通常、テーブルに対して DML 操作を実行しているコミットされていないトランザクションがあるために DDL 操作が中断されたときに発生します。ただし、MySQL セッションの数が非常に多いため、どのセッションの操作が時間内に送信されず、DDL に影響を与えたかはわかりません。通常、この種の問題をトラブルシューティングする場合、information_schema.innodb_trx テーブルから現在実行中のトランザクションをクエリする必要があります。ただし、SQL が実行されてコミットされていない場合、このテーブルで SQL を確認することはできません。

MySQL 5.7 では、MDL 関連情報を記録するために、performance_schema ライブラリに新しい metadata_locks テーブルが追加されました。まず、MDL ロック ログを有効にする必要があります。有効にするには、次の SQL を実行します。

performance_schema.setup_instruments を更新します
有効 = 'YES'、時間 = 'YES' に設定
WHERE NAME = 'wait/lock/metadata/sql/mdl';

以下に、MDL ロックをシミュレートして検出するプロセスを示します。

# セッション 1 トランザクション内で DML 操作を実行します。mysql> begin;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> student_tb (stu_id,stu_name) に値 (1009,'xin') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> student_tb から * を選択します。
+--------------+--------+----------+----------------------+----------------------+
| 増分 ID | スタッ ID | スタッ名 | 作成時刻 | 更新時刻 |
+--------------+--------+----------+----------------------+----------------------+
| 1 | 1001 | 1 から | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 2 | 1002 | dfsfd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 3 | 1003 | fdgfg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 4 | 1004 | sdfsdf | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 5 | 1005 | dsfsdg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 6 | 1006 | fgd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 7 | 1007 | fgds | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 8 | 1008 | dgfsa | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 9 | 1009 | xin | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+----------------------+----------------------+

# セッション 2 DDL 操作を実行してテーブルにフィールドを追加し、DDL がハングしていることを確認します。mysql> alter table student_tb add stu_age int after stu_name;

# セッション 3 すべてのセッションをクエリし、MDL ロックが発生していることを確認します。mysql> show processlist;
+----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+
| 31 | ルート | ローカルホスト | testdb | スリープ | 125 | | NULL |
| 32 | root | localhost | testdb | クエリ | 7 | テーブル メタデータ ロックを待機中 | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | クエリ | 0 | 開始 | プロセスリストを表示 |
+----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+

# セッション 3 metadata_locks テーブル レコードを確認し、student_tb テーブルに MDL ロック競合があることを確認します。mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+
| テーブル | testdb | student_tb | 94189250717664 | SHARED_WRITE | トランザクション | 許可 | | 56 | 34 |
| グローバル | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | ステートメント | 許可 | | 57 | 18 |
| スキーマ | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | トランザクション | 許可 | | 57 | 18 |
| テーブル | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | トランザクション | 許可 | | 57 | 18 |
| テーブル | testdb | student_tb | 139764477697696 | 排他 | トランザクション | 保留中 | | 57 | 18 |
| テーブル | パフォーマンス スキーマ | メタデータ ロック | 139764544135120 | 共有読み取り | トランザクション | 許可 | | 58 | 20 |
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+

# セッション3は他のシステムテーブルを組み合わせてセッションIDを見つける
mysql> performance_schema.metadata_locks から m.*、t.PROCESSLIST_ID を選択し、 performance_schema.threads t を m.owner_thread_id=t.thread_id に結合します。
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+
| テーブル | testdb | student_tb | 94189250717664 | SHARED_WRITE | トランザクション | 許可 | | 56 | 34 | 31 |
| グローバル | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | ステートメント | 許可 | | 57 | 18 | 32 |
| スキーマ | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | トランザクション | 許可 | | 57 | 18 | 32 |
| テーブル | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | トランザクション | 許可 | | 57 | 18 | 32 |
| テーブル | testdb | student_tb | 139764477697696 | 排他 | トランザクション | 保留中 | | 57 | 18 | 32 |
| テーブル | パフォーマンス スキーマ | メタデータ ロック | 139764544135120 | 共有読み取り | トランザクション | 許可 | | 58 | 22 | 33 |
| テーブル | performance_schema | スレッド | 139764549217280 | SHARED_READ | トランザクション | 許可 | | 58 | 22 | 33 |
+-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+

# 結果の解釈: 上記の結果から、セッション 31 が student_tb テーブルの SHARED_WRITE ロックを保持していることは明らかです。
# MDL ロックを解除するには、送信されるまで待つか、セッションを手動で終了する必要があります。

3. MDLロックを最適化して回避する方法

MDL ロックが発生すると、テーブルへの後続のアクセスがすべてブロックされ、接続のバックログが発生するため、ビジネスに大きな影響を与えます。日常生活では、MDL ロックの発生を避けるように努めるべきです。参考までに、最適化の提案をいくつか示します。

  • MDL ロックを記録するには、metadata_locks テーブルを有効にします。
  • ブロックされた側がアクティブに停止するように、パラメータ lock_wait_timeout を小さい値に設定します。
  • トランザクションを標準化された方法で使用し、トランザクションをタイムリーに送信し、大規模なトランザクションの使用を避けます。
  • 監視とアラームを強化して、MDL ロックをタイムリーに検出します。
  • DDL 操作とバックアップ操作は、業務のオフピーク時間帯に実行されます。
  • クエリのためにトランザクションを開くために使用するツールを少なくし、グラフィカル ツールを適切なタイミングで閉じます。

要約:

この記事では、主に MDL ロックを 3 つの側面から説明します。まず、MDL ロックの原因と機能を紹介します。次に、MDL ロックをシミュレートし、検索と解決の方法を示します。最後に、MDL ロックを回避するための提案をいくつか示します。実際、MDL ロックは DB の操作とメンテナンス中に頻繁に発生します。MDL ロックは脅威ではなく、データベース オブジェクトを保護し、データの一貫性を確保するためにのみ使用されます。この記事を読んで、MDL ロックについてより深く理解していただければ幸いです。

上記はMySQL MDLメタデータロックの詳細分析です。MySQL MDLメタデータロックの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL でメタデータ ロックがブロックされている場所を確認する方法
  • MYSQL メタデータ ロック (MDL ロック) MDL ロックの問題分析

<<:  Vue.js ディレクティブのカスタム命令の詳細な説明

>>:  Nginx 外部ネットワーク アクセス イントラネット サイト構成操作

推薦する

Dockerでランナーコンテナを構成する方法

1. ランナーコンテナを作成する mk@mk-pc:~/Desktop$ docker run -d...

NodeJSとブラウザにおけるこのキーワードの違い

序文JavaScript を学習した人なら誰でも、さまざまな環境で this がどこを指すかという問...

JavaScript のフラット配列をツリー構造に変換する例

目次バックグラウンドで10,000個のデータが失われた再帰法非再帰的方法要約するバックグラウンドで1...

ログインインターセプションを実装するためのVueルーティング

目次1. 概要2. ログインインターセプションを実装するためのルーティングナビゲーションガード1. ...

CSS ピックアップ矢印、カタログ、アイコン実装コード

1. CSS その他のアイコンアイコンを作成するには 3 つの方法があります。写真css (小さな矢...

MySQL バイナリログデータ復旧: 誤ってデータベースを削除した場合の詳細な説明

MySQL Binログデータの回復: 誤ってデータベースを削除した場合前書き: テスト マシンで誤っ...

Axios を使用して Vue2 がリクエストを開始する詳細なプロセス記録

目次序文Axiosのインストールと設定シンプルなGETリクエストを開始するPOSTリクエストを行うシ...

JavaScriptエンジンV8の実行プロセスの詳細な説明

目次1. V8ソース2. V8サービスターゲット3. V8の初期アーキテクチャIV. V8の初期アー...

Linux のロード vmlinux デバッグ

gdb を使用してカーネル シンボルをロードする arm-eabi-gdb 出力/ターゲット/製品/...

MySQLループは数千万のデータを挿入する

1. テストテーブルを作成する テーブル `mysql_genarate` を作成します ( `id...

Springboot プロジェクトに動的にパラメータを渡すための Docker の実装方法

背景最近、Docker 初心者の友人から、毎回プロジェクト構成ファイルにハードコーディングしてサービ...

MySQL 接続数を設定する方法 (接続数が多すぎる)

mysql使用中に接続数が超過していることが判明しました~~~~ [root@linux-node...

Vue の計算プロパティとリスナーの使用の概要

1. 計算プロパティとリスナー1.1 計算プロパティ <!DOCTYPE html> &...

CSS のサイズと幅と高さのブラウザ解釈の違いに対する解決策

まずは例を見てみましょうコードをコピーコードは次のとおりです。 <!DOCTYPE html ...

MySQL 文字セットの変更に関する実践的なチュートリアル

序文: MySQL では、システムが多くの文字セットをサポートしており、異なる文字セット間にはわずか...