本当に必要？ MySQLのクエリキャッシュ

1 MySQLのクエリキャッシュとは
2 現在のMySQLとクエリキャッシュ
3 どういうケースで利用するのか
4 クエリキャッシュを設定してみよう

MySQLのクエリキャッシュとは

MySQLのクエリキャッシュは、簡単に言うとクエリとクエリの結果を保持しておくキャッシュで、全く同じクエリが発行された場合はクエリキャッシュから結果を取り出すというもので、全く同じクエリが大量に発行される場合は、パフォーマンス面で非常に効果が高いと言われています。
ただし、データの更新が多い場合はキャッシュがクリアされてしまうので、更新が多いシステムではあまり効果がなくむしろオーバーヘッドが増えてしまうために性能が落ちてしまうこともあり、クエリキャッシュは使わないほうがよいともされています。

現在のMySQLとクエリキャッシュ

2010年くらいまでのWebサービスはブログや記事などが多く、人気ブログなどでは確かに全く同じクエリが大量に発行されるケースも多かったです。ただし、現在のWebサービスはゲームなども含め大量にデータが更新されるケースが多く、全く同じものを同時に多くの人が見るということはあまりないのではないでしょうか。
つまり、昔はquery_cacheを入れると効果的な場合が多かったですが、今では設定を入れない方がベターというのが通例のようです。
そもそもmemcachedを利用すればクエリキャッシュを使う必要もないとも言えるでしょう。
ただし、思いもよらない場所で同じクエリが大量に発行されていたり、うまくmemcached化されていない箇所がある場合に助けてくれるという意味では、あったほうがよいケースもあるようです。

どういうケースで利用するのか

クエリキャッシュを利用しない基準としては、クエリキャッシュヒット率が70%以上であれば設定する意味があると言われています。ただし、40%くらいでも効果があったというケースもあるため、各サービスによっては違いがあります。40%以上であれば効果があるというわけではなく、40%程度でも効果があったケースがあるということです。
クエリキャッシュヒット率はデータベース全体としてのヒット率であるため、更新クエリが多いと全体としては低くなりますが、40%程度のヒット率があれば参照系のクエリが改善される可能性もあるのです。

クエリキャッシュを設定してみよう

MySQLのクエリキャッシュ設定は動的にset globalで変えられるので、色々調整してみるのがいいでしょう。きちんと設計されていないシステムであればあるほどクエリキャッシュの設定の意味はあります。
効果があるというのは何をもって効果があるといえるのかはケースバイケースです。クエリのレスポンスは変わらないけれどCPU使用率やI/OWaitが下がったりすることもあります。

クエリキャッシュ関連のパラメータ設定は以下のような形です。(バージョンによっては多少違います)

query_cache_limit キャッシュしたクエリ結果の最大サイズ(1クエリあたり)(デフォルト1MB)
query_cache_min_res_unit クエリキャッシュの格納されるブロックサイズ(デフォルト4KB)
query_cache_size キャッシュするクエリ結果を格納するメモリ量(デフォルト0：無効)
query_cache_type クエリキャッシュの有効・無効(0:OFF 1:ON 2:DEMAND)
query_cache_wlock_invalidate ONにした時テーブルの書き込みロック獲得時にそのテーブルのクエリキャッシュを無効にする(デフォルトOFF

まずは下記の設定値くらいから設定をしてみましょう。

query_cache_limit=2Mくらい
query_cache_type = 1
query_cache_size = 16Mくらい

一般的なWebサービスだとquerycachesizeを32MB以上にして効果があることはあまりないことが多いようです。Onにして効果があるのは大体4〜32MBくらいですので、初めは大きめの数値に設定して下げていきながら効果を見るのがよいでしょう。## クエリキャッシュヒット率を確認してみよう
次にヒット率を見ていきます。

mysql> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 2329 |
| Qcache_free_memory | 9209472 |
| Qcache_hits | 218608973 |
| Qcache_inserts | 262140809 |
| Qcache_lowmem_prunes | 839915 |
| Qcache_not_cached | 71506151 |
| Qcache_queries_in_cache | 5355 |
| Qcache_total_blocks | 13096 |
+-------------------------+-----------+

ここで表示されるのはMySQLを起動してからの累積値ですので、複数回確認して差分で計算しないと結果はわかりません。
今回はとりあえずもう一度実行して2回分だけで計算してみましょう。

mysql> show global status like 'Qcache%'; 2回目
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 2986 |
| Qcache_free_memory | 11520448 |
| Qcache_hits | 218617556 |
| Qcache_inserts | 262148859 |
| Qcache_lowmem_prunes | 839915 |
| Qcache_not_cached | 71508340 |
| Qcache_queries_in_cache | 4471 |
| Qcache_total_blocks | 11962 |
+-------------------------+-----------+

それぞれの数値の意味は以下の通りとなります。

Qcachefreeblocks クエリキャッシュ内の空きメモリブロック数
Qcachefreememory クエリキャッシュの空きメモリ量
Qcachehits キャッシュヒット数
Qcacheinserts キャッシュに追加されたクエリ数
Qcachelowmemprunes メモリ不足のためにキャッシュから削除されたクエリ数
Qcachenotcached キャッシュされていない（キャッシュ不可、または QUERYCACHETYPE により）クエリ数
Qcachequeriesincache キャッシュに登録されているクエリ数
Qcachetotalblocks クエリキャッシュ内の合計ブロック数実際にselectが発行された数がQcacheinserts+Qcachenotcachedとなります。Qcache_hitsはキャッシュから返している数値です。

クエリキャッシュヒット率の計算式は

Qcache_hits/(Qchache_hits+Qchache_inserts+Qchache_not_cached)*100

となるので、上記の場合は

(218617556- 218608973)/((218617556- 218608973)+(262148859-262140809)+(71508340-71506151))*100=45.60089257

となります。
この場合45%なのでそれほど高くもないし、空きメモリも多いのでもう少しクエリキャッシュサイズを下げてみてヒット率やパフォーマンスが上がるか様子を見てみて、あまり変わらないようであればOFFにしたほうがよいかもしれません。
(※実際はクエリの実行時間とサーバの負荷を見ながらどれがいいか検討していくことになります。)

とはいえ、今までやってきた中で大体はクエリキャッシュのチューニングで変化がない、または悪くなる場合が9割です。1割くらいの確率で効果が出ることがあります。