mysql性能调试query cache

发布时间：2017-06-24 10:52:47编辑：丝画阁阅读（695）

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   | 1m
| query_cache_min_res_unit     | 4096      |  4k
| query_cache_size             | 268435456 | 256M
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

6 rows in set (0.01 sec)

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 5242880   | 5m
| query_cache_min_res_unit     | 131072    | 128k
| query_cache_size             | 536870912 | 512M
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.02 sec)

• have_query_cache 表示是否支持query cache；
• query_cache_limit 表示query cache存放的单条query最大结果集，默认值为1M，结果集大小超过该值的query不会被cache；
• query_cache_min_res_unit 表示query cache 每个结果集存放的最小内存大小，默认4k；
• query_cache_size 表示系统中用于query cache的内存大小；
• query_cache_type 系统是否打开了query cache功能；
• query_cache_wlock_invalidate 针对myisam存储引擎，设置当有write lock在某个table上面的时候，读请求是要等待write lock释放资源之后再查询还是允许直接从query cache中读取结果，默认是OFF，可以直接从query cache中取得结果。

query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache。

mysql> show status like '%Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 3093      |
| Qcache_free_memory      | 202504904 |
| Qcache_hits             | 235876    |
| Qcache_inserts          | 74794     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 2359      |
| Qcache_queries_in_cache | 22767     |
| Qcache_total_blocks     | 49066     |
+-------------------------+-----------+
8 rows in set (0.00 sec)

• Qcache_free_blocks 表示query cache中目前还有多杀剩余的blocks，如果该值显示较大，则说明query cache中的内存碎片较多，需要进行整理了；

减少碎片：
合适的query_cache_min_res_unit可以减少碎片，这个参数最合适的大小和应用程序查询结果的平均大小直接相关，可以通过内存实际消耗（query_cache_size - Qcache_free_memory）除以Qcache_queries_in_cache计算平均缓存大小。
可以通过Qcache_free_blocks来观察碎片，这个值反应了剩余的空闲块，如果这个值很多，但是
Qcache_lowmem_prunes却不断增加，则说明碎片太多了。可以使用flush query cache整理碎片，重新排序，但不会清空，清空命令是reset query cache。整理碎片期间，查询缓存无法被访问，可能导致服务器僵死一段时间，所以查询缓存不宜太大。

Qcache_free_blocks代表内存自由块的多少，反映了内存碎片的情况

1）当查询进行的时候，Mysql把查询结果保存在qurey cache中，但如果要保存的结果比较大，超过query_cache_min_res_unit的值 ，这时候mysql将一边检索结果，一边进行保存结果，所以，有时候并不是把所有结果全部得到后再进行一次性保存，而是每次分配一块 query_cache_min_res_unit 大小的内存空间保存结果集，使用完后，接着再分配一个这样的块，如果还不不够，接着再分配一个块，依此类推，也就是说，有可能在一次查询中，mysql要 进行多次内存分配的操作。
2）内存碎片的产生。当一块分配的内存没有完全使用时，MySQL会把这块内存Trim掉，把没有使用的那部分归还以重 复利用。比如，第一次分配4KB,只用了3KB，剩1KB，第二次连续操作，分配4KB，用了2KB，剩2KB，这两次连续操作共剩下的 1KB+2KB=3KB，不足以做个一个内存单元分配， 这时候，内存碎片便产生了。
3）使用flush query cache，可以消除碎片4）如果Qcache_free_blocks值过大，可能是query_cache_min_res_unit值过大，应该调小些
5）query_cache_min_res_unit的估计值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

• Qcache_free_memory 表示query cache目前剩余的内存大小；
• Qcache_hits 表示query cache有多少次命中；
• Qcache_inserts 表示未命中cache后将结果集再写入到cache中的次数；
• Qcache_lowmem_prunes 表示多少条query因为内存不足而被清除出query_cache；
• Qcache_not_cached 表示因为query_cache_type的设置或者不能被cache的query的数量；
• Qcache_queries_in_cache 表示当前cache的query的数量；
• Qcache_total_blocks 当前query cache中的block数量。

提高查询缓存的使用率：
如果碎片不是问题，命中率却非常低，可能是内存不足，可以通过 Qcache_free_memory 参数来查看没有使用的内存。
如果2者都没有问题，命中率依然很低，那么说明缓存不适合你的当前系统。可以通过设置
query_cache_size = 0或者query_cache_type 来关闭查询缓存。

检查查询缓存使用情况

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率

当服务器收到SELECT 语句的时候，Qcache_hits 和Com_select 这两个变量会根据查询缓存

的情况进行递增

查询缓存命中率的计算公式是：Qcache_hits/(Qcache_hits + Com_select)。

mysql> show status like '%Com_select%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select    | 1     |

+---------------+-------+

1 row in set (0.00 sec)

此时的查询缓存命中率：3/（3+1）=75%；由于个人的测试数据库，查询较少，更行更少，命中率颇高。

查询缓区的碎片整理 查询缓存使用一段时间之后，一般都会出现内存碎片，为此需要监控相关状态值，并且定期进行内存碎片的整理，碎片整理的操作语句：FLUSH QUERY CACHE；
清空查询缓存的数据
那些操作操作可能触发查询缓存，把所有缓存的信息清空，以避免触发或需要的时候，知道如何做，二类可触发查询缓存数据全部清空的命令：
(1).RESET QUERY CACHE；
(2).FLUSH TABLES；