mysql?的優化方案，在互聯網上可以查找到非常多資料，今天對mysql緩存碎片和命中率作了詳細了解，個人作了簡單整理。
一、Mysql查詢緩存碎片和緩存命中率。
mysql>?SHOW?STATUS?LIKE?'qcache%'; ????????+-------------------------+-----------+ ????????|?Variable_name?|?Value?| ????????+-------------------------+-----------+ ????????|?Qcache_free_blocks?|?5?| ????????|?Qcache_free_memory?|?134176648?| ????????|?Qcache_hits?|?110?| ????????|?Qcache_inserts?|?245?| ????????|?Qcache_lowmem_prunes?|?0?| ????????|?Qcache_not_cached?|?7119?| ????????|?Qcache_queries_in_cache?|?9?| ????????|?Qcache_total_blocks?|?31?| ????????+-------------------------+-----------+ ????????8?rows?in?set?(0.01?sec)
MySQL?查詢緩存變量
變量名 ?
說明 ??
Qcache_free_blocks ??
緩存中相鄰內存塊的個數。數目大說明可能有碎片。FLUSH?QUERY?CACHE?會對緩存中的碎片進行整理，從而得到一個空閑塊。 ?
Qcache_free_memory ??
緩存中的空閑內存。 ??
Qcache_hits ??
每次查詢在緩存中命中時就增大。 ??
Qcache_inserts ??
每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率；用?1?減去這個值就是命中率。在上面這個例子中，大約有?87%?的查詢都在緩存中命中。 ?
Qcache_lowmem_prunes ??
緩存出現內存不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者內存很少。（上面的?free_blocks?和?free_memory?可以告訴您屬于哪種情況）。?
Qcache_not_cached ??
不適合進行緩存的查詢的數量，通常是由于這些查詢不是?SELECT?語句。?
Qcache_queries_in_cache ?
當前緩存的查詢（和響應）的數量。 ??
Qcache_total_blocks ??
緩存中塊的數量。
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?|?134217728?|? |?query_cache_type?|?ON?|? |?query_cache_wlock_invalidate?|?OFF?|? +------------------------------+-----------+ 6?rows?in?set?(0.00?sec)
?
query_cache_min_res_unit????查詢緩存分配的最小塊的大小(字節)
query_alloc_block_size????為查詢分析和執行過程中創建的對象分配的內存塊大小
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
檢查查詢緩存使用情況
檢查是否從查詢緩存中受益的最簡單的辦法就是檢查緩存命中率
當服務器收到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%；由于個人的測試數據庫，查詢較少，更行更少，命中率頗高。
二、監控緩存命中率
通過Nagios+pnp4nagios來監控緩存命中率，并通過圖表來展示。
1、監控腳本：?check_mysql_qch.sh.sh
#!/bin/bash #function:查詢緩存命中率 #time:20121130 #author:system?group while?getopts?":w:c:h"?optname do case?"$optname"?in "w") WARN=$OPTARG ;; "c") CIRT=$OPTARG ;; "h") echo?"Useage:?check_mysql_qch.sh?-w?warn?-c?cirt" exit ;; "?") echo?"Unknown?option?$OPTARG" exit ;; ":") echo?"No?argument?value?for?option?$OPTARG" exit ;; *) #?Should?not?occur echo?"Unknown?error?while?processing?options" exit ;; esac done [?$??-ne?0?]?&&?echo?"error:?Unknown?option?"?&&?exit [?-z?$WARN?]?&&?WARN=60 [?-z?$CIRT?]?&&?CIRT=50 export?selete=`/usr/local/mysql/bin/mysql?-h?127.0.0.1?-uroot?-Bse?"SHOW?GLOBAL?STATUS?LIKE?'Com_select';"?|awk?'{print?$2}'` export?hits=`/usr/local/mysql/bin/mysql?-h?127.0.0.1?-uroot?-Bse?"SHOW?GLOBAL?STATUS?LIKE?'Qcache_hits';"?|awk?'{print?$2}'` a=$(($selete+$hits)) #rw_ratio=$(($a/$b)) #echo?"rw_ratio=$rw_ratio" #ratio=$(($rw_ratio*100)) #echo?"ratio=$ratio" if?[?$a?-ne?"0"?];then percent=`awk?'BEGIN{printf?"%.2f%n",('$hits'/'$a')*100}'` Qch=`awk?'BEGIN{printf?('$hits'/'$a')*100}'` fi C=`echo?"$Qch??$WARN"?|?bc` if?[?$C?==?1?];then echo?-e?"CIRT?-?Mysql?Qcache?Hits?is?$percent,Com_select?is?$selete,Qcache_hits?is?$hits|Qcache_hits=${Qch}%;${selete};${hits};0" exit?2 fi if?[?$W?==?1?];then echo?-e?"WARN?-?Mysql?Qcache?Hits?is?$percent,Com_select?is?$selete,Qcache_hits?is?$hits|Qcache_hits=${Qch}%;${selete};${hits};0" exit?1 fi if?[?$O?==?1?];then echo?-e?"OK?-?Mysql?Qcache?Hits?is?$percent,Com_select?is?$selete,Qcache_hits?is?$hits|Qcache_hits=${Qch}%;${selete};${hits};0" exit?0 fi
2、生成報表
Pnp4nagios?templates：check_mysql_qch.php?
<?php # # Copyright (c) 2006-2010 system (http://www.cnfol.com) # Plugin: check_mysql_qch # $opt[1] = "--vertical-label hits/s -l0 --title "Mysql Qcache Hits for $hostname / $servicedesc" "; # # # $def[1] = rrd::def("var1", $RRDFILE[1], $DS[1], "AVERAGE"); if ($WARN[1] != "") { $def[1] .= "HRULE:$WARN[1]#FFFF00 "; } if ($CRIT[1] != "") { $def[1] .= "HRULE:$CRIT[1]#FF0000 "; } $def[1] .= rrd::area("var1", "#0000FF", "Mysql Qcache Hits percent") ; $def[1] .= rrd::gprint("var1", array("LAST", "AVERAGE", "MAX"), "%6.2lf"); ?>
結果：
