データクエリが多すぎる場合に MySQL が OOM を引き起こすかどうかについて話しましょう

この記事では、MySQL データ クエリが多すぎる場合に OOM が発生するかどうかに関する関連知識を提供します。皆様のお役に立てれば幸いです。

ホスト メモリには 100G しかありません。200G の大きなテーブルのテーブル全体をスキャンする必要があります。DB ホストのメモリは使い果たされてしまいますか?

論理バックアップを実行する場合、データベース全体をスキャンするだけではありませんか?これでメモリがすべて消費されてしまうのであれば、論理バックアップはずっと前に失敗するのではないでしょうか?

したがって、大きなテーブルのフルテーブルスキャンには問題がないようです。どうしてこれなの？

サーバー層に対するフル テーブル スキャンの影響

200G InnoDB テーブルに対してフル テーブル スキャンを実行するとします。 db1.t テーブルスキャン。もちろん、スキャン結果をクライアントに保存したい場合は、次のようなコマンドを使用します。

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

InnoDB データはプライマリ キー インデックスに保存されるため、フル テーブル スキャンでは実際にテーブル スキャンが直接スキャンされます。テーブル t の主キーインデックス。このクエリ ステートメントには他の判定条件がないため、見つかった各行は結果セットに直接配置され、クライアントに返されます。

それでは、この「結果セット」はどこに存在するのでしょうか?

サーバーは完全な結果セットを保存する必要はありません。データの取得と送信のプロセスは次のとおりです。

行を取得し、**"net_buffer" に書き込みます。このメモリのサイズはパラメータ「net_buffer_length」によって定義されます。デフォルトは 16k です。送信が成功した場合は **「net_buffer」をクリアし、引き続き次の行をフェッチして「net_buffer」に書き込みます**

送信関数が **"EAGAIN" または "WSAEWOULDBLOCK"** を返した場合は、ローカル ネットワーク スタック (ソケット送信バッファ) がいっぱいで待機中であることを意味します。ネットワーク スタックが再び書き込み可能になるまで、送信を続行します。

クエリ結果送信プロセス

Visible:

1 クエリ送信プロセス中に、MySQL が占有する最大内部メモリは **"net_buffer_length"** ですが、これは 200G

• ソケット送信バッファにも達せず、200G にも達しません (デフォルトの定義) /proc/sys/net/core/wmem_default)、ソケットの送信バッファがいっぱいの場合、データの読み取りプロセスは一時停止されます

• したがって、MySQL は実際には「読み取り中に送信」します。つまり、クライアントの受信が遅い場合、MySQL サーバーは結果を送信できず、トランザクションの実行時間が長くなります。

たとえば、次の状態は、クライアントが「ソケット受信バッファ」の内容を読み込まなかった場合に、サーバーの show processlist で表示される結果です。

サーバー側の送信ブロック

#状態が常に「クライアントに送信中」である場合は、サーバー側のネットワーク スタックが障害を起こしていることを意味します。一杯。

クライアントが –quick パラメーターを使用する場合、mysql_use_result メソッドが使用されます。つまり、1 行を読み取り、1 行を処理します。特定のビジネスのロジックが比較的複雑で、データの各行が読み取られた後に処理されるロジックが非常に遅いと、クライアントが次のデータ行をフェッチするのに長い時間がかかり、上記の結果が表示される場合があります。

したがって、通常のオンライン ビジネスでは、クエリがほとんど結果を返さない場合は、**"mysql_store_result"** インターフェイスを使用してクエリ結果をローカル メモリに直接保存することをお勧めします。

もちろん、クエリが返す結果はそれほど多くないことが前提です。多すぎると、大規模なクエリが実行されるため、クライアントが 20G 近くのメモリを占有することになるため、代わりに「mysql_use_result」インターフェイスを使用する必要があります。

あなたが保守を担当している MySQL の「クライアントへの送信」に多くのスレッドが表示される場合は、ビジネス開発の学生にクエリ結果を最適化し、返される結果の数が妥当であるかどうかを評価してもらいたいことを意味します。

この状態のスレッド数をすぐに減らすには、**"net_buffer_length"** を大きく設定します。

インスタンス上の多くのクエリステートメントのステータスが「データ送信中」になることがありますが、ネットワークを確認すると問題ありません。データ送信に時間がかかるのはなぜですか?

