MySQL 設定檔優化-cache

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的狀態進行調整。即便是這個月調整好的優化參數,到了下個月業務不同,數據量增加,也會需要調整的。

Please follow and like us:

2 comments on “MySQL 設定檔優化-cache

  1. 更改前(8MB):
    +-------------------------+--------+
    | Variable_name | Value |
    +-------------------------+--------+
    | Qcache_free_blocks | 341 |
    | Qcache_free_memory | 951328 |
    | Qcache_hits | 582095 |
    | Qcache_inserts | 840979 |
    | Qcache_lowmem_prunes | 471145 |
    | Qcache_not_cached | 14304 |
    | Qcache_queries_in_cache| 1295 |
    | Qcache_total_blocks | 3481 |
    +-------------------------+--------+

  2. 更改後(32MB):
    +-------------------------+---------+
    | Variable_name | Value |
    +-------------------------+---------+
    | Qcache_free_blocks | 926 |
    | Qcache_free_memory | 6064696 |
    | Qcache_hits | 925690 |
    | Qcache_inserts | 600668 |
    | Qcache_lowmem_prunes | 106150 |
    | Qcache_not_cached | 27196 |
    | Qcache_queries_in_cache | 7513 |
    | Qcache_total_blocks | 16123 |
    +-------------------------+---------+

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *