MySQL參數最佳化

標籤：建立   for   .so   開頭   睡眠   最好   else   mon   isa   

MySQL參數最佳化

MySQL參數最佳化對於不同的網站，及其線上量，訪問量，貼文量，網路情況，以及機器硬體設定都有關係，最佳化不可能一次性完成，需要不斷的觀察以及調試，才有可能得到最佳的效果。

1）串連請求的變數

1、max_connections

MySQL的最大串連數，如果伺服器的並發串連請求量較大，建議調高此值，以增加並行串連數量，當然這建立在機器能支撐的情況下，因為如果串連數越多，MySQL回味每個串連提供串連緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。

數值過小經常會出現ERROR 1040：Too mant connetcions錯誤，可以通過mysql>show status like ‘connections’；萬用字元來查看目前狀態的串連數量（試圖串連到MySQL（不管是否串連成功）的串連數），以定奪該值的大小。

show variadles like ‘max_connections’最大串連數

show variables like ‘max_used_connection’相應串連數

max_used_connection/max_connections*100%（理想值約等於85%）

如果max_used_connections和max_connections相同，那麼就是max_connections值設定過低或者超過伺服器的負載上限了，低於10%則設定過大了。

2、back_log

MySQL能夠暫存的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，他就會起作用。如果MySQL的串連資料達到max_connections時，新的請求將會被存在堆棧中，以等待某一串連釋放資源，該堆棧數量即back_log，如果等待串連的數量超過back_log，將不被接受串連資源。

3、wait_timeout和interative_timeout

wait_timeout：指的是MySQL再關閉一個非互動的串連之前所需要等待的秒數。

interative_timeout：指的是關閉一個互動的串連之前所需要等待的秒數。

對效能的影響

wait_timeout

（1）如果設定太小，那麼串連關閉的很快，從而使一些持久的串連不起作用

（2）如果設定太大容易造成串連開啟時間過長，在show processlist時，能夠看到太多的sleep狀態的串連，從而造成too many connections錯誤。

（3）一般希望wait_timeuot儘可能的低

interative_timeout的設定將對你的web application沒有多大的影響

2）緩衝區變數

全域緩衝

4、key_buffer_size

key_buffer_size指定索引緩衝的大小，他決定索引的處理速度，尤其是索引讀的速度。通過檢查狀態值 key_read_requests和key_reads，可以知道key_buffer_size設定是否合理。比例key_reads/key_read_requests應該儘可能的低，至少是1：100，1：1000更好（上述狀態值可以使用show status like ‘key_read%’獲得）

未命中緩衝的機率：

key_cache_miss_rate = key_reads/key_read_requests*100%

key_buffer_size只對MAISAM表起作用。

如何調整key_buffer_size的值

預設的配置數時8388608（8M），主機有4G記憶體可以調優值為268435456（256M）

5、query_cache_size（查詢快取簡稱QC)

使用查詢快取，MySQL將查詢結果存放在緩衝區中，今後對同樣的select語句（區分大小寫），將直接從緩衝區中讀取結果。

一個SQL查詢如果以select開頭，那麼MySQL伺服器將嘗試對其使用查詢快取。

註：兩個SQL語句，只要相差哪怕是一個字元（例如 大小寫不一樣：多一個空格等），那麼兩個SQL將使用不同的cache

通過 show ststus like ‘Qcache%’ 可以知道query_cache_size的設定是否合理

Qcache_free_blocks：緩衝中相鄰記憶體塊的個數。如果該值顯示過大，則說明Query Cache中的記憶體片段較多了。

註：當一個表被更新後，和他相關的cache block將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。可以用 flush query cache語句來清空free blocks。

Qcache_free_memory:Query Cache 中目前剩餘的記憶體大小。通過這個參數我們可以較為準確的觀察當前系統中的Query Cache記憶體大小是否足夠，是需要增多還是過多了。

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

Qcache_inserts：表示多少次未命中而插入，意思是新來的SQL請求在緩衝中未找到，不得不執行查詢處理，執行查詢處理後把結果insert帶查詢快取中。這樣的情況次數越多，表示查詢快取 應用到的比較少，效果也就不理想。

