MySQL　キャッシュチューニング：パフォーマンス改善フレームワーク 【MyISAM編】

　キャッシュの設定はストレージエンジンによって項目が異なるため、今回はMyISAMに絞り解説をしていきます。一般的には更新やリカバリに強く、トランザクションもサポートしているInnoDBがメジャーですが、昨今のワードプレスの広まりもあり、MyISAMを使用する機会も多いかと思います（ワードプレスではMyISAMをデフォルトのストレージエンジンとして使用しています）。使用するMySQLのバージョンやOSの環境等は以下のものとして解説していきます。

【環境】
MySQLバージョン：5.1.71
OS : Red Hat 4.4.7-4
搭載メモリ : 8GB
CPU : 仮想6コア
設定ファイル保存ディレクトリ : /etc/my.cnf

チューニング項目

１．インデックスバッファ(key_buffer_size)
２．テーブルキャッシュ(table_open_cache)
３．通信バッファ(net_buffer,max_allowed_packet)
４．クエリキャッシュ(query_cache_size)
５．スレッドキャッシュ(thread_cache,max_connections)

１．インデックスバッファ(key_buffer_size)

【設定例】
[mysqld]
key_buffer_size=16M

【一般的な値】
　搭載メモリの25%程度　参照：4.2.3. システム変数
　※最大値は4GB
　※搭載メモリの50%だとシステム処理が遅くなる場合がある。

【解説】
　key_buffer_sizeはインデックスデータを読み込むときに使用されるバッファです。MyISAMでは各テーブルにつき拡張子がMYI, MYD, frmという3つのファイルが作成されます。MYIにはテーブルのインデックスデータが、MYDには格納されているデータが、frmにはテーブルのフォーマット情報が格納されています。key_buffer_sizeにはインデックスデータが格納されているMYIファイルをキャッシュするサイズを指定します。

２．テーブルキャッシュ(table_open_cache)

【設定例】
table_open_cache=1024

【一般的な値】
同時接続数×N
N：結合に使用するテーブル数の最大値

【解説】
　table_open_cacheは1つのスレッドに対しキャッシュするテーブル数を指定します。MyISAMではテーブル1つにつき3つのファイル（MYI、MYD、frm）が作成されるため、頻繁にファイルへアクセスします。毎回3つのファイルをopenしてからアクセスするのは時間がかかるため、テーブルキャッシュにファイルディスクリプタをキャッシュすることでファイルオープンのオーバーヘッドを削減します。

　また、table_open_cacheはバージョン5.0までtable_cacheという名前でした。バージョンによって名称が異なることに注意しましょう。

３．通信バッファ(net_buffer_length, max_allowed_packet)

【設定例】
net_buffer_length=1000
max_allowed_packet=10M

【一般的な値】
net_buffer_length : 通常は変更しない。最大は1MB。
最大は1MB。
max_allowed_packet : 最大のBLOG型データより大きい値。最大は1GB。

【解説】
　net_buffer_lengthとmax_allowed_packetは通信バッファのサイズに関するものです。初期状態でnet_buffer_lengthのサイズが確保され、データのサイズに応じてmax_allowed_packetの値まで拡大します。もしもmax_allowed_packetの値より大きい値を送信しようとするとエラーになるため、この値は少なくともBLOB型データより大きくする必要があります。

４．クエリキャッシュ(query_cache_size)

【設定例】
query_cache_size=16M

【一般的な値】
使用するSQLクエリやヒット率によって異なる。チューニングの仕方は以下参照。
※最低40kBは設定する
※デフォルトは0であり、0はクエリキャッシュを無効化する。

【解説】
　クエリキャッシュのサイズを決定します。クエリキャッシュとは一度発行されたSQLクエリの結果をキャッシュしておき、再度同じSQLクエリが発行された場合にキャッシュから結果を返す仕組みです。指定値は1024の倍数である必要があり、最低40kB、256MBあれば十分であるといわれています。

　クエリキャッシュの最適値は環境によって異なるため、以下のステータスを随時確認しながら最適値を探す必要があります。

【クエリキャッシュの有効/無効確認コマンド】
mysql> show variables like ‘have_query_cache’;
結果

【クエリキャッシュのステータス確認コマンド】
mysql> show status like ‘Qcache%’;
結果

Qcache_free_blocks : クエリキャッシュの空きメモリブロック数。
Qcache_free_memory : クエリキャッシュ内の空きメモリブロック量。これが不足している場合はクエリキャッシュのサイズを増やす必要がある。
Qcache_hits : キャッシュのヒット数。キャッシュのヒット数は多ければ多いほど高率が良いことを示す。ヒット率が低い場合は、SQLクエリの見直しを行う必要があります。例えば、大文字小文字は別のSQLクエリとして認識されるため、どちらかに統一する必要がある。
Qcache_inserts : キャッシュに追加されたクエリ数
Qcache_lowmem_prunes : メモリ不足のためにキャッシュから削除されたクエリ数。この値が大きい場合はクエリキャッシュが足りていないため、クエリキャッシュサイズを増やす。
Qcache_not_cached : キャッシュされていないクエリ数
Qcache_queries_in_cache : キャッシュに登録されているクエリ数
Qcache_total_blocks : クエリキャッシュ内の合計ブロック数

【クエリキャッシュの最適値】

　クエリキャッシュの最適値は上のステータスを見ながら模索していきます。最適値を見つけるのは簡単ではありませんがいくつかコツがあります。まずQcache_free_blocksの空きが少ない場合はキャッシュが足りていないためクエリキャッシュサイズを増やします。ヒット率が芳しくなかい場合はSQLクエリを見直し、Qcache_lowmem_prunesが高い場合は削除されたクエリ数が多いことを示しているため、クエリキャッシュサイズを増やします。

　MyISAMは更新頻度の低いアプリケーションに使用されることが多いため、多くのSQLクエリはSELECT文となるでしょう。同じSQLクエリを使用すると、キャッシュされやすくヒット率(Qcache_hits)向上に繋がります。もし、ヒット率が芳しくない場合は、クエリキャッシュを無効化することも一つの戦略です。クエリキャッシュの更新にもオーバーヘッドがかかるため、多少なりともパフォーマンスに影響が出ます。上記のステータスを確認しながら有効・無効も含めチューニングしていきます。

５．スレッドキャッシュ(thread_cache, max_connections)

【設定例】
max_connections=500
thread_cache_size=500

【一般的な値】
max_connections : 予想される最大同時クライアント数
thread_cache_size : max_connectionsと同じ値

【解説】
max_connections : は同時接続できるクライアントの数を設定します。デフォルト値は100です。

thread_cache_size : MySQLはクライアント毎にスレッドが生成され、1クライアント毎に1スレッドが割り当てられます。一度生成されたスレッドは『スレッドキャッシュ』に保存され、次の接続に備えます。スレッドキャッシュを利用することでスレッドの生成・破棄といった処理を減らすことができるため、レスポンスの処理速度が向上します。