Mysql query Cache研究三

QueryCache 在哪些地方节省了开销，以及如何设置QueryCache的值，又如何来判断QueryCache带来的性能问题呢？
进行一些测试来从应用层面观察QC的变化：
检查是否开启了QC
mysql> 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_type            | ON       | 
| query_cache_wlock_invalidate| OFF      | 
+------------------------------+----------+

可以看到query cache是开启了的。
mysql> show status like '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 5     | 
+---------------+-------+
1 row in set (0.00 sec)
执行查询，对比统计信息的变化.执行查询之后:
mysql> select id,account from account where id= '19922730';
+----------+-----------+
| id       | account   |
+----------+-----------+
| 19922730 | 694595671 | 
+----------+-----------+
1 row in set (0.00 sec)
mysql> show status like '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 6    | 
+---------------+-------+
1 row in set (0.00 sec)

select的统计变成了6.发现Com_select增加了

第二次在运行该查询 :
mysql> select id,account from account where id= '19922730';
+----------+-----------+
| id       | account   |
+----------+-----------+
| 19922730 | 694595671 | 
+----------+-----------+
1 row in set (0.00 sec)
mysql> show status like '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 6     | 
+---------------+-------+
1 row in set (0.00 sec)
可以看到com_select并没有起变化，这是因为第二次使用了QC,并没有parse，execution，explan等操作，没有进行统计。
同样我们来看看QC相关的状态变化(第一次执行之前):

执行之前:
mysql> show status like '%QC%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16762264 | 
| Qcache_hits             | 30       | 
| Qcache_inserts          | 6        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 52826    | 
| Qcache_queries_in_cache | 5        | 
| Qcache_total_blocks     | 13       | 
+-------------------------+----------+
8 rows in set (0.00 sec)
执行之后:
mysql> select min(id) from account;
+---------+
| min(id) |
+---------+
|       1 | 
+---------+
1 row in set (0.01 sec)
mysql> show status like '%QC%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16761240 | 
| Qcache_hits             | 30       | 
| Qcache_inserts          | 7        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 52827    | 
| Qcache_queries_in_cache | 6        | 
| Qcache_total_blocks     | 15       | 
+-------------------------+----------+
8 rows in set (0.00 sec)

可以看到在使用了QC之后，第一次会导致该query插入QueryCache.统计参数Qcache_inserts +1/Qcache_queries_in_cache+1.

第二次执行：
mysql> select min(id) from account;
+---------+
| min(id) |
+---------+
|       1 | 
+---------+
1 row in set (0.00 sec)
mysql> show status like '%QC%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16761240 | 
| Qcache_hits             | 31       | 
| Qcache_inserts          | 7        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 52868    | 
| Qcache_queries_in_cache | 6        | 
| Qcache_total_blocks     | 15       | 
+-------------------------+----------+
8 rows in set (0.00 sec)

可以看到第二次命中，所以Qcache_hits +1.Qcache_inserts 和Qcache_queries_in_cache 并没有增加.
通过profile来跟踪发生的变化：
检查profiling是否打开
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   | 
+---------------+-------+
1 row in set (0.00 sec)
默认没有打开，打开profiling:
mysql> set session profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    | 
+---------------+-------+
1 row in set (0.00 sec)
mysql> select id,account from account order by account limit 1;

