query cacheがある時、ない時

query cacheが有効でないワークロードでquery cacheがあるとどうなるかという話
DB: MariaDB 10.0.11
OS: Ubuntu 14.04

#テーブル、データ、テスト

CREATE TABLE event_test(
 id    	     BIGINT	AUTO_INCREMENT,
 event_id    INT	NOT NULL	DEFAULT 0,
 user_id     INT	NOT NULL	DEFAULT 0,
 status	     INT	NOT NULL	DEFAULT 0,
 create_time DATETIME	NOT NULL,
 update_time DATETIME	NOT NULL,
 delete_time DATETIME	NOT NULL,
 PRIMARY KEY (id),
 KEY (user_id,event_id)
)ENGINE=InnoDB	DEFAULT CHARSET=utf8;

データを3500万行くらい入れた上記のテーブルに対して適当に検索を繰り返す

MariaDB [my_test]> select count(*) from event_test;
+----------+
| count(*) |
+----------+
| 34623542 |
+----------+
1 row in set (5.07 sec)

テストはsysbenchのselect.luaのevent関数を以下のようにちょっと変更して実行
4スレッドで5分間軽い検索をし続ける

function event(thread_id)
   local eid
   eid = sb_rand(0, 1000000)%15
   rs = db_query("SELECT * FROM event_test WHERE event_id=" .. eid .. " AND user_id=" .. sb_rand(0,3000000))
end

./bin/sysbench --test=/tests/db/my_select_test.lua --db-driver=mysql --mysql-user=user --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=my_test --mysql-table-engine=innodb --num-threads=4 --max-requests=0 --max-time=300 run

#結果1

テスト実行中にプロセスリストを見ているとWaiting for query cache lockの状態に結構なっているように見える
そのせいかcpu使用率もあまり高くない
以下、実行結果

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            3157862
        write:                           0
        other:                           0
        total:                           3157862
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 3157862 (10526.19 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0004s
    total number of events:              3157862
    total time taken by event execution: 1195.9937s
    response time:
         min:                                  0.02ms
         avg:                                  0.38ms
         max:                                 11.40ms
         approx.  95 percentile:               0.59ms

Threads fairness:
    events (avg/stddev):           789465.5000/614.61
    execution time (avg/stddev):   298.9984/0.00

#クエリキャッシュを試しに切って再度試してみた

クエリキャッシュはquery_cache_sizeを0にすれば切ることが出来る

MariaDB [my_test]> SET @@global.query_cache_size=0;
Query OK, 0 rows affected (0.01 sec)

#結果2

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            9999853
        write:                           0
        other:                           0
        total:                           9999853
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 9999853 (33332.83 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0001s
    total number of events:              9999853
    total time taken by event execution: 1188.3766s
    response time:
         min:                                  0.07ms
         avg:                                  0.12ms
         max:                                 17.82ms
         approx.  95 percentile:               0.14ms

Threads fairness:
    events (avg/stddev):           2499963.2500/4806.77
    execution time (avg/stddev):   297.0942/0.02

リクエスト処理数が3倍になってる
軽い検索がメインの場合や更新頻度が極端に高い場合などはクエリキャッシュを切ってしまうのがいいかもしれない
その場合は重い検索処理をmemcacheに移すなどの考慮が必要になる