MySQLデータクエリが多すぎるとOOMが発生するかどうかについての簡単な議論

ホストのメモリは 100 GB しかありません。200 GB のテーブルに対してフル テーブル スキャンを実行する場合、DB ホストのメモリは使い果たされますか?

論理バックアップを実行する場合、データベース全体をスキャンするだけではないでしょうか?もしこのようなことが起こると、すべてのメモリが消費され、論理バックアップはずっと前に失敗しているはずですよね?
したがって、大きなテーブルの完全なテーブルスキャンには問題はないと思われます。これはなぜでしょうか?

サーバー層でのフルテーブルスキャンの影響

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

mysql -h$ホスト -P$ポート -u$ユーザー -p$pwd -e 
	"db1.t から * を選択" > $target_file

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

では、この「結果セット」はどこに存在するのでしょうか?
サーバーは完全な結果セットを保存する必要はありません。データの取得と送信のプロセスは次のとおりです。

• 行を取得して net_buffer に書き込みます。このメモリのサイズはパラメータnet_buffer_lengthによって定義され、デフォルトは16kです。
• net_bufferがいっぱいになるまで繰り返し行を取得し、ネットワークインターフェースを呼び出して行を送信します。
• 送信が成功すると、net_buffer がクリアされ、次の行が取得されて net_buffer に書き込まれます。
• 送信関数が EAGAIN または WSAEWOULDBLOCK を返す場合、ローカル ネットワーク スタック (ソケット送信バッファー) がいっぱいで待機状態になることを意味します。送信を続行する前に、ネットワークスタックが再び書き込み可能になるまで待ちます。
  

クエリ結果送信プロセス

見える：

• クエリが送信されると、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）で管理されます。WALでは、BPは更新を高速化する役割を果たします。
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 年前は、物理メモリが数 GB であるのに対し、1 台のマシン上のデータ量は数百 GB でしたが、現在では多くのサーバーが 128 GB 以上のメモリを搭載しているにもかかわらず、1 台のマシン上のデータ量は T レベルに達しています。

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

InnoDB メモリ管理

最も長く使用されていないデータを削除するには、最近使用されていない (LRU) アルゴリズムが使用されます。

• 基本的なLRUアルゴリズム

やるべきこと

• InnoDB は、リンク リストを使用して実装される BP の LRU アルゴリズムを管理します。
• state1では、リンクリストの先頭はP1であり、P1が最近アクセスされたデータページであることを示しています。
• このとき、読み取り要求が P3 にアクセスするため、状態 2 に変わり、P3 が最前面に移動します。
• 状態 3 は、アクセスされているデータ ページがリンク リストに存在しないため、BP で新しいデータ ページ Px を適用し、リンク リストの先頭に追加する必要があることを意味します。ただし、メモリがいっぱいなので、新しいメモリを要求することはできません。したがって、リンクリストの末尾にあるPmデータページメモリをクリアし、Pxの内容を格納して、リンクリストの先頭に配置します。

最後に、最も長い時間アクセスされていないデータ ページ Pm が削除されます。
この時点でテーブル全体のスキャンを実行するとどうなりますか? 200G のテーブルをスキャンする場合、このテーブルが履歴データ テーブルであれば、通常、どのビジネスもそれにアクセスしません。

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

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

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

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

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

LRU アルゴリズム実行プロセスの改善:

1. 状態 1、P3 にアクセスするには、P3 は新しい領域にあるため、最適化前の LRU と同様に、リンク リストの先頭に移動します => 状態 2
2. 次に、現在のリンク リストに存在しない新しいデータ ページにアクセスする場合、データ ページ Pm は削除されますが、新しく挿入されたデータ ページ Px は LRU_old に配置されます。
3. 古い領域のデータ ページについては、アクセスされるたびに次の判断を行う必要があります。

• データ ページが LRU リスト内に 1 秒以上存在する場合は、リストの先頭に移動します。
• データ ページが LRU リスト内に 1 秒未満存在する場合、その位置は変更されません。 1s はパラメータ innodb_old_blocks_time によって制御され、デフォルト値は 1000、単位は ms です。
  

この戦略は、完全なテーブルスキャンなどの操作を処理するように調整されています。または、200G の履歴データ テーブルをスキャンします。
4. スキャン処理中、新しく挿入されたデータページはすべて古い領域に配置されます。
5. データ ページには複数のレコードがあります。このデータ ページは複数回アクセスされます。ただし、シーケンシャル スキャンにより、このデータ ページの最初のアクセスと最後のアクセスの間の時間間隔は 1 秒を超えないため、古い領域に保持されたままになります。
6. 後続のデータのスキャンを続けます。前のデータ ページは再度アクセスされないため、リンク リストの先頭 (新しい領域) に移動する機会がなく、すぐに削除されます。

この戦略の最大の利点は、この大きなテーブルをスキャンするプロセスで BP も使用されるにもかかわらず、若い領域に影響を与えず、通常の業務に応じてバッファー プールのクエリ ヒット率を保証することであることがわかります。

まとめ

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

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

完全なテーブル スキャンは依然として比較的 IO 集約型であるため、ビジネスのピーク時間帯にオンライン マスター データベースで完全なテーブル スキャンを直接実行することはできません。

MySQL データクエリが多すぎると OOM が発生するかどうかについては、これでこの記事は終わりです。MySQL データクエリ OOM に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。