8.9.1 InnoDB バッファープール

InnoDB は、データとインデックスをメモリーにキャッシュするためのバッファープールと呼ばれるストレージ領域を維持しています。InnoDB バッファープールの仕組みを知り、頻繁にアクセスされるデータをメモリーに維持するためにそれを利用することは、MySQL チューニングの重要な側面です。

ガイドライン

理想的には、バッファープールのサイズをできるだけ大きな値に設定して、サーバー上のほかのプロセスが過剰なページングなく実行するように、十分なメモリーを残します。バッファープールが大きいほど、InnoDB はさらにインメモリーデータベースのように動作し、ディスクから 1 回データを読み取り、後続の読み取り時に、メモリーからデータにアクセスします。パフォーマンス向上のため、ディスク書き込みをグループ化できるように、バッファープールは挿入および更新操作によって変更されたデータもキャッシュします。

システムの一般的なワークロードに応じて、バッファープール内の各パートの割合を調整した方がよい場合があります。バッファープールがいっぱいになったときにキャッシュするブロックを選択する方法をチューニングし、バックアップやレポートなどの操作のアクティビティーが急増しても頻繁にアクセスされるデータをメモリーに保持できます。

大きなメモリーサイズを備える 64 ビットシステムでは、バッファープールを複数のパートに分割することで、同時操作中のメモリー構造の競合を最小にできます。詳細については、セクション14.13.1.4「複数のバッファープールインスタンスの使用」を参照してください。

内部の詳細

InnoDB は、LRU (Least Recently Used) アルゴリズムのバリエーションを使用して、プールをリストとして管理します。プールに新しいブロックを追加するために、空きが必要な場合、InnoDB は最近もっとも使用されていないブロックを削除し、新しいブロックをリストの途中に追加します。この「ミッドポイント挿入戦略」はリストを 2 つのサブリストとして扱います。

• 先頭は、最近アクセスされた「新しい」 (または「若い」) ブロックのサブリストです。

• 末尾は、最近あまりアクセスされていない「古い」ブロックのサブリストです。

このアルゴリズムでは、クエリーによって頻繁に使用されるブロックを新しいサブリストに維持します。古いサブリストはあまり使用されないブロックを格納し、これらのブロックはエビクションの候補になります。

LRU アルゴリズムはデフォルトで次のように動作します。

• バッファープールの 3/8 が古いサブリストに割り振られます。

• リストのミッドポイントは、新しいサブリストの末尾と古いサブリストの先頭が接する境界です。

• InnoDB がブロックをバッファープールに読み込むと、最初にそれをミッドポイント (古いサブリストの先頭) に挿入します。ブロックを読み取ることができるのは、SQL クエリーなどのユーザー指定の操作や、InnoDB によって自動的に実行される先読み操作の一部として、必要であるためです。

• 古いサブリスト内のブロックにアクセスすると、それが「若く」なり、バッファープールの先頭 (新しいサブリストの先頭) に移動されます。ブロックが必要であるために読み取られた場合、最初のアクセスはただちに行われ、ブロックが若くなります。先読みのためにブロックが読み取られた場合、最初のアクセスはただちに行われません (ブロックが削除されるまでまったく行われないこともあります)。

• データベースが動作すると、アクセスされていないバッファープール内のブロックが、リストの末尾に移動されることによって、「古く」なります。新しいサブリストと古いサブリストの両方のブロックは、ほかのブロックが新しくなると、古くなります。古いサブリストのブロックは、ブロックがミッドポイントに挿入されたときも古くなります。最終的に、長い間使われないままのブロックは、古いサブリストの末尾に到達し、削除されます。

デフォルトで、クエリーによって読み取られたブロックは、ただちに新しいサブリストに移動され、それらが長時間バッファープールにとどまることを意味します。テーブルスキャン (mysqldump 操作または WHERE 句のない SELECT ステートメントで実行されるような) によって、大量のデータがバッファープールに取り込まれ、新しいデータが再度使われることがない場合でも、同等の量の古いデータが削除されることがあります。同様に、先読みバックグラウンドスレッドによってロードされ、その後 1 回だけアクセスされたブロックは新しいリストの先頭に移動されます。こうした状況では、頻繁に使用されるブロックが古いサブリストに押し出され、そこでそれらがエビクション対象になることがあります。

構成オプション

いくつかの InnoDB システム変数で、バッファープールのサイズを制御し、LRU アルゴリズムをチューニングできます。

• innodb_buffer_pool_size

  バッファープールのサイズを指定します。バッファープールが小さく、十分なメモリーがある場合、プールを大きくすると、クエリーが InnoDB テーブルにアクセスするときに必要なディスク I/O の量が減ることによってパフォーマンスが向上することがあります。

• innodb_buffer_pool_instances

  バッファープールを、それぞれ独自の LRU リストと関連データ構造を持つユーザー指定の数の個別の領域に分割して、同時メモリー読み取りおよび書き込み操作中の競合を削減します。このオプションは、innodb_buffer_pool_size を 1G バイト以上のサイズに設定した場合にのみ有効になります。指定した合計サイズは、すべてのバッファープール間で分割されます。最高の効率を得るには、innodb_buffer_pool_instances と innodb_buffer_pool_size の組み合わせを、各バッファープールインスタンスが少なくとも 1G バイトになるように指定します。

