データクエリが多すぎる場合に MySQL が OOM を引き起こすかどうかについて話しましょう-mysql チュートリアル-php.cn

この記事では、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:

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

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

ソケット送信バッファにも達せず、200G にも達しません (デフォルトの定義) /proc/sys/net/core/wmem_default)、ソケットの送信バッファがいっぱいの場合、データの読み取りプロセスは一時停止されます
したがって、MySQL は実際には「読み取り中に送信」します。つまり、クライアントの受信が遅い場合、MySQL サーバーは結果を送信できず、トランザクションの実行時間が長くなります。

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

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

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

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

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

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

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

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

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

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

次に、実行結果のカラム関連情報(メタデータ)をクライアントに送信します
その後、ステートメントの実行処理を続行します
実行が完了したら、ステータスを空の文字列に設定します。
つまり、「データの送信」は必ずしも「データの送信」を意味するわけではなく、実行プロセスのどの段階でも構いません。たとえば、ロック待機シナリオを構築し、データ送信ステータスを確認できます。

データ送信ステータス

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

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

スレッドが「クライアントによる結果の受信を待機中」の状態にある場合にのみ、「クライアントに送信中」と表示されます。「データの送信中」と表示されますが、これは単に「実行中」を意味します
したがって、クエリの結果はセグメントでクライアントに送信されるため、テーブル全体がスキャンされ、クエリが返されます。大量のデータがあり、メモリが爆発しません。

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% に設定することをお勧めします。

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

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

InnoDB メモリ管理

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

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

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

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

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

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

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

データクエリが多すぎる場合に MySQL が OOM を引き起こすかどうかについて話しましょう 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 サイトの他の関連記事を参照してください。