クエリ ステートメントのステータス変更は次のとおりです。

MySQL クエリ ステートメントが実行フェーズに入ったら、まずステータスを「データ送信中」に設定します

• 次に、実行結果のカラム関連情報(メタデータ)をクライアントに送信します

• その後、ステートメントの実行処理を続行します

• 実行が完了したら、ステータスを空の文字列に設定します。

• つまり、「データの送信」は必ずしも「データの送信」を意味するわけではなく、実行プロセスのどの段階でも構いません。たとえば、ロック待機シナリオを構築し、データ送信ステータスを確認できます。

テーブル全体の読み取りがロックされています:

データ送信ステータス

それは確認できますセッション 2 がロックを待機していることを示します。ステータスはデータ送信中と表示されます。

• スレッドが「クライアントによる結果の受信を待機中」の状態にある場合にのみ、「クライアントに送信中」と表示されます。 「データの送信中」と表示されますが、これは単に「実行中」を意味します

• したがって、クエリの結果はセグメントでクライアントに送信されるため、テーブル全体がスキャンされ、クエリが返されます。大量のデータがあり、メモリが爆発しません。

上記はサーバー層の処理ロジックですが、InnoDB エンジンではどのように処理されるのでしょうか?

InnoDB に対するフル テーブル スキャンの影響

InnoDB メモリの機能の 1 つは、更新された結果を保存し、REDO ログと連携することです。ランダム性を避けるため、ディスクに書き込みます。 メモリのデータ ページはバッファ プール (BP と呼ばれます) で管理され、BP は WAL の更新を高速化する役割を果たします。

BP はクエリを高速化することもできます。

WAL により、トランザクションがコミットされたとき、ディスク上のデータ ページは古いです。データ ページをすぐに読み取るクエリがある場合、REDO ログをデータ ページにすぐに適用する必要がありますか? ＃＃＃＃＃＃不要。この時点ではメモリデータページの結果が最新なので、そのままメモリページを読み込んでください。現時点では、クエリはディスクを読み取る必要がなく、結果はメモリから直接フェッチされるため、非常に高速です。したがって、バッファ プールによりクエリを高速化できます。

クエリに対する BP の高速化効果は、重要な指標、つまりメモリ ヒット率に依存します。

システムの現在の BP ヒット率は、show Engine innodb status の結果で確認できます。一般に、安定したサービスを備えたオンライン システムで応答時間が要件を確実に満たすためには、メモリ ヒット率が 99% 以上である必要があります。

show Engine innodb status を実行すると、「Buffer pool hit rate」という文字が表示され、現在のヒット率が表示されます。たとえば、下の図のヒット率は 100% です。

#クエリに必要なすべてのデータ ページをメモリから直接取得できればそれが最良であり、対応するヒット率は 100% になります。

InnoDB バッファ プールのサイズは、パラメータ **"innodb_buffer_pool_size"** によって決定されます。通常は、使用可能な物理メモリの 60% ～ 80% に設定することをお勧めします。

約 10 年前、1 台のマシンのデータ量は数百 G、物理メモリは数 G でしたが、現在では、多くのサーバーが 128G 以上のメモリを搭載できるようになりましたが、1 台のマシンのデータ量は減少しています。 Tレベルに到達しました。

したがって、**"innodb_buffer_pool_size"** はディスク データ量よりも小さいことが一般的です。バッファ プールがいっぱいで、データ ページをディスクから読み取る必要がある場合は、古いデータ ページを削除する必要があります。

InnoDB メモリ管理

最も最近使用されていない (LRU) アルゴリズムを使用して、使用されていないデータを削除します。長い間。 基本的な LRU アルゴリズムInnoDB が BP を管理するために使用する LRU アルゴリズムは、リンク リストを使用して実装されます:

state1、リンクされたリストの先頭リストは P1 で、P1 を示します。 最近アクセスされたデータ ページです。

