源码库详解mysql缓存参数的使用方法
2024-07-24 12:40:31
供稿：网友
对mysql的优化不在行，搞过几次优化，但是都不是很理想，还是浪费资源太多。一直发现我的mysql的缓存命中率极差，情况良好的时候到达过60-70%，但是运行时间一长，只有10-20%。查了一些资料，关于缓存的一些参数记录 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 | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +——————————+———-+ 6 rows in set (0.00 sec) have_query_cache 是否支持查询缓存区 “YES”表是支持查询缓存区 query_cache_limit 可缓存的Select查询结果的最大值 1048576 byte /1024 = 1024kB 即最大可缓存的select查询结果必须小于1024KB query_cache_min_res_unit 每次给query cache结果分配内存的大小 默认是 4096 byte 也即 4kB 经过我测试， set GLOBAL query_cache_min_res_unit=4096; 的时候，碎片会比较多，在3000多。 set GLOBAL query_cache_min_res_unit=2046;的时候，碎片比较少，在1000多。 但是过小会增加IO负担 mysql>show status; 中间有一段 Qcache_开头的 Qcache_free_blocks | 4984 | | Qcache_free_memory | 30097400 | | Qcache_hits | 701669 | | Qcache_inserts | 832414 | | Qcache_lowmem_prunes | 41224 | | Qcache_not_cached | 2654 | | Qcache_queries_in_cache | 20527 | | Qcache_total_blocks | 46362 1-(Qcache_hits /Qcache_inserts )是缓存命中率。 但是我总质疑干嘛要1减去这个数字，Qcache_hits 是命令的数量，那么Qcache_inserts是总数，命中数量除以总数，不就是命中率了，这里还要好好查查。 Qcache_free_memory 表示查询缓存区现在还有多少的可用内存 Qcache_hits 表示查询缓存区的命中个数，也就是直接从查询缓存区作出响应处理的查询个数 Qcache_inserts 表示查询缓存区此前总过缓存过多少条查询命令的结果 Qcache_lowmem_prunes 表示查询缓存区已满而从其中溢出和删除的查询结果的个数 Qcache_not_cached 表示没有进入查询缓存区的查询命令个数 Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果 这部分和我后来找到的另外的有些出入 MySQL查询缓存变量解释： Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 Qcache_free_memory：缓存中的空闲内存。 Qcache_hits：每次查询在缓存中命中时就增大 Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。 Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况) Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。 Qcache_total_blocks：缓存中块的数量。 query_cache_limit：超过此大小的查询将不缓存 query_cache_min_res_unit：缓存块的最小大小 query_cache_size：查询缓存大小 query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询 query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。 query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。 查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。 查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100% 查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100% 示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。 引用一段前辈的话 优化提示： 如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。 如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache 根据我看的 《High Performance MySQL》中所述，关于query_cache_min_res_unit大小的调优 ，书中给出了一个计算公式，可以供调优设置参考： query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
