MyISAMキーキャッシュの概要とチューニング

wp-admin

9年前

MyISAMキーキャッシュの概要とチューニング方法についてまとめました。

※目次をクリックすると目次の下部にコンテンツが表示されます。

１．MyISAMキーキャッシュの概要
２．MyISAMキーキャッシュのチューニング
３．MyISAMキーキャッシュアルゴリズムとチューニング
４．インデックスのプリロードで性能改善
５．マルチキーキャッシュの使用で性能改善

MyISAMキーキャッシュの概要

１）インデックスブロックとデータブロック
　
・MyISAMキーキャッシュは、頻繁にアクセスされるインデックスブロックを保持するメモリーバッファー。
　
・MyISAMテーブルのインデックスブロックはバッファーされ、すべてのスレッドによって共有される。
　
①インデックスブロック
　
・インデックスブロックは、MyISAMインデックスファイルにアクセスする隣接ユニット。通常、インデックスブロックのサイズは、インデックスB-treeのノードサイズと等価。
　
・キーキャッシュという特別な構造が保持されている。
　
・キーキャッシュには、もっとも頻繁に使用されるインデックスがおかれた多数のブロックバッファーが含まれる。
　
・キーキャッシュ構造内のブロックバッファーはすべて同サイズ。このサイズは、テーブルインデックスブロックサイズと比べて、等しい/大きい/小さい場合がある。
　
②データブロック
　
・MySQLは特別なキャッシュを使用せず、OSのファイルシステムキャッシュに依存する。
　
２）キーキャッシュのサイズを制限するには？
　
・key_buffer_sizeシステム変数を使用。
　
・この変数がゼロの場合、利用できるキーキャッシュはなし。
　
・key_buffer_size値が小さすぎてブロックバッファーの最小値が割り当てられない場合、キーキャッシュも使用できない。
　
３）インデックスへのアクセス
　
①キーキャッシュのどれかのブロックバッファー内で利用可能なものがあるかどうかをまず確認。
　
②可能である場合、サーバーはディスク上ではなく、キーキャッシュのデータにアクセス。
　
ディスクからではなく、キャッシュから読むかまたはキャッシュに書きこむ。
　
③上記①で利用可能でなければ、LRU(Least Recently Used)でキャッシュブロックバッファーを選択し、そのデータを要求されたテーブルインデックスブロックのコピーと置き換える。
　
新しいインデックスブロックがキャッシュ内におかれるとすぐ、インデックスデータはアクセス可能となる。
　
置き換えのために選択されたブロックが変更されていた場合、ブロックは「汚染されている」と見なされ、置き換えられる前に、内容は元のテーブルインデックスにフラッシュされる。

MyISAMキーキャッシュのチューニング

１）key_buffer_sizeサーバーシステム変数
　
・キーキャッシュのサイズを指定。
　
・ゼロの場合はキーキャッシュ無効。（無効にしてもOSによるディスクバッファーは行われる）
　
・MySQL専用のサーバーの場合、トータルメモリーの25～50%をキーキャッシュに割当てるのが大まかな目安。
　
２）キャッシュミスを見積り、チューニング
　
①ステータスを確認
　

mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 12042585 |
| Key_reads         | 76531    |
+-------------------+----------+

　
・Key_read_requests
キャッシュからキーブロックを読んだリクエストの回数
　
・Key_reads
キャッシュ内になく、ディスクからキーブロックを読んだ回数。
　
②キーキャッシュミス率を計算
　
キーキャッシュミス率 = Key_reads/Key_read_requests
　
大まかな目安としてキーキャッシュミス率が0.01が超えないようにする。
　
③キーキャッシュミス率チューニング時の注意
　
・キーキャッシュミス率だけでなく、実際の時間と回数も考慮する。
　
例えば、1日の間の測定値が下記の場合
Key_reads/Key_read_requests = 24 / 1000 = 0.024
キーキャッシュミス率は0.024で目安の0.01より大きいが、実際にキャッシュミスが起きている回数は１時簡に1回だけなので、この程度の利用頻度ではキャッシュミス率が高くてもシステムに影響はない。
　
・MySQLのキーキャッシュが利用されず、Key_readsがカウントされた場合でも、OSによるメモリバッファーの機能もあるので実際にディスクから読み出されたとは限らない。
　
・サーバーリスタート後は、キーバッファーは空の状態なのでリスタート直後はキャッシュミス率は非常に高く、時間の経過とともに安定していく。
　
一回の測定値で判断せず、ある一定時間の差分を使って評価するようにする。
　
mysqladminを使って一定時間毎に差分の測定値を表示する事が出来る。
　
mysqladmin extended-status -ri60 -u user -p | grep Key_reads

