MySQL 設定檔優化-cache

http://article.denniswave.com/6345

From：http://www.bsdlover.cn/html/89/n-1789.html

• thread_concurrency
數量設置為CPU核心數量的兩倍.

• thread_cache_size
按照內存大小來設置, 1G=8, 2G=16, 3G=32, >3G=64

• wait_timeout
超時時間,如果連接數比較大，可以減少此參數的值，我使用的是10

• max_connections
最大連接數，mysql實際允許連接數的值是max_connections+1，按照系統庫不同而有不同性能。一般是500~1000，MySQL AB提供的linux靜態庫可以達到4000。

• query_cache_size
查詢緩衝，默認是0，所以必須打開以提高mysql性能,其本身需要40K來保存結構數據。所以不能設置的太小，初期可以設置成32M，然後根據實際運行情況另行調整。

• query_cache_type
指定查詢緩衝的類型：
0是關閉
1是緩衝除了使用 SELECT SQL_NO_CACHE 語句指明了不需要緩衝的數據意外的所有查詢
2是只緩衝 SELECT SQL_CACHE 指定的查詢
一般設置為1

• query_cache_limit
允許進入查詢緩衝區的最小數據大小，默認值是1MB，可以修改的小一點以滿足更多查詢的需求。但是如果設置的過於小，則會導致很多新的小查詢的結果將原有的查詢結果交換出去，增加系統的顛簸。

相關命令

• 查詢mysql服務器相關狀態數據
>SHOW STATUS;

• 查詢mysql服務器相關配置選項
>SHOW VARIABLES;

• 整理查詢緩衝區裡的碎片
>flush query cache;

• 刪除查詢緩衝區裡的所有內容
>reset query cache;

• 設置mysql參數
>SET GLOBAL;

• 查詢mysql當前執行的sql語句
>show processlist;

變量含義

• 查詢緩存中的空閒內存塊的數目
Qcache_free_blocks

• 查詢緩存的空閒內存總數
Qcache_free_memory

• 緩存採樣數數目
Qcache_hits

• 被加入到緩存中的查詢數目
Qcache_inserts

• 因為缺少內存而被從緩存中刪除的查詢數目
Qcache_lowmem_prunes

• 沒有被緩存的查詢數目 (不能被緩存的，或由於 QUERY_CACHE_TYPE)
Qcache_not_cached

• 在緩存中已註冊的查詢數目
Qcache_queries_in_cache

• 查詢緩存中的塊的總數目
Qcache_total_blocks

MySQL查詢優化
查詢Cache狀態

>SHOW STATUS LIKE 'Qcache%';

如果Qcache_lowmem_prunes非常大,說明因為內存不足而被交換出cache的數據很多.如果增加內存.可以保證較小的交換次數以及較高的命中率
例如現在我們查詢的結果如下：

Qcache_free_blocks	1234
Qcache_free_memory	25957504
Qcache_hits	55771119
Qcache_inserts	7441153
Qcache_lowmem_prunes	28332
Qcache_not_cached	1233788
Qcache_queries_in_cache	4810
Qcache_total_blocks	11038

設置為64M cache內存後

>set global query_cache_size=67108864;

Qcache_free_blocks	1
Qcache_free_memory	66623616
Qcache_hits	55788258
Qcache_inserts	7445445
Qcache_lowmem_prunes	28332
Qcache_not_cached	1234057
Qcache_queries_in_cache	183
Qcache_total_blocks	392

自由內存塊看起來變小了。
是因為現在自由內存塊是一個整塊，而以前的內存塊都是分散的小塊。而因為重建了cache區，Qcache_queries_in_cache 變量變小了。因為此操作重新建立了cache內存區，所有數據重新緩存，在運行一兩天後我們再看此數據。如果變大了，說明增大cache內存區域是有效 的，如果和以前數據差不多說明增加的內存並沒有實際起到多大的作用。
有人會覺得如果我將cache內存設置的非常大，然後將cache_limit設置成0，那麼所有查詢都會被緩存了。理論上是這樣，但是一台數據庫 服務器的查詢非常多，如果連查詢單條數據都要緩存，那麼內存再大也會不夠的。到時候老的內容就會被交換出去，當cache內存使用滿的時候,就會不停的有 新查詢進來將老查詢替換出去。
這樣導致兩個結果：一個是內存顛簸，效率反而下降；第二個是cache內存的小碎塊增多，內存利用率降低。如果是只有內容很少的小庫，並且查詢率不高，是可以使用這種方法提高響應速度。但是如果是實際生產環境，數據量會比較大，還是需要按照最佳比例來配置。
而不同的應用不同的數據量會有不同的搭配，這點大家不要看網上的優化配置隨便的填寫，還是要時時的查看mysql的狀態進行調整。即便是這個月調整好的優化參數，到了下個月業務不同，數據量增加，也會需要調整的。