ホストのメモリは 100 GB しかありません。200 GB のテーブルに対してフル テーブル スキャンを実行する場合、DB ホストのメモリは使い果たされますか? 論理バックアップを実行する場合、データベース全体をスキャンするだけではないでしょうか?もしこのようなことが起こると、すべてのメモリが消費され、論理バックアップはずっと前に失敗しているはずですよね? サーバー層でのフルテーブルスキャンの影響ここで、200G の InnoDB テーブル db1.t に対して完全なテーブルスキャンを実行するとします。もちろん、スキャン結果をクライアントに保存する場合は、次のようなコマンドを使用します。 mysql -h$ホスト -P$ポート -u$ユーザー -p$pwd -e "db1.t から * を選択" > $target_file InnoDB データは主キー インデックスに格納されるため、フル テーブル スキャンでは実際にはテーブル t の主キー インデックスが直接スキャンされます。このクエリ ステートメントには他の判断条件がないため、見つかった各行は結果セットに直接格納され、クライアントに返されます。 では、この「結果セット」はどこに存在するのでしょうか?
クエリ結果送信プロセス 見える:
つまり、MySQL は実際には「読み取りと送信」を行っています。つまり、クライアントがデータを受信する速度が遅い場合、MySQL サーバーは結果を送信できず、トランザクションの実行時間が長くなります。 たとえば、次のステータスは、クライアントがソケット受信バッファの内容を読み取らない場合に、サーバー上で show processlist によって表示される結果です。 サーバーがブロックされた送信 状態が常に「クライアントに送信中」となっている場合は、サーバーのネットワーク スタックがいっぱいであることを意味します。 クライアントが –quick パラメータを使用する場合、mysql_use_result メソッドが使用されます。つまり、1 行ずつ読み取り、1 行ずつ処理します。ある業務のロジックが比較的複雑であると仮定します。各行のデータを読み取ってから処理するロジックが非常に遅い場合、クライアントが次の行のデータを取得するのに長い時間がかかり、上図のような結果になる可能性があります。 したがって、通常のオンライン ビジネスでは、クエリが返す結果が少ない場合は、 mysql_store_resultインターフェイスを使用して、クエリ結果をローカル メモリに直接保存することをお勧めします。 もちろん、前提として、クエリは結果をほとんど返しません。数が多すぎると、大きなクエリが実行されるため、クライアントは 20G 近くのメモリを占有することになります。この場合、代わりにmysql_use_resultインターフェイスを使用する必要があります。 自分が管理を担当している MySQL データベースで「クライアントに送信中」状態のスレッドが多数見られる場合、ビジネス開発の同僚にクエリ結果を最適化し、返される結果の数が妥当かどうかを評価するよう依頼する必要があります。 この状態のスレッドの数をすぐに減らしたい場合は、 net_buffer_length をより大きな値に設定できます。 時々、インスタンスに「データを送信中」というステータスのクエリ ステートメントが多数表示されますが、ネットワークの問題はありません。データの送信になぜ時間がかかるのでしょうか。
つまり、「データの送信」は必ずしも「データの送信」を意味するわけではなく、実行プロセスのどの段階を意味する場合もあります。たとえば、ロック待機シナリオを構築し、データ送信ステータスを確認できます。 テーブル全体の読み取りがロックされています:
データ送信ステータス セッション2がロックを待機しており、ステータスが「データ送信中」と表示されていることがわかります。
したがって、クエリ結果はセグメント単位でクライアントに送信されるため、テーブル全体をスキャンして大量のデータを返してもメモリが爆発的に増加することはありません。 上記はサーバー層の処理ロジックですが、InnoDB エンジンではどのように処理されるのでしょうか。 InnoDB におけるフルテーブルスキャンの影響InnoDB メモリの機能の 1 つは、更新結果を保存し、REDO ログと連携してランダムなディスク書き込みを回避することです。 メモリ内のデータページはバッファプール(略してBP)で管理されます。WALでは、BPは更新を高速化する役割を果たします。 WAL のため、トランザクションがコミットされると、ディスク上のデータ ページは古くなります。データ ページをすぐに読み取るクエリがある場合、REDO ログをデータ ページにすぐに適用する必要がありますか? 不要。この時点ではメモリデータページの結果が最新であるため、メモリページを直接読み取ることができます。このとき、クエリはディスクを読み取る必要がなく、結果はメモリから直接取得されるため、非常に高速です。したがって、バッファ プールはクエリを高速化できます。 クエリに対する BP の加速効果は、メモリ ヒット率という重要な指標によって決まります。 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) アルゴリズムが使用されます。
やるべきこと
最後に、最も長い時間アクセスされていないデータ ページ Pm が削除されます。 次に、このアルゴリズムに従ってスキャンすることで、現在の 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
この戦略は、完全なテーブルスキャンなどの操作を処理するように調整されています。または、200G の履歴データ テーブルをスキャンします。 この戦略の最大の利点は、この大きなテーブルをスキャンするプロセスで BP も使用されるにもかかわらず、若い領域に影響を与えず、通常の業務に応じてバッファー プールのクエリ ヒット率を保証することであることがわかります。 まとめMySQL は計算と送信を同時に行うロジックを使用しているため、大量のデータを含むクエリ結果の場合、完全な結果セットはサーバー側に保存されません。したがって、クライアントが結果を時間内に読み取らない場合、MySQL クエリ プロセスはブロックされますが、メモリが爆発的に増加することはありません。 InnoDB エンジンに関しては、排除戦略により、大規模なクエリによってメモリ使用量が急増することはありません。さらに、InnoDB では LRU アルゴリズムが改善されているため、コールド データのフル テーブル スキャンがバッファー プールに与える影響を制御できます。 完全なテーブル スキャンは依然として比較的 IO 集約型であるため、ビジネスのピーク時間帯にオンライン マスター データベースで完全なテーブル スキャンを直接実行することはできません。 MySQL データクエリが多すぎると OOM が発生するかどうかについては、これでこの記事は終わりです。MySQL データクエリ OOM に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
<<: VirtualBox Centos7 の NAT+ホストオンリーネットワークの落とし穴のまとめ
nGrinderとは何ですか? nGrinder は、スクリプトの作成、テストの実行、監視、結果レポ...
目次1. 概要2. 応用例2.1、Docker コンテナ分離名前空間2.2. Docker のフリー...
フォームが送信されると、返された HTML ページが再レンダリングされ、SELECT コントロールの...
前面に書かれた注文テーブル、アクセス記録テーブル、商品テーブルなど、日常生活でデータベースを操作する...
目次方法1 1. 構成とインストールの手順:方法2方法3要約する方法1 1. 構成とインストールの手...
目次序文インライン要素の中央揃えテキストを垂直に中央揃え要素を水平方向に中央揃えにするブロックレベル...
スタックフローからの回答:単に<br>だけで十分です。その他の形式は、XHTML との互...
1. 需要バックエンドは、フロントエンドがツリー構造(重複データなし)に変換するためのデータを提供し...
はじめに: MySQL は、スウェーデンの MySQL AB によって開発されたリレーショナル デー...
<meta name="viewport" content="w...
当社には、外部ネットワークからの干渉を受けることが多いサーバーが多数あります。侵入者はポート 338...
最近、データベースについて学び始めました。最初にやったことは、データベースとは何か、データベースとデ...
この記事では、MySQL 5.7.21のインストールとインストール中に発生した問題を参考までに紹介し...
背景会社のコードは第三者に提供され、利用されます。ソースコードが完全に漏洩しないようにするには、提供...
この記事では、カレンダー効果を素早く実現するためのJavaScriptの具体的なコードを例として紹介...