MYSQL5.6緩存性能優化my.ini檔案配置方案

使用MYSQL版本：5.6

<a></a>

官方文檔：

<a href="http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html" target="_blank">http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html" target="_blank">http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html</a>

1、back_log

back_log值指出在MySQL暫時停止回答新請求之前的短時間内多少個請求可以被存在堆棧中。也就是說，如果MySql的連接配接資料達到max_connections時，新來的請求将會被存在堆棧中，以等待某一連接配接釋放資源，該堆棧的數量即back_log，如果等待連接配接的數量超過back_log，将不被授予連接配接資源。将會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接配接程序逾時。

back_log值不能超過TCP/IP連接配接的偵聽隊列的大小。若超過則無效，檢視目前系統的TCP/IP連接配接的偵聽隊列的大小指令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系統為1024。對于Linux系統推薦設定為小于512的整數。

檢視mysql 目前系統預設back_log值，指令：

2、interactive_timeout

interactive_timeout是MySQL在等待一個活動連接配接關閉連接配接前等待的秒數。

wait_timeout是MySQL在等待一個非活動連接配接關閉連接配接前等待的秒數。

3、query_cache_size、query_cache_type

MYSQL的查詢緩存用于緩存select查詢結果，并在下次接收到同樣的查詢請求時，不再執行實際查詢處理而直接傳回結果，有這樣的查詢緩存能提高查詢的速度，使查詢性能得到優化，前提條件是你有大量的相同或相似的查詢，而很少改變表裡的資料，否則沒有必要使用此功能。可以通過Qcache_lowmem_prunes變量的值來檢查是否目前的值滿足你目前系統的負載。注意：如果你查詢的表更新比較頻繁，而且很少有 相同的查詢，最好不要使用查詢緩存。

具體配置方法：

1． 将query_cache_size設定為具體的大小，具體大小是多少取決于查詢的實際情況，但最好設定為1024的倍數，參考值32M。

2． 增加一行：query_cache_type=1

如果設定為0，那麼可以說，你的緩存根本就沒有用，相當于禁用了。但是這種情況下query_cache_size設定的大小系統是否要為其配置設定呢，這個問題有待于測試？

如果設定為1，将會緩存所有的結果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。

如果設定為2，則隻緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

儲存檔案，重新啟動MYSQL服務，然後通過如下查詢來驗證是否真正開啟了：

+——————————+———–+

| 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_size和query_cache_type的值是否跟我們設的一緻：

這裡query_cache_size的值是134217728，我們設定的是128M，實際是一樣的，隻是機關不同，可以自己換算下：134217728 = 128*1024*1024。

query_cache_type設定為1，顯示為ON，這個前面已經說過了。

總之，看到上邊的顯示表示設定正确，但是在實際的查詢中是否能夠緩存查詢，還需要手動測試下，我們可以通過show status like ‘%Qcache%’;語句來測試，現在我們開啟了查詢緩存功能，在執行查詢前，我們先看看相關參數的值：

+————————-+———–+

| Variable_name           | Value     |

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134208800 |

| Qcache_hits             | 0         |

| Qcache_inserts          | 0         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 2         |

| Qcache_queries_in_cache | 0         |

| Qcache_total_blocks     | 1         |

8 rows in set (0.00 sec)

這裡順便解釋下這個幾個參數的作用：

Qcache_free_blocks:表示查詢緩存中目前還有多少剩餘的blocks，如果該值顯示較大，則說明查詢緩存中的記憶體碎片過多了，可能在一定的時間進行整理。

Qcache_free_memory:查詢緩存的記憶體大小，通過這個參數可以很清晰的知道目前系統的查詢記憶體是否夠用，是多了，還是不夠用，DBA可以根據實際情況做出調整。

Qcache_hits:表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數字越大，緩存效果越理想。

Qcache_inserts: 表示多少次未命中然後插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理後把結果insert到查詢緩存中。這樣的情況的次 數，次數越多，表示查詢緩存應用到的比較少，效果也就不理想。當然系統剛啟動後，查詢緩存是空的，這很正常。

Qcache_lowmem_prunes:該參數記錄有多少條查詢因為記憶體不足而被移除出查詢緩存。通過這個值，使用者可以适當的調整緩存大小。

Qcache_not_cached: 表示因為query_cache_type的設定而沒有被緩存的查詢數量。

Qcache_queries_in_cache:目前緩存中緩存的查詢數量。

