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

■ 目次

  1. 概要
  2. 設定確認
  3. 設定方法
  4. 検証
  5. キャッシュについて
  6. 参考

■ 概要

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に設定する

設定項目 概要 設定例
query_cache_size 実行したクエリのSQLとその結果をキャッシュしておくための保存用メモリサイズ
この値を0にするとクエリキャッシュは無効になる
16M
query_cache_type クエリキャッシュを行う条件を指定する
0またはOFF:キャッシュしない
1またはON:「SELECT SQL_NO_CACHE」を除く全ての結果をキャッシュする
2またはDEMAND:「SELECT SQL_CACHE」で始まるクエリだけキャッシュする
1

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」サイズを増やすとパフォーマンスが改善する可能性がある

■ 参考

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


Be First to Comment

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です