USE DB 輻輳に対する MySQL ソリューションの詳細な説明

障害に遭遇すると、障害の根本的な原因を考えるのではなく、障害を解決する方法を考えることがよくあります。これでは魚は獲れるものの、漁法は失われてしまうだけです。今日は、USE DB 輻輳障害によって発生する考えられるケースを共有しましょう。

障害の説明

今日、友人が重大なデータベース障害に遭遇しました。障害環境は次のとおりです。

MYSQL 5.6.16

RR 分離レベル

GITD 閉じる

パフォーマンスは以下のとおりです。

dbを使用してもデータベースにアクセスできません

show table status はテーブル情報を照会できません

schema.processlistによると、テーブルメタデータロックを待機しているものが多数あります

絶望した彼は、多数のスレッドを強制終了しましたが、それでも回復できないことがわかりました。最終的に、時間内に送信されなかったトランザクションを強制終了して、正常な状態に戻しました。残っているのは、以下のスクリーンショットだけです。

障害情報抽出

上の図に戻ると、ステートメントの種類を次のようにまとめることができます。

1.テーブルAをSELECT Bとして作成する

その州はデータを送信しています

2.テーブルAを削除する

状態はテーブルメタデータロックを待機中です

3. Aから*を選択

状態はテーブルメタデータロックを待機中です

4.テーブルステータスを表示 ['A' のように]

状態はテーブルメタデータロックを待機中です

情報分析

このケースは、MYSQL レベルの MDL LOCK と RR モードの innodb 行ロックの組み合わせであるため、分析が容易ではなく、schema.processlist の STATE に敏感である必要があります。

MDL LOCK について学ぶには、以下の記事を読むことをお勧めします。

https://www.jb51.net/article/131383.htm

このセクションでは、次の 2 つの方法を使用して MDL LOCK を検証します。

方法 1 : MDL LOCK ソースコード ロック機能にログ出力を追加しました。さまざまなステートメントに追加された MDL LOCK の種類を分析したい場合は、MDL LOCK が頻繁にフラッシュし、performance_schema.metadata_locks ではそれを観察できないため、この方法のみを使用できます。

方法 2 : performance_schema.metadata_locks 5.7 を使用して、ブロックされた状態で観察します。

P_S で mdl モニタリングを開く方法は次のとおりです。

1. CREATE TABLE A AS SELECT BにおけるテーブルBの送信データの分析

データ送信ステータスは、実際には多くの意味を表すことができます。現在の私の理解では、これはINNODB層とMYSQL層が相互に作用する際のMYSQL上位層のSELECT型ステートメントの総称であるため、その出現としては以下が考えられます。

アクセスする必要があるデータの量は非常に大きいため、最適化が必要になる場合があります。

INNODB レイヤーでの行ロックの取得には、一般的な SELECT FOR UPDATE などの待機が必要になるためです。

同時に、RR モードでの SELECT B のロック方法は INSERT...SELECT の方法と一致していることにも注意する必要があるため、ここでは繰り返さないことにします。

彼の反応から判断すると、最後に長期間コミットされていないトランザクションを強制終了したため、状況 2 にありました。また、テーブル B の一部のデータベースがロックされているため、CREATE TABLE A AS SELECT B ステートメント全体を取得できず、ステートメント全体がデータ送信状態になります。

2. SHOW TABLE STATUSの分析 ['A'のように] テーブルメタデータロックを待機中

これは、このケースの最も重要な部分です。SHOW TABLE STATUS[like 'A'] はブロックされています。その状態は、テーブル メタデータ ロックを待機しています。MDL LOCK には多くの種類があるため、ここではテーブルであることに注意してください。 MDL を紹介した記事で、テーブルを desc すると MDL_SHARED_HIGH_PRIO(SH) が表示されると書きました。実際、SHOW TABLE STATUS を実行すると、このテーブルに対しても MDL_SHARED_HIGH_PRIO(SH) が表示されます。

方法1

方法2

どちらの方法でも MDL_SHARED_HIGH_PRIO(SH) の存在を観察でき、ブロッキング状況をシミュレートしました。

ただし、MDL_SHARED_HIGH_PRIO (SH) は、次に示すように、非常に高い優先度を持つ MDL LOCK タイプです。

互換性:

ブロッキングキューの優先度:

MDL_EXCLUSIVE(X) によってブロックされる以外に、ブロックされる可能性のある条件はありません。したがって、これは非常に重要な進歩です。