Qcache_lowmen_prunes：多少條Query因為記憶體不足而被清除出Query Cache，通過Qcache_lowmem_prunes和Qcache_free_memory 相互結合，能夠更清楚的瞭解到我們系統中Query Cache的記憶體大小是否真的足夠，是否非常頻繁的出現因為記憶體不足而有Query被換出。這個數字最好是長時間來看，如果這個數字在不斷增長，就表示可能片段化非常嚴重，或者記憶體很少。

Qcache_queries_in_cache：當前Query Cache 中cache的Query數量

Qcache_total_blocks：當前Query Cache中block的數量

查詢服務器關於query_cache的配置

各欄位的解釋：

query_cache_limit：超出此大小的查詢將不被緩衝

query_cache_min_res_unit：緩衝塊的最小大小，query_cache_min_res_unit的配置是一柄雙刃劍，預設是 4KB ，設定值大對大資料查詢有好處，但是如果你查詢的都是小資料查詢，就容易造成記憶體片段和浪費。

query_cache_size：查詢快取大小（註：QC儲存的單位最小是1024byte，所以如果你設定的一個不是1024的倍數的值。這個值會被四捨五入到最接近當前值的等於1024的倍數的值。）

query_cache_type：緩衝類型，決定緩衝什麼樣子的查詢，注意這個值不能隨便設定必須設定為數字，可選值以及說明如下：

0：OFF 相當于禁用了

1：ON 將緩衝所有結果，除非你的select語句使用了SQL_NO_CACHE禁用了查詢快取

2：DENAND  則只緩衝select語句中通過SQL_CACHE指定需要緩衝的查詢。

query_cache_wlock_invalidate：當有其他用戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成在讀表擷取結果。

查詢快取片段率：Qcache_free_block/Qcache_total_block*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_hits+Qcache_inserts)*100%

Query Cache的限制

a）所有子查詢中的外部查詢SQL 不能被Cache：

b）在p'rocedure，function以及trigger中的Query不能被Cache

c）包含其他很多每次執行可能得到不一樣的結果的函數的Query不能被Cache

6、max_connect_errors：是一個MySQL中與安全有關的計數器值，他負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼的情況，當超過指定次數，MySQL伺服器將禁止host的串連請求，直到mysql伺服器重啟或通過flush hotos命令清空此host的相關資訊。（與效能並無太大的關係）

7、sort_buffer_size：每個需要排序的線程分配該大小的一個緩衝區。增加這值加速ORDER BY 或 GROUP BY操作

sort_buffer_size是一個connection級的參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性分配設定的記憶體。

sort_buffer_size：並不是越大越好，由於是connection級的參數，過大的設定+高並發可能會耗盡系統的記憶體資源。例如：500個串連將會消耗500*sort_buffer_size(2M)=1G

8、max_allowed_packet=32M

根據設定檔限制server接受的資料包大小。

9、join_buffer_size=2M

用於表示關聯緩衝的大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每個串連獨享。

10、thread_cache_size=300

伺服器線程緩衝，這個值表示可以重新利用儲存在緩衝中的線程數量，當中斷連線時，那麼用戶端的線程將被放到緩衝中以響應下一個客戶而不是銷毀（前提時緩衝數未達上限），如果線程重新被請求，那麼請求將從緩衝中讀取，如果緩衝中是空的或者是新的請求，這個線程將被重新請求，那麼這個線程將被重新建立，如果有很多新的線程，增加這個值可以改善系統效能，通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。

設定規則如下：1G記憶體配置為8，2G記憶體為16.伺服器處理此客戶的線程將會緩衝起來以響應下一個客戶而不是被銷毀（前提是緩衝數未到達上限）

Threads_cached：代表當前此時此刻線程緩衝中有多少空閑線程。

Threads_connected：代表當前已建立串連的數量，因為一個串連就需要一個線程，所以也可以看成當前被使用的線程數。