• innodb_old_blocks_pct

  InnoDB が古いブロックサブリストに使用するバッファープールのおおよその割合を指定します。値の範囲は 5 から 95 です。デフォルト値は 37 (つまり、プールの 3/8 ) です。

• innodb_old_blocks_time

  古いサブリストに挿入されたブロックが、その最初のアクセス後、新しいサブリストに移動するまでに、そこにとどまる必要のある時間をミリ秒 (ms) 単位で指定します。デフォルト値は 0 です。挿入後にどのくらいの期間でアクセスが発生するかに関係なく、古いサブリストに挿入されたブロックは、Innodb がバッファープールから、挿入されたブロックのページの 1/4 を削除したときに、新しいサブリストに移動されます。この値が 0 より大きい場合、ブロックは最初のアクセス後、少なくともそのミリ秒でアクセスが発生するまで、古いサブリストに残ります。たとえば、1000 の値では、ブロックは最初のアクセス後、それらが新しいサブリストに移動される資格を得るまで、1 秒間古いサブリストにとどまります。

innodb_old_blocks_time を 0 より大きく設定すると、1 回のテーブルスキャンで、スキャンだけに使用されたブロックによって新しいサブリストがいっぱいになることを防ぎます。スキャンで読み取られるブロック内の行は、すばやく連続して何回もアクセスされますが、その後ブロックは使用されません。innodb_old_blocks_time がブロックを処理するより長い時間の値に設定されていれば、ブロックは「古い」サブリストに残り、リストの末尾まで古くなり、すぐに削除されます。このようにすると、1 回のスキャンだけに使用されるブロックが、新しいサブリスト内の頻繁に使用されるブロックの損失を促進しません。

innodb_old_blocks_time は実行時に設定できるため、テーブルスキャンやダンプなどの操作の実行中にそれを一時的に変更できます。

SET GLOBAL innodb_old_blocks_time = 1000;... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;

目的が、テーブルの内容をバッファープールに入れることによって、バッファープールを「ウォームアップ」することである場合、この戦略は適用されません。たとえば、通常の使用期間後、データは通常バッファープール内にあるため、ベンチマークテストでは、多くの場合サーバーの起動時にテーブルまたはインデックススキャンを実行します。この場合、少なくともウォームアップフェーズが完了するまで、innodb_old_blocks_time を 0 に設定されたままにします。

バッファープールのモニタリング

InnoDB 標準モニターからの出力には、BUFFER POOL AND MEMORY セクションに、バッファープール LRU アルゴリズムの操作に属するいくつかのフィールドが含まれます。

• Old database pages: バッファープールの古いサブリスト内のページ数。

• Pages made young, not young: バッファープールの先頭 (新しいサブリスト) に移動された古いページ数と、新しくなることなく、古いサブリストに残されているページ数。

• youngs/s non-youngs/s: 若くされたか、またはされなかった古いページへのアクセスの数。このメトリックは、2 つの点で、前の項目のそれとは異なります。まず、これは古いページにのみ関連します。2 つめに、それはページへのアクセス数に基づき、ページ数に基づきません。(特定のページに複数のアクセスがあることがあり、そのすべてがカウントされます。)

• young-making rate: ブロックがバッファープールの先頭に移動されるヒット。

• not: ブロックがバッファープールの先頭に移動されないヒット (満たされていない遅延のため)。

young-making 率と not 率は通常バッファープール全体のヒット率まで達することはありません。古いサブリスト内のブロックのヒットによって、それらが新しいサブリストに移動されますが、新しいサブリスト内のブロックへのヒットでは、それらが先頭から特定の距離にある場合にのみ、リストの先頭に移動されます。

モニターからの先述の情報が、LRU のチューニングの決定に役立つことがあります。

• 大きなスキャンが実行中でないときに、youngs/s 値がきわめて低いことが確認された場合、遅延時間を減らすか、古いサブリストに使用されるバッファープールの割合を増やす必要がある可能性があることを示しています。割合を増やすと、古いサブリストが大きくなるため、そのサブリスト内のブロックが末尾に移動され、削除されるまで長くかかるようになります。これにより、再度アクセスされ、若くされる可能性が高くなります。

• 大きなテーブルスキャンの実行中 (および大量の youngs/s) に大量の non-youngs/s が確認されない場合、遅延値を大きくするようにチューニングします。

注記

InnoDB モニターの出力で示される 1 秒あたりの平均は、現在の時間と InnoDB モニターの出力が最後に出力された時間の間の経過時間に基づいています。

InnoDB モニターの詳細については、セクション14.15「InnoDB モニター」を参照してください。

INNODB_BUFFER_POOL_STATS テーブルと InnoDB バッファープールのサーバーステータス変数は、SHOW ENGINE INNODB STATUS 出力によって提供される、多くの同じバッファープール情報を提供します。