Qcache_total_blocks:目前緩存的block數量。

4、table_open_cache

table_cache 參數設定表高速緩存的數目。每個連接配接進來，都會至少打開一個表緩存。是以， table_open_cache 的大小應與 max_connections 的設定有關。例如，對于 200 個并行運作的連接配接，應該讓表的緩存至少有 200 × N ，這裡 N 是應用可以執行的查詢的一個聯接中表的最大數量。此外，還需要為臨時表和檔案保留一些額外的檔案描述符。

當 Mysql 通路一個表時，如果該表在緩存中已經被打開，則可以直接通路緩存；如果還沒有被緩存，但是在 Mysql 表緩沖區中還有空間，那麼這個表就被打開并放入表緩沖區；如果表緩存滿了，則會按照一定的規則将目前未用的表釋放，或者臨時擴大表緩存來存放，使用表緩存的好處是可以更快速地通路表中的内容。

一般來說，可以通過檢視資料庫運作峰值時間的狀态值 Open_tables 和 Opened_tables ，判斷是否需要增加 table_cache 的值（其中 open_tables 是目前打開的表的數量， Opened_tables 則是已經打開的表的數量）。即如果open_tables接近table_open_cache的時候，并且Opened_tables這個值在逐漸增加，那就要考慮增加這個值的大小了。還有就是Table_locks_waited比較高的時候，也需要增加table_open_cache。

如果Open_tables的值已經接近table_open_cache的值，且Opened_tables還在不斷變大，則說明mysql正在将緩存的表釋放以容納新的表，此時可能需要加大table_open_cache的值。對于大多數情況，

比較适合的值：

Open_tables / Opened_tables &gt;= 0.85 Open_tables / table_open_cache &lt;= 0.95

如果對此參數的把握不是很準，VPS管理百科給出一個很保守的設定建議：把MySQL資料庫放在生産環境中試運作一段時間，然後把參數的值調整得比Opened_tables的數值大一些，并且保證在比較高負載的極端條件下依然比Opened_tables略大。

在mysql預設安裝情況下，table_open_cache的值在2G記憶體以下的機器中的值預設時256到 512，如果機器有4G記憶體,則預設這個值是2048，但這決意味着機器記憶體越大，這個值應該越大，因為table_open_cache加大後，使得mysql對 SQL響應的速度更快了，不可避免的會産生更多的死鎖（dead lock），這樣反而使得資料庫整個一套操作慢了下來，嚴重影響性能。是以平時維護中還是要根據庫的實際情況去作出判斷，找到最适合你維護的庫的 table_open_cache值。

執行

指令将會清空目前所有緩存的表。

5、myisam_max_sort_file_size

# MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

# 如果檔案大小比此值更大,索引會通過鍵值緩沖建立(更慢)

6、myisam_sort_buffer_size

# MyISAM表發生變化時重新排序所需的緩沖

7、key_buffer_size

1.單個key_buffer的大小不能超過4G，如果設定超過4G，就有可能遇到下面3個bug:

<a href="http://bugs.mysql.com/bug.php?id=29446" target="_blank">http://bugs.mysql.com/bug.php?id=29446</a>

<a href="http://bugs.mysql.com/bug.php?id=29419" target="_blank">http://bugs.mysql.com/bug.php?id=29419</a>

<a href="http://bugs.mysql.com/bug.php?id=5731" target="_blank">http://bugs.mysql.com/bug.php?id=5731</a>

2.建議key_buffer設定為實體記憶體的1/4(針對MyISAM引擎)，甚至是實體記憶體的30%~40%，如果key_buffer_size設定太大，系統就會頻繁的換頁，降低系統性能。因為MySQL使用作業系統的緩存來緩存資料，是以我們得為系統留夠足夠的記憶體；在很多情況下資料要比索引大得多。

3.如果機器性能優越，可以設定多個key_buffer,分别讓不同的key_buffer來緩存專門的索引

8、read_buffer_size

# MySql讀入緩沖區大小。對表進行順序掃描的請求将配置設定一個讀入緩沖區，MySql會為它配置設定一段記憶體緩沖區。read_buffer_size變量控制這一緩沖區的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能。和sort_buffer_size一樣，該參數對應的配置設定記憶體也是每個連接配接獨享。

本文轉自黃聰部落格園部落格，原文連結：http://www.cnblogs.com/huangcong/p/5218461.html，如需轉載請自行聯系原作者