III. CREATE TABLE A AS SELECT B でテーブル A に MDL LOCK を追加する場合の分析

これは、以前は知らなかったことであり、この場合最も時間がかかる部分でもあります。前回の記事で分析したように、MDL_SHARED_HIGH_PRIO (SH) MDL LOCK のみを設定する SHOW TABLE STATUS [like 'A'] のようなステートメントが MDL LOCK でブロックされる可能性があるのは 1 つだけで、それはテーブル A が MDL_EXCLUSIVE (X) を設定していることです。

そこで、この DDL ステートメントがステートメントの終了前にテーブル A に対して MDL_EXCLUSIVE(X) を実行するのではないかと疑い始めました。その後、実際のテストを実行して、次のように確かにその通りであることがわかりました。

方法1

方法2

performance_schema.metadata_locks に MDL_EXCLUSIVE(X) が表示されず、MDL_SHARED(S) が表示されるのは残念です。出力したログを見ると、ここで MDL_SHARED(S) を MDL_EXCLUSIVE(X) にアップグレードするアップグレード操作が実行されたことが分かります。また、以前の互換性リストから、MDL_EXCLUSIVE(X) のみが MDL_SHARED_HIGH_PRIO(SH) をブロックします。したがって、ここでアップグレード操作が実際に実行されたことを確認できるはずです。そうでない場合、SHOW TABLE STATUS[like 'A']はブロックされません。

4. SELECT * FROM A テーブルメタデータロックの待機の分析

SELECT はロックされないと思われるかもしれませんが、それは innodb レベルの話です。MYSQL レベルでは、MDL_SHARED_READ(SR) は次のように設定されます。

方法1

方法2

MDL_SHARED_READ(SR) が存在し、現在ブロックされていることがわかります。

互換性は次のとおりです。

どうやら MDL_SHARED_READ(SR) と MDL_SHARED_HIGH_PRIO(SH) は互換性がないので、待機する必要があります。

5. DROP TABLE Aの分析 テーブルメタデータロックの待機

テーブル A には X ロックがあり、DROP TABLE A には MDL_EXCLUSIVE(X) ロックが必要であり、これは当然 MDL_EXCLUSIVE(X) とは互換性がないため、これを分析するのは簡単です。次のように：

方法1

方法2

このうち、EXCLUSIVE は MDL_EXCLUSIVE(X) と呼ばれるものです。これは存在し、現在ブロックされています。

6. use db もブロックされるのはなぜですか?

-A オプション (または no-auto-rehash) なしで mysql クライアントを使用する場合は、USE DB を使用するときに少なくとも次の操作を行う必要があります。

1. データベース内の各テーブルに対する MDL (SH) ロックは次のとおりです (ブロックが発生したときに情報を提供するには、MDL_context::acquire_lock を呼び出します)

方法1

方法2

USE DB は MDL_SHARED_HIGH_PRIO(SH) により実際にブロックされていることがわかります。

2. 各テーブルをテーブルキャッシュに追加し、テーブルを開きます（open_table_from_share() を呼び出します）。

この状況は、SHOW TABLE STATUS [like 'A'] がブロックされる状況とまったく同じであり、これも MDL ロックの非互換性によって発生します。

分析と分類

これまでの分析から、この失敗の原因は次のように分類できます。

テーブルBに長期間コミットされていないDMLがある
このステートメントは、InnoDB レイヤーのテーブル B の特定のデータに InnoDB 行ロックを追加します。

ステップ 1 により、CREATE TABLE A AS SELECT B がブロックされます<br /> RR モードの SELECT B はテーブル B のデータをロックする必要があり、ステップ 1 でデータがロックされているため、待機がトリガーされ、STATE がデータを送信しています。

ステップ 2 により、他のステートメントがブロックされます<br /> CRATE TABLE A AS SELECT B はテーブル A が作成される前に MDL_EXCLUSIVE(X) を保持するため、このロックにより、MDL_SHARED_HIGH_PRIO(SH)MDL LOCK のみを保持する DESC/SHOW TABLE STATUS/USE DB (非 A) ステートメントを含む、テーブル A に関連する他のすべてのステートメントがブロックされます。 STATE は、テーブル メタデータ ロックを待機中として統合されます。

シミュレーションテスト

テスト環境:

5.7.14

GITD 閉じる

RR 分離レベル

次のスクリプトを使用します:

手順は次のとおりです。

セッション1