源碼庫詳解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
