MySQL ダーティ ページ フラッシュとテーブル スペースの縮小の原理の分析

mysql ダーティページ

WAL メカニズムにより、InnoDB はステートメントを更新するときに、書き込みログと呼ばれるディスク操作 (REDO ログ) を作成します。REDO ログはメモリに書き込まれた後、クライアントに返され、更新が成功したことを示します。

メモリ内のデータをディスクに書き込む処理をフラッシュと呼びます。フラッシュ前は、REDO ログに基づいてデータが更新されたがまだ書き込まれておらず、データベースが古いため、実際のデータはデータベース内のデータと一致していません。メモリ データ ページの内容がディスク データ ページと一致していない場合、メモリ ページはダーティ ページと呼ばれます。メモリに書き込まれた後は、一貫性が保たれ、クリーン ページと呼ばれます。

MySQL の動作が時々非常に遅くなる場合は、ダーティ ページがフラッシュされている可能性があります。データベースフラッシュをトリガーするプロセス

• REDO ログがいっぱいになると、システムはすべての更新操作を停止し、チェックポイントを前方にプッシュして、さらに書き込みを行うためのスペースを確保します。
• システムのメモリが不足し、新しいメモリ ページが十分でない場合は、一部のデータ ページが削除され、他のデータ ページ用に残されます。ダーティ ページが削除されると、まずディスクに書き込まれます。
• mysql がアイドル状態のとき。
• mysqlを通常通りシャットダウンする場合
• 最初のケースでは、REDO ログがいっぱいになりますが、これは InnoDB が回避したい状況です。システム全体を更新できなくなるため、これは許容できません。
• 2番目のケースでは、メモリがいっぱいで、まずディスクに書き込む必要があります。InnoDBはバッファプールを使用してメモリを管理します。3つの状態があります。
• 未使用のメモリページ
• 中古品できれいなページです
• 使用済みページとダーティページ（削除時にディスクに書き込む必要がある）

そのため、データベースを使用するときに、ダーティ ページの処理が原因でデータベースのパフォーマンスが突然低下することがあります。

ダーティページフラッシュ制御戦略

• Innodb_io_capacity パラメータ。このパラメータは Innodb にディスク IO 容量を伝えます。 （計算式で算出）
• InnoDBのフラッシュには、ダーティページ率とREDOログ書き込み速度という2つの主な要因があります。
• innodb_max_derty_pages_pctはダーティページ率の上限で、デフォルトは75%です。ダーティページ率が75%を超えないようにInnodb_io_capacityパラメータ値を調整し、テーブルスペースを縮小します。

シナリオ例: データベースが占有する領域が大きすぎるため、最大のテーブルのデータの半分が削除されますが、テーブルのサイズは変更されません。

データ削除プロセス

R4 を削除する場合、InnoDB エンジンはレコード R4 のみを削除済みとしてマークします。後で ID が 300 から 600 のレコードが追加された場合、この位置は再利用されますが、ディスク ファイルのサイズは縮小されません。

データ ページ上のすべてのレコードが削除された場合、データ ページを再利用できます。

注: データ ページの再利用はレコードの再利用とは異なります。

• たとえば、レコード R4 が削除された場合、ID 400 の行が挿入されると、そのスペースは直接再利用できます。ただし、ID 800 の行が挿入されると、その位置は再利用できません。
• ただし、データ ページ Page A 全体のすべてのレコードが削除された後、pageA は再利用可能としてマークされます。ID=50 のレコードを挿入するために新しいデータ ページが必要な場合は、PageA を再利用可能です。
• delete コマンドを使用してテーブル データ全体を削除すると、すべてのデータ ページが再利用可能としてマークされますが、ディスク上のファイルは小さくなりません。

データフローを挿入

データがインデックス順に挿入されると、インデックスはコンパクトになりますが、ランダムに挿入されると、インデックス データ ページのページングが発生します。

ページA がすでにいっぱいの場合、別のデータ行を挿入するとどうなるでしょうか? A がいっぱいなので、ID 550 のデータを挿入すると、データを保存するために新しいページ pageB が要求されます。分割が完了すると、pageA の最後に穴が残ります。

インデックスの値を更新すると、古い値が削除され、新しい値が挿入されるため、これも穴が発生します。

縮小する空間

テーブル A と同じ構造を持つ新しいテーブル B を作成します。テーブル A から主キー ID の昇順で 1 行ずつデータを読み取り、テーブル B に挿入します。テーブル B に穴がなくなり、データ ページの使用率が高くなります。テーブル B を一時テーブルとして使用する場合、テーブル A から B にデータをインポートした後、A を B に置き換えます。これにより、A も実質的に縮小されます。

DDL プロセス全体を通じてテーブル A を更新できないため、この DDL はオンラインではありません。バージョン 5.6 以降では、プロセスが変更されました。

一時ファイルを作成し、A 内のすべてのデータ ページをスキャンします。

データページ内のAのレコードを使用してB+ツリーを生成し、一時ファイルに保存します。

Aのすべての操作をログファイルに記録する

一時ファイルが生成された後、ログ ファイルの操作が一時ファイルに適用され、表 A と同じ論理データを持つデータ ファイルが得られます。

テーブルAのデータファイルを一時ファイルに置き換える

アイコン

図 3 のプロセスとの違いは、ログ ファイルの記録と再生操作機能が存在するため、このソリューションでは、テーブルを再構築するプロセス中にテーブル A に対する追加、削除、および変更操作が可能になる点です。

テーブルを再構築するには、alter table A engine=InnoDB コマンドを使用します。 MySQL 5.5 より前では、このコマンドの実行プロセスは上記で説明したものと似ています。唯一の違いは、一時テーブル B を自分で作成する必要がないことです。MySQL は、データの転送、テーブル名の交換、古いテーブルの削除などの操作を自動的に完了します。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。