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

USE DB 輻輳に対する MySQL ソリューションの詳細な説明 セッション2セッション3セッション 4------use test;---use test;begin; delete from b;-------------use test;create table a asselect * from b;(テーブル b の innodb 行ロックがブロックされているため)------------show table status like 'a';(テーブル a の MDL LOCK がブロックされているため)------------use test(テーブル a の MDL LOCK がブロックされているため)

最後に、次の待機ステータスが表示されます。

このようにして、オンライン状態を完全にシミュレートできます。セッション 1 で操作を強制終了すると、すべて自然にロックが解除されます。performance_schema.metadata_locks の出力を見てみましょう。

上記の出力を見ることができますが、LOCK_TYPE: SHARED に注意する必要があります。LOCK_TYPE: SHARED_HIGH_PRIO をブロックすることはできません (付録または MDL LOCK 分析に関する以前の記事を参照してください)。上で分析したように、ここでは実際には MDL_EXCLUSIVE(X) へのアップグレード操作が実行されます。

要約する

RC モードでは、CREATE TABLE A SELECT B でテーブル B に INNODB ROW LOCK が設定されていなくても、テーブル B が非常に大きい場合は、テーブル A も MDL_EXCLUSIVE(X) によって保護され、USE DB\SHOW TABLE STATUS 待機もトリガーされます。

GTID が有効になっている場合、CREATE TABLE A SELECT B などのステートメントは使用できません。

DML/DDL が混在するシステムでは、同時実行性に注意する必要があります。この例のように、同時実行性が高い状況に注意すれば、それを回避する方法を見つけることができます。

この事例は、長期間コミットされていないトランザクションが悲劇につながる可能性があることを改めて示しているため、N 秒以上完了していないトランザクションを監視することをお勧めします。

付録

MDLロックタイプ

互換性マトリックス

待機キューの優先度マトリックス

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

障害の説明

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

MYSQL 5.6.16

RR 分離レベル

GITD 閉じる

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

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

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

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

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

640?wx_fmt=png&wxfrom=5&wx_lazy=1

障害情報抽出

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

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

<<:  Node.js コード実行をバイパスするためのヒントのまとめ

>>:  仕事でよく使うLinuxコマンドまとめ

推薦する

Webデザインにおけるフォームデザインテクニックのまとめ

「脳が多数の領域間の関係を処理できるように、入力は論理的なグループに分割する必要があります。」 – ...

TypeScript インターフェースの紹介

目次1. インターフェース定義2. 属性2.1 オプション属性2.2 読み取り専用プロパティ3. ク...

div が contentEditable=true に設定されている場合、コンテンツをリセットした後にカーソルを配置することはできません。

最近、絵文字にコメントする機能が必要なコメント機能に取り組んでいたため、 contentEditab...

Docker での Tomcat インストールの 404 問題の解決方法

tomcat の containerID を見つけて、tomacat ディレクトリに入ります。 [r...

JavaScriptの基本構文とデータ型の詳細な説明

目次JavaScript のインポート1. 内部ラベル2. 外部紹介基本的な構文データ型番号弦ブール...

Vue要素はテーブルの追加、削除、データの変更を実装します

この記事では、テーブル内のデータを追加、削除、変更するためのvue要素の具体的なコードを参考までに共...

MySQL 8.0.18 ハッシュ結合は左/右結合をサポートしていません 左と右の結合の問題

MySQL 8.0.18 では、インデックスが作成されていないフィールドに適用でき、等価値の関連付け...

デュアル VIP を使用した高可用性 MySQL クラスタの構築

目次1. プロジェクトの説明: 2. プロジェクト環境: 2. プロジェクトの手順: 3. プロジェ...

Vueでルーティング権限を動的に設定する主なアイデア

以前、インターネット上で動的ルーティング設定をいくつか見たことがありましたが、現在のプロジェクトとは...

Ubuntu 16.04/18.04 に Pycharm と Ipython をインストールするチュートリアル

Ubuntu 18.04の場合1. sudo apt install python 。コマンドライン...

Mapper SQL ステートメント フィールドとエンティティ クラス属性名の関係は何ですか?

背景: 1. データベースに通知テーブルがある あなたは見ることができますgmt_create、通知...

React双方向データバインディングの原理についての簡単な説明

目次双方向データバインディングとは双方向データバインディングの実装データ影響ビュービューはデータに影...

Java で ffmpeg を呼び出してビデオ形式を flv に変換する方法の詳細な説明

Java で ffmpeg を呼び出してビデオ形式を flv に変換する方法の詳細な説明注:以下のプ...

小さな画面のモバイルデバイス向けにWebページを設計する際に注意すべきこと

その理由は、このタイプの Web ページが WAP と呼ばれるワイヤレス プロトコルから生成されたた...

Linuxシステムにおけるプロセス管理の詳細な説明

目次1. プロセスとスレッドの概念2. プロセス管理とは何ですか? 3. プロセス管理の役割4. L...