MyISAMキーキャッシュアルゴリズムとチューニング

１）LRU
　
・キーキャッシュがフルになった場合、デフォルトではLRU(Least Recently Used)ポリシーでキャッシュから退避させるデータを選択する。
　
あまり使用しないブロックより頻繁に使用されるブロックをキャッシュ内に残すように維持する。
　
・退避されるブロックがディスクから読み出された後に変更されていた場合、ブロックは「dirty」と見なされ、まず最初にディスクに書き戻してから除去される。
　
２）Midpoint Insertion Strategy(MIS)
　
①hotリストとwarmリストの二つの領域を設け、ディスクから読み出されたブロックは最初はwarmリストに配置し、warmリスト内で頻繁にアクセスされたブロックはhotリストの最後尾に移動する。
　
②長期間、hotリストの先頭にとどまっているブロックは、warmリストに移動する。
　
・キーキャッシュのうち、warmリストに割り当てる比率を”key_cache_division_limit”サーバーシステム変数で設定する。
　
デフォルトは100（hotリストは0）でMISが無効の状態。
　
・上記②は、”key_cache_age_threshold”サーバーシステム変数で制御し、この値を小さくすると短期間でwarmリストに戻すことになる。

インデックスのプリロードで性能改善

・MySQL開始直後はキーキャッシュが空で、アクセスを重ねるうちにディスクから読み込まれたデータがキャッシュされていく。
　
インデックスのプリロードを行うと、MySQL開始直後でもキャッシュを利用でき、キャッシュ内に配置されたインデックスブロックは散らばって配置されず、まとまって配置されるのでシーケンシャルなアクセスの場合は速度を向上できる。
　
●インデックスプリロードの実行方法
　
○構文
LOAD INDEX INTO CACHE テーブル名 [IGNORE LEAVES]
　
・”IGNORE LEAVES”を指定するとleafブロックをプリロードの対象から外す事ができる。
　
・インデックスのプリロードに使用するキャッシュのサイズはpreload_buffer_sizeサーバーシステム変数で設定できる。デフォルトは32,768。

マルチキーキャッシュの使用で性能改善

・デフォルトのキーキャッシュでは、バッファーが更新中は待機しなければならず、バッファーがフルの場合は、他のユーザーによってキャッシュから除去されるキーは利用できないという制限がある。
　
マルチキーキャッシュによってこの問題を回避できる。
　
１）マルチキーキャッシュを定義
　
例）”keycache1″という名前で128KBのキーキャッシュを定義
　
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
　
２）マルチキーキャッシュの設定を確認
　
@@GLOBALを使って確認する。
　
①キーキャッシュのサイズ
　

mysql> select @@GLOBAL.keycache1.key_buffer_size;
+------------------------------------+
| @@GLOBAL.keycache1.key_buffer_size |
+------------------------------------+
| 131072                             |
+------------------------------------+

　
②ブロックサイズ
　

mysql> select @@GLOBAL.keycache1.key_cache_block_size;
+-----------------------------------------+
| @@GLOBAL.keycache1.key_cache_block_size |
+-----------------------------------------+
| 1024                                    |
+-----------------------------------------+

　
３）指定したテーブルのインデックスをマルチキーキャッシュに割り当て
　
例）テーブルt1,t2,t3のインデックスをマルチキーキャッシュkeycache1に割り当てる

mysql> CACHE INDEX t1, t2, t3 IN keycache1;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

それ以外のテーブルはデフォルトのキーキャッシュに割り当てられる。
　
４）３つ（hot,cold,warm）のマルチキーキャッシュ使用した例
　
①”hot”キャッシュ
　
・キーキャッシュのメモリー領域の20%を割当て。
・頻繁にアクセスされ、ほとんど更新されないテーブルのインデックス。
　
②”cold”キャッシュ
　
・キーキャッシュのメモリー領域の20%を割当て。
・頻繁に更新されるテーブルのインデックス。
　
③”warm”キャッシュ
　
・キーキャッシュのメモリー領域の60%を割当て。
・デフォルトのキーキャッシュ