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コマンドまとめ

推薦する

binlog2sql と簡単なバックアップおよびリカバリを使用して mysql8.0.20 を構成するための詳細な手順

目次最初のステップのインストールステップ2: MySQLデータを準備する3 番目のステップは、bin...

Vue3.xはコンポーネント通信にmitt.jsを使用します

目次クイックスタート使い方基本原則Vue2.x はコンポーネント通信に EventBus を使用しま...

Node.js パッケージ マネージャー npm の具体的な使用方法

目次目的npm init および package.json ファイルモジュールのインストールと管理モ...

MySQL 基本チュートリアル パート 1 MySQL5.7.18 のインストールと接続チュートリアル

この記事から、MySQL を紹介し学習するための新しい一連の記事がスタートします。なぜ MySQL ...

Vue における Vue.use() の原理と基本的な使用法

目次序文1. 例で理解する2. ソースコードを分析する3. まとめ要約する序文他の人のコンポーネント...

autoconfを使用してMakefileを生成し、プロジェクトをコンパイルする手順

序文Linux では、コンパイルとリンクには Makefile を使用する必要がありますが、適切な ...

Linux 上でプライベート Git サーバーを構築するための詳細なチュートリアル

1. サーバーのセットアップリモート リポジトリは実際にはローカル リポジトリと何ら変わりなく、純粋...

...

Linux プラットフォームの MySQL でリモート ログインを有効にする

開発中、MySQL へのリモートアクセスでよく問題に遭遇します。そのたびに検索する必要があり、面倒に...

Dockerコンテナを介してランプアーキテクチャを構築するプロセス

目次1. Centosイメージを取得する2. nginxイメージをビルドする3. MySQLイメージ...

Navicat による MySQL パーティショニングの実践

MySQLのパーティショニングは、非常に大きなテーブルを管理するのに役立ちます。MySQLのパーティ...

Vueは物流タイムライン効果を実現します

この記事では、物流タイムライン効果を実現するためのVueの具体的なコードを例として紹介します。具体的...

CSS3 でクールなスライス画像カルーセル効果を実現

今日は、CSS を使用してクールな画像カルーセル コンポーネントを作成する方法を学びます。その原理は...

Dockerディスク容量不足の問題を解決する

Docker が配置されているサーバーをしばらく稼働させたところ、サーバーのディスク ディレクトリの...

JS クロスドメイン ソリューション React 構成 リバース プロキシ

クロスドメインソリューションjsonp (get をシミュレート) CORS (クロスオリジンリソー...