MySQLのクエリキャッシュについて

概要

MySQLのクエリキャッシュについて調べる

SELECTでの参照が来た際にまったく同じクエリを受け取るとSQLを実行するのではなくキャッシュから返すので高速に動作する
同じSELECTクエリが投げられて、同じ結果を返すことが多いサーバには向いているかも

設定確認

// 値がYESならばキャッシュすることになっている
mysql > show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.04 sec)

// キャッシュについてのステータス
> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16746288 |
| Qcache_hits             | 3304     |
| Qcache_inserts          | 7        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 41648    |
| Qcache_queries_in_cache | 7        |
| Qcache_total_blocks     | 16       |
+-------------------------+----------+
8 rows in set (0.05 sec)

// クエリキャッシュについての設定
> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | DEMAND   |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.00 sec)

設定方法

この辺りをmy.cnfに設定する

my.cnf修正例

// 修正する箇所 (この例ではすでに記述)
$ egrep -n 'query_cache_type|query_cache_size' /etc/my.cnf
697:query_cache_type = 0
705:query_cache_size = 256M

// 書き換える前にバックアップを取得(my.cnf.yyyymmddが作成される)
$ sudo cp -p /etc/my.cnf{,.`date +%Y%m%d`}

// 修正する
$ sudo vim /etc/my.cnf

// mysql restart
$ sudo service mysqld restart

検証

0.3秒かかっているものがクエリキャッシュを設定したことにより(2回目以降のアクセスは)0.00秒と早くなった

// 変更前
mysql > SELECT hoge FROM table WHERE id = 0;
-- 出力は省略 --
3 rows in set (0.34 sec)

// 変更 (検証するだけなのにmy.cnf書き換えてmysql restartするのは面倒なのでオンラインで実行する)
mysql > SET SESSION query_cache_type = 'ON';
mysql > SET GLOBAL query_cache_type = 'ON';
mysql > SET GLOBAL query_cache_size = 16777216;


// 設定されていることを確認
mysql > show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
... 中略 ...
+------------------------------+----------+
7 rows in set (0.00 sec)

// 1回目のアクセスはキャッシュにないので遅い
mysql > SELECT hoge FROM table WHERE id = 0;
-- 出力は省略 --
3 rows in set (0.32 sec)

// 2回目のアクセスからはキャッシュにのっているため早い
mysql > SELECT hoge FROM table WHERE id = 0;
-- 出力は省略 --
3 rows in set (0.00 sec)

mysqlslapを使ってのテスト
参考：mysqlslapを使って負荷検証を行う

// 並列5でそれぞれ10回selectさせる
$ mysqlslap -u user -p --delimiter=";" --query="SELECT hoge FROM table WHERE id = 0;" --number-of-queries=50 --concurrency=5
Benchmark
    Average number of seconds to run all queries: 3.631 seconds
    Minimum number of seconds to run all queries: 3.631 seconds
    Maximum number of seconds to run all queries: 3.631 seconds
    Number of clients running queries: 5
    Average number of queries per client: 10

// クエリキャッシュ設定後
Benchmark
    Average number of seconds to run all queries: 0.002 seconds
    Minimum number of seconds to run all queries: 0.002 seconds
    Maximum number of seconds to run all queries: 0.002 seconds
    Number of clients running queries: 5
    Average number of queries per client: 10

結論：はやい (同じクエリで同じ実行結果の時に限る)

キャッシュについて

テーブルキャッシュはファイルのオープンオーバーヘッドをできるだけすくなくするための仕組み

スレッドごとにデータファイルがオープンされコネクション数を増やした時やテーブルが増えた時に「table_cache」サイズを増やすとパフォーマンスが改善する可能性がある

参考

参考一つ目のサイトでキャッシュヒット率を計算することができる

• MySQLキャッシュヒット率計算ツール
• MySQL/最適化/サーバパラメタ