mysql> select id,account from account order by account limit 1;
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration   | Query                                                   |
+----------+------------+---------------------------------------------------------+
|        1 | 0.00017200 | show variables like 'profiling'                         | 
|        2 | 0.00016600 | show create table account                               | 
|        3 | 0.01087900 | select id,account from account order by account limit 1 | 
|        4 | 0.00006800 | select id,account from account order by account limit 1 | 
+----------+------------+---------------------------------------------------------+
4 rows in set (0.00 sec)
第一次查询的profile:
mysql> show profile for query 3;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000008 | 
| checking query cache for query | 0.000033 | 
| Opening tables                 | 0.000006 | 
| System lock                    | 0.000004 | 
| Table lock                     | 0.000011 | 
| init                           | 0.000014 | 
| optimizing                     | 0.000004 | 
| statistics                     | 0.000007 | 
| preparing                      | 0.000007 | 
| executing                      | 0.000002 | 
| Sorting result                 | 0.000003 | 
| Sending data                   | 0.010735 | 
| end                            | 0.000006 | 
| query end                      | 0.000005 | 
| storing result in query cache  | 0.000007 | 
| freeing items                  | 0.000004 | 
| closing tables                 | 0.000003 | 
| logging slow query             | 0.00002  | 
+--------------------------------+----------+
18 rows in set (0.01 sec)
 第二次查询的profile：
mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000003 | 
| checking query cache for query | 0.000004 | 
| checking privileges on cached  | 0.000003 | 
| sending cached result to clien | 0.000056 | 
| logging slow query             | 0.000002 | 
+--------------------------------+----------+
5 rows in set (0.00 sec)

可以看到使用了QC之后整个步骤少了很多，包括opening tables,system lock等等,带了性能的提升.但是不是QueryCache size的值设置的越大就越好呢？
我们知道在QueryCache的使用过程之中，有一个全局的锁需要维护，只有拿到了这个lock，才能继续下面的操作，否则跳出QC,执行普通的parse,optimize,execute等操作,由于这个lock是全局的，所以就存在lock争用问题，同时QC分配的越大，与之来讲维护QC的lock所需的成本就越高，当维护成本高于所带来的性能提升的时候，可能增加QC之后带来适得其反的作用。通常在什么情况下会引起lock contention呢？
1.并发select比较大.当并发select比较大的情况，都会争用lock来进入QC检查。所以对查询很大的系统不适合使用QC.
2.dml/ddl较多的情况，这种情况可能会导致在QC中，大量与该表相关的query无效，在这个过程中会获得该lock，从而串行的进行这些操作。这时候其他的查询或者dml/ddl等需要获得lock的操作会被阻塞。

从中我们也看到mysql的QC两个非常重要的缺点:
1.对于并发不大或者串行的应用，可能QC开启会很有用，也就是在锁的处理上，对于并发有一定的缺陷。
2.可能很多情况下，我们只对一些表进行了改动，但这些表实际的数据并没有变化，或者少部分表的数据改变，而大多数并没有变化，但QC的机制中，会将所有与这小表相关的query都无效。


如何合理的设置QC的值呢？我们可以遵循下面的这些原则：
1.query_cache_size设置一个较小的值，然后不断的增大该值，每次测试一段时间，看看系统的性能是否有实质性的提高，找到一个最合适的值。
2.观察Qcache_free_blocks的变化，如果该值持续增大，可能造成了过多的碎片，此时可能需要flush query cache整理碎片，同时增加query_cache_min_res_unit
3.根据应用的需求，判断需要query cache的sql所返回的结果的最大值，设置query_cache_limit，以免设置的过大，导致select * from table 操作大量的数据,刷掉query cache里面的数据。
4.如果lowmem_prunes比较低而free_memory 很大，可以适当减少query_cache 。否则增加query_cache 。
5.根具hit的，insert，prune的比例进行合适的调整.
                  Hit rate = Qcache_hits / (Qcache_hits + Com_select)
                          Insert rate = Qcache_inserts / (Qcache_hits + Com_select)
                          Prune rate = Qcache_lowmem_prunes / Qcache_inserts

具体的设计还是要更具应用的需求，与测试测试过程中的结果，以及日后的监控暴露出来的问题来做合理的设置。
参考 :
         http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
         https://blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing
         %E4%B8%AD%E5%AF%B9query-cache%E5%A4%84%E7%90%86%E7%9A%84%E6%BA%90%E7%A0%81%E5%88%86%E6%9E%90.html
         http://dev.mysql.com/doc/refman/5.1/en/query-cache.html