mysql性能调试query cache

发布时间:2017-06-24 10:52:47编辑:丝画阁阅读(699)

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值过大,应该调小些
5query_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;




关键字