sysbench0.4.12测试query_cache_size和query_cache_type
建议：
query_cache_size和query_cache_type生产环境中关闭。
（1）软件包下载地址：
https://dev.mysql.com/downloads/benchmarks.html
（2）安装
tar xzvf sysbench-0.4.12.14.tar.gz
cd sysbench-0.4.12.14
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make && make install
sysbench -v
which sysbench
sysbench --version
（3）测试query_cache=0关闭还是其它值和query_cache_type=off或开启。
以下是第一种情况：
mysql> show variables like "query_cache_size%";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)
mysql> show variables like "query_cache_type%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)
（a）测试三过程prepare（创建sbtest表等环境）、run(正式测试)、clean(清除);
[root@zstedu lib]# /usr/local/bin/sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=andyhsi --oltp-table-size=10000 --db-driver=mysql --num-threads=16 --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password="andyxi" prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 10000 records in table 'sbtest'...
[root@zstedu lib]# /usr/local/bin/sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=andyhsi --oltp-table-size=10000 --db-driver=mysql --num-threads=16 --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password="andyxi" run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 144102
write: 50592
other: 20293
total: 214987
transactions: 10000 (158.16 per sec.)
deadlocks: 293 (4.63 per sec.)
read/write requests: 194694 (3079.30 per sec.)
other operations: 20293 (320.96 per sec.)
General statistics:
total time: 63.2267s
total number of events: 10000
total time taken by event execution: 1008.7897
response time:
min: 9.51ms
avg: 100.88ms
max: 1134.90ms
approx. 95 percentile: 277.95ms
Threads fairness:
events (avg/stddev): 625.0000/5.72
execution time (avg/stddev): 63.0494/0.09
(2)
mysql> show variables like "query_cache_size%";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 0 |
+------------------+---------+
1 row in set (0.00 sec)
mysql> show variables like "query_cache_type%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)
[root@zstedu lib]# /usr/local/bin/sysbench --test=oltp --mysql-table-engine=innodb --mysql-db=andyhsi --oltp-table-size=10000 --db-driver=mysql --num-threads=16 --mysql-socket=/tmp/mysql3306.sock --mysql-user=root --mysql-password="andyxi" run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 143892
write: 50550
other: 20278
total: 214720
transactions: 10000 (162.17 per sec.)
deadlocks: 278 (4.51 per sec.)
read/write requests: 194442 (3153.32 per sec.)
other operations: 20278 (328.85 per sec.)
General statistics:
total time: 61.6626s
total number of events: 10000
total time taken by event execution: 986.1886
response time:
min: 9.31ms
avg: 98.62ms
max: 1305.78ms
approx. 95 percentile: 214.48ms
Threads fairness:
events (avg/stddev): 625.0000/6.88
execution time (avg/stddev): 61.6368/0.01
(3)
mysql> show variables like "query_cache_type%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | ON |
+------------------+-------+
mysql> show variables like "query_cache_size";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 0 |
+------------------+---------+
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 143304
write: 50479
other: 20236
total: 214019
transactions: 10000 (202.88 per sec.)
deadlocks: 236 (4.79 per sec.)
read/write requests: 193783 (3931.51 per sec.)
other operations: 20236 (410.55 per sec.)
General statistics:
total time: 49.2897s
total number of events: 10000
total time taken by event execution: 788.1897
response time:
min: 9.04ms
avg: 78.82ms
max: 227.10ms
approx. 95 percentile: 111.58ms
Threads fairness:
events (avg/stddev): 625.0000/3.74
execution time (avg/stddev): 49.2619/0.02
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
发现 query_cache_type 是off问 是否有必要打开 query_cache_type ， 他的好处或者是否适合搞并发的oltp系统？
这样的很多 ， 基本name名字都不同 这个可以用有cache？？？
MySQL query_cache_type 详解
MySQL设置查询缓存的用意：
把查询到的结果缓存起来，下次再执行相同查询时就可以直接从结果集中取；这样就比重新查一遍要快的多。
查询缓存的最终结果是事与愿违：
之所以查询缓存并没有能起到提升性能的做用，客观上有如下两点原因
1、把SQL语句的hash值作为键，SQL语句的结果集作为值；这样就引起了一个问题如 select user from mysql.user 和 SELECT user FROM mysql.user
这两个将会被当成不同的SQL语句，这个时候就算结果集已经有了，但是一然用不到。
2、当查询所基于的低层表有改动时与这个表有关的查询缓存都会作废、如果对于并发度比较大的系统这个开销是可观的；对于作废结果集这个操作也是要用并发
访问控制的，就是说也会有锁。并发大的时候就会有Waiting for query cache lock 产生。
3、至于用不用还是要看业务模型的。
如果何配置查询缓存：
query_cache_type 这个系统变量控制着查询缓存工能的开启的关闭。
query_cache_type=0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存。
这个参数的设置有点奇怪，1、如果事先查询缓存是关闭的然而用 set @@global.query_cache_type=1; 会报错
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
2、如果事先是打开着的尝试去闭关它，那么这个关闭也是不完全的，这种情况下查询还是会去尝试查找缓存。
最好的关闭查询缓存的办法就是把my.cnf 中的query_cache_type=0然后再重启mysql。
查询缓存相关的系统变量：
have_query_cache 表示这个mysql版本是否支持查询缓存。
query_cache_limit 表示单个结果集所被允许缓存的最大值。
query_cache_min_res_unit 每个被缓存的结果集要占用的最小内存。
query_cache_size 用于查询缓存的内存大小。
如何监控查询缓存的命中率：
Qcache_free_memory 查询缓存目前剩余空间大小。
Qcache_hits 查询缓存的命中次数。
Qcache_inserts 查询缓存插入的次数。
也就是说缓存的命中率为 Qcache_hits/(Qcache_hits+Qcache_inserts)