Threads_created：代表最近一次服務啟動，已建立線程的數量，如果發現Threads_created值過大的話，說明MySQL伺服器一直在建立線程，這也比較消耗資源，可以適當增加設定檔中thread_cache_size值

Threads_running：代表當前啟用的（非睡眠狀態）線程數。並不是代表正在使用的線程數，有時候串連已建立，但是串連處於sleep狀態。

3）配置Innodb的幾個變數

11、innodb_buffer_pool_size

對於innodb表來說，innodb_buffer_pool_size的作用相當於key_buffer_size對於MyISAM表的作用一樣。Innodb使用該參數指定大小的記憶體來緩衝資料和索引。最大可以把該值設定成實體記憶體的80%。

12、innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的資料寫入記錄檔並flush磁碟的時間點，取值分別為0，1，2.

實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄只需要兩秒，設定為0時只需要一秒，設定為1時，則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅度提高速度。

13、innodb_thread_concurrency=0

此參數用來設定innodb線程的並發數，預設值為0表示不被限制，若要設定則與伺服器的CPU核心數相同或是CPU的核心數的2倍。

14、innodb_log_buffer_size

此參數確定記錄檔所用的記憶體大小，以M為單位。緩衝區更大能提高效能，對於較大的事務，可以增大緩衝大小。

15、innodb_log_file_size=50M

此參數確定資料記錄檔的大小，以M為單位，更大的設定可以提高效能。

16、innodb_log_files_in_group=3

為提高效能，MySQL可以以迴圈方式將記錄檔寫到多個檔案。推薦設定為3

17、read_buffer_size=1M

MySQL 讀入緩衝區大小。對錶進行順序掃描的請求將分配到一個讀入緩衝區MySQL會為他分配一段記憶體緩衝區

18、read_rnd_buffer_size=16M

MySQL 的隨機讀（查詢操作）緩衝區大小。當按任意順序讀取行時（例如，按照排序次序），將分配到一個隨機都緩衝區。進行排序查詢時，MySQL會首先掃描一遍該緩衝區，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但是MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體消耗過大。

註：順序讀是根據索引的分葉節點資料就能順序的讀取所需要的行資料。隨機讀是指一般需要根據輔助索引分葉節點中的主鍵尋找侍其巷進行資料，而輔助索引和主鍵所在的資料端不同，因此訪問方式是隨機的。

19、bulk_insert_buffer_size=64M

批量插入資料緩衝大小，可以有效提高插入效率，預設為8M

20、binary log

binlog_cache_size=2M   //為每個session分配的記憶體，在事務過程中用來儲存二進位日誌的緩衝，提高記錄bin-log的效率。

max_binlog_cache_size=8M //表示的是binlog能夠使用的最大cache記憶體大小

max_binlog_size=512M  //指定binlog記錄檔的大小。不能將變數設定為大於1G或小於4096位元組。預設值為1G.在匯入大容量的sql檔案時，建議關閉，sql_log_bin，否則硬碟扛不住，而且建議定期做刪除。

expire_logs_days=7  //定義了mysql清除到期日誌的時間

參數匯總：

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

server_id = 1

socket = /usr/local/mysql/mysql.sock

log-error = /usr/local/mysql/data/mysqld.err

slow_query_log = 1

slow_query_log_file=/usr/local/mysql/data/slow-query.log

long_query_time = 1

log-queries-not-using-indexes

max_connections = 1024

back_log = 128

wait_timeout = 60

interactive_timeout = 7200

key_buffer_size = 256M

query_cache_size = 256M

query_cache_type = 1

query_cache_limit = 50M 

max_connect_errors = 20

sort_buffer_size = 2M

max_allowed_packet = 32M

join_buffer_size = 2M

thread_cache_size = 200

innodb_buffer_pool_size = 2048M

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 32M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

log-bin=/usr/local/mysql/data/mysqlbin

binlog_cache_size = 2M 

max_binlog_cache_size = 8M

max_binlog_size = 512M

expire_logs_days = 7

read_buffer_size = 1M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

MySQL參數最佳化