このとき、読み取り要求は P3 にアクセスするため、状態 2 になり、P3 は状態 2 に移動します。 Front
状態 3 は、今回アクセスしたデータ ページがリンク リストに存在しないことを示します。そのため、新しいデータ ページ Px を BP に申請し、BP の先頭に追加する必要があります。リンクされたリスト。しかし、メモリがいっぱいであるため、新しいメモリを要求できません。したがって、リンク リストの最後にある Pm データ ページ メモリがクリアされ、Px の内容が保存され、リンク リストの先頭に配置されます。
最終的に、データは最も長い間アクセスされていないページＰｍは削除される。

• この時点でテーブル全体のスキャンを実行したい場合はどうなりますか? 200G テーブルをスキャンしたいと考えています。これは履歴データ テーブルであり、通常はアクセスする企業はありません。

次に、このアルゴリズムに従ってスキャンすると、現在の BP 内のすべてのデータが削除され、スキャン プロセス中にアクセスされたデータ ページのコンテンツが保存されます。つまり、BP は主にこの履歴データ テーブルのデータを保存します。

ビジネス サービスを行っている図書館の場合、これは受け入れられません。 BP メモリ ヒット率が急激に低下し、ディスク圧力が増加し、SQL ステートメントの応答が遅くなっていることがわかります。

したがって、InnoDB は元の LRU を直接使用できません。 InnoDB がそれを最適化します。

改良された LRU アルゴリズム

InnoDB は、リンク リストを 5:3 の比率に従って新しい領域と古い領域に分割します。この図では、LRU_old は古い領域の最初の位置 (リンクされたリスト全体の 5/8) を指します。つまり、リンク リストの先頭近くの 5/8 が新しい領域であり、リンク リストの末尾近くの 3/8 が古い領域です。

LRUアルゴリズム実行処理の改善:

状態1でP3にアクセスする必要がありますP3はNew領域にあるため最適化前のLRUと同じなので先頭に移動しますリンク リストの => 状態 2

その後、現在のリンク リストに存在しない新しいデータ ページにアクセスする必要があります。このとき、データ ページ Pm はまだ削除されていますが、新たに挿入されたデータページ Px は **"LRU_old"** に配置されます。

旧領域のデータページは、アクセスされるたびに以下の判断を行う必要があります。

データページが存在する場合LRU リンク リストに 1 秒以上存在する場合は、リンク リスト ヘッダーに移動します。

データ ページが LRU リンク リストに存在する時間が 1 秒未満の場合、位置は変更されません。 1 秒はパラメータ **"innodb_old_blocks_time"** によって制御されます。デフォルト値は 1000 (ミリ秒単位) です。

この戦略は、テーブル全体のスキャンと同様の操作を処理するように調整されています。または、200G 履歴データ テーブルをスキャンします:

4. スキャン プロセス中、新しく挿入する必要があるデータ ページは古い領域

5 に配置されます。1 つの中に複数のレコードがありますデータ ページ、このデータ ページは複数回アクセスされますが、シーケンシャル スキャンにより、このデータ ページの最初のアクセスと最後のアクセスの間の時間間隔は 1 秒を超えないため、古い領域に保持されたままになります。

6. 後続のデータのスキャンを続ける場合、前のデータ ページには再度アクセスされないため、リンク リストの先頭 (新しい領域) に移動する機会はありません。すぐに排除される。

この戦略の最大の利点は、この大きなテーブルをスキャンするプロセスで、BP も使用されますが、若い領域にはまったく影響を与えず、バッファーが確実に維持されることであることがわかります。プールは通常のビジネス クエリに応答します。ヒット率。

まとめ

MySQL は計算と発行を同時に行うロジックを採用しているため、大量のデータを含むクエリ結果の場合、完全なデータは得られません。サーバー側に保存された結果セット。したがって、クライアントが時間内に結果を読み取れなかった場合、MySQL クエリ プロセスはブロックされますが、メモリがバーストすることはありません。

InnoDB エンジンに関しては、消去戦略により、大規模なクエリによってメモリが急増することはありません。さらに、InnoDB は LRU アルゴリズムを改良したため、バッファ プール上のコールド データのフル テーブル スキャンの影響も制御できます。

フル テーブル スキャンは依然として IO リソースを消費するため、ビジネスのピーク時にオンラインでメイン データベースに対してフル テーブル スキャンを直接実行することは依然として不可能です。

推奨学習: mysql ビデオ チュートリアル

以上がデータクエリが多すぎる場合に MySQL が OOM を引き起こすかどうかについて話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。