MySQL クエリーキャッシュ 【チューニング方法とかも】

参照メインのWordPressさんでRDS(for MySQL)のチューニングを行った。
以前に参照メインのWordpressの場合はMySQLのクエリキャッシュを有効にすると幸せになるかもしれない
でクエリーキャッシュを導入したのですが、今回はどの程度の割合でクエリーキャッシュを入れると
よいパフォーマンスになるのか？を調べてみました。

環境

• wordpress 4.5.3
• RDS for MySQL 5.6.29

参考情報

• MySQL クエリーキャッシュ
• サーバーステータス変数#statvar_Qcache_free_blocks
• MySQL クエリーのキャッシュヒット率をSQLで求める方法

クエリキャッシュとはなんぞや???

MySQLリファレンスより

クエリーキャッシュには、クライアントに送信された対応する結果とともに、SELECT ステートメントのテキストが格納されます。あとで同じステートメントを受け取った場合、サーバーはそのステートメントを再度解析して実行する代わりに、クエリーキャッシュから結果を取得します。クエリーキャッシュはセッション間で共有されるため、1 つのクライアントで生成された結果セットを、別のクライアントによって発行された同じクエリーへの応答で送信できます。

クエリーキャッシュは、あまり頻繁に変更されないテーブルがあり、それに対してサーバーが多くの同一のクエリーを受け取る環境で役立つことがあります。これは、データベースの内容に基づいて、多くの動的ページを生成する多くの Web サーバーに一般的な状況です。

クエリーキャッシュは古くなったデータを返しません。テーブルが変更されると、クエリーキャッシュ内の関連エントリがフラッシュされます。

簡単に説明すると

• 同じSELECT文をMySQL内のメモリでキャッシュ
  • select * from TABLE と select * from table は別々のキャッシュに確保されるので注意
• UPDATE,INSERT等のデータ更新時にはMySQL内で自動にキャッシュ更新をかける

とのことです。

めっちゃいいじゃん!!って思うかもしれませんが、

MySQLリファレンスより

クエリーキャッシュは、かなりのパフォーマンスの改善の可能性を提供しますが、すべての環境でそうなるものと想定しないでください。クエリーキャッシュの構成やサーバーのワークロードによっては、実際にパフォーマンスの低下が見られることもあります。

とのこと。

調べている感じだと

• INSERTが多いシステムの場合はキャッシュ保守のオーバーヘッドが増えて、逆にパフォーマンスが落ちる可能性がある
• 参照メインなら基本パフォーマンスがあがるよー

とのこと

参照メインのWordPressのサイトに入れて効果はわかっていたので、今回はチューニングに焦点をあてて調査をしました。

クエリーキャッシュを調査するために知っておくといいもの

今回は下記の状態で設定をかけています

RDS MySQL m4.xlarge

• メモリ 16G
• query_cache_size に割り当てたメモリ 1G
• query_cache_limit 16M

他のMySQLの設定値はRDSのデフォルトを使用しています。

サーバーステータス変数 query_cache_xxx

クエリーキャッシュの設定一覧

SHOW VARIABLES LIKE '%query_cache_%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| query_cache_limit            | 16777216   |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 1073741824 |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
+------------------------------+------------+
5 rows in set (0.00 sec)

変数	説明
query_cache_limit	クエリーキャッシュに格納する上限値(バイト)このバイト数より大きい結果をキャッシュしません。 デフォルト値は 1M バイトです。
query_cache_min_res_unit	クエリーキャッシュによって割り当てられたブロックの最小サイズ (バイト単位)。query_cache_min_res_unit のデフォルト値は 4K バイトです。ほとんどの場合、これで十分であるはずです。	参考 クエリーキャッシュの構成
query_cache_size	クエリーキャッシュに使用するメモリサイズ。大きれば大きいほど良いとわけでもないらしい。	参考 https://dev.mysql.com/doc/refman/5.6/ja/query-cache.html クエリーキャッシュのサイズを過度に大きくすると、キャッシュの保守に必要なオーバーヘッドが増え、それを有効にすることのメリットに勝る可能性があります。数十メガバイトのサイズが通常は有益です。数百メガバイトのサイズはそうでない可能性があります。
query_cache_type	クエリーキャッシュタイプの設定値クエリーキャッシュを利用したい場合は1 or 2	詳しくは https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_query_cache_type

QCache_xxx

クエリーキャッシュの状態を示す一覧

