mysql緩衝參數

對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