show global status like "%QCache%";
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 1525       |
| Qcache_free_memory      | 1066602576 |
| Qcache_hits             | 3800550    |
| Qcache_inserts          | 3649965    |
| Qcache_lowmem_prunes    | 1327795    |
| Qcache_not_cached       | 551696     |
| Qcache_queries_in_cache | 1880       |
| Qcache_total_blocks     | 5421       |
+-------------------------+------------+
8 rows in set (0.00 sec)

変数	説明
Qcache_free_blocks	クエリーキャッシュ内の空きメモリーブロックの数。	このブロックはSQL文に1ブロック,SQLの結果に1ブロックが最低割当られる。(最低なので2ブロックで構成されるとは限らない)
Qcache_free_memory	クエリーキャッシュの使用できるメモリーの残り
Qcache_hits	クエリーキャッシュヒットの数	RESET QUERY_CACHE を実行すると0になる
Qcache_inserts	クエリーキャッシュに追加されるクエリーの数。	RESET QUERY_CACHE を実行すると0になる
Qcache_lowmem_prunes	メモリーが少ないためクエリーキャッシュから削除されたクエリーの数。
Qcache_not_cached	非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされない)。
Qcache_queries_in_cache	クエリーキャッシュ内に登録されたクエリーの数。
Qcache_total_blocks	クエリーキャッシュ内のブロックの合計数。

クエリーキャッシュメモリが足りているか確認する

前述の項目からすると

Qcache_free_memoryのを見れば良い
この値が少ない場合はquery_cache_sizeの量を大きくするといいかもしれない

キャッシュヒットレート

SELECT文発行量の内クエリーキャッシュを使用して返却した割合
下記のSQLで求められます

SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE)    FROM INFORMATION_SCHEMA.GLOBAL_STATUS   WHERE VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;

+-------------------+
| CACHE_HIT_RATE    |
+-------------------+
| 47.48295949635104 |
+-------------------+
1 row in set (0.00 sec)

47%なので半分位はクエリーキャッシュで返している状態です。

削除クエリーの割合

• Qcache_lowmem_prunes
• Qcache_inserts

からわかります

SELECT (
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_LOWMEM_PRUNES') /
(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_INSERTS') * 100
) AS "削除クエリの割合";

+--------------------------+
| 削除クエリの割合         |
+--------------------------+
|        35.52166862048172 |
+--------------------------+
1 row in set (0.00 sec)

クエリーキャッシュに乗っているSQLのうち35%はメモリが足りない、更新をしたなどの理由により削除されているみたいです。

チューニングのポイント

チューニングをするに当たってのポイントです。

サーバーのメモリに足りるようにクエリーキャッシュに割り当てる

当たり前のことですが、サーバーのメモリ以上に割り当てるとメモリ不足に陥ってパフォーマンスは下がって死にます。
INDEXなりでメモリを確保していると思うので、現在使っているメモリから余剰分などをみて適切なサイズを割り当ててください。

• query_cache_limit 2M - 4M
• query_cache_size 256m - 512m

で設定している人が多いみたいです
(サーバースペックによって割当比率がかわってくるので一概に言えない)

query_cache_limitでクエリーキャッシュに保存する大きさを決める

単発の小さいクエリーが多い場合この値は小さくても問題はありませんが、動いているアプリケーションのSQLが一覧系でデータfetchの内容が大きい場合はquery_cache_limitを大きくするといいです。
ちなみにwordpressの場合はpluginのロード判断でpluginの確認SQLが大量に発行されるので

query_cache_limitを1Mぐらいに設定してもpluginロード確認SQLがクエリーキャッシュに乗るためだいぶパフォーマンスが上がります。

クエリーキャッシュのメモリが足りているか？

Qcache_lowmem_prunesがメモリーが足らなくて削除された数です。
この数が多ければ多いほどメモリーがたらない状態を指しています。
すべてのクエリーキャッシュに乗っている場合は0になります。
この数が多い場合は余剰メモリーと相談しながら、
query_cache_sizeの量を多くしてください

また多く割り当てたからといってパフォーマンスが必ずしもよくなるとは限りません。

参考 https://dev.mysql.com/doc/refman/5.6/ja/query-cache.html

クエリーキャッシュのサイズを過度に大きくすると、キャッシュの保守に必要なオーバーヘッドが増え、それを有効にすることのメリットに勝る可能性があります。数十メガバイトのサイズが通常は有益です。数百メガバイトのサイズはそうでない可能性があります。

最後に

色々と調査するにあたりMySQLのドキュメントを読むのが一番早かったな。。と
でもMySQLのドキュメント読みづらいだもん。。。。