mysql在大資料量下效能調優相關參數

標籤：配置   伺服器   利用   命中   size   提高   設定   art   div   

摘自http://blog.csdn.net/nightelve/article/details/17393631

一、thread_concurrency，預設的8

thread_concurrency的值的正確與否, 對mysql的效能影響很大, 在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值, 會導致mysql不能充分利用多cpu(或多核), 出現同一時刻只能一個cpu(或核)在工作的情況。
thread_concurrency應設為CPU核心數的2倍. 比如有一個雙核的CPU, 那thread_concurrency 的應該為4; 2個雙核的cpu, thread_concurrency的值應為8.
比如：根據上面介紹我們目前系統的配置，可知道為4個CPU,每個CPU為8核，按照上面的計算規則，這兒應為:4*8*2=64
查看系統當前thread_concurrency預設配置命令：

show variables like ‘thread_concurrency‘;

二、key_buffer_size,預設為384M

key_buffer_size是用於索引塊的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，對MyISAM(MySQL表格儲存體的一種類型，可以百度等查看詳情)表效能影響最大的一個參數。如果你使它太大，系統將開始換頁並且真的變慢了。嚴格說是它決定了資料庫索引處理的速度，尤其是索引讀的速度。對於記憶體在4GB左右的伺服器該參數可設定為256M或384M.
怎麼才能知道key_buffer_size的設定是否合理呢，一般可以檢查狀態值Key_read_requests和Key_reads ，比例key_reads / key_read_requests應該儘可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下命令查得：

show status like ‘key_read%‘;

比如查看系統當前key_read和key_read_request值為：

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 28535 |
| Key_reads | 269 |
+-------------------+-------+
可知道有28535個請求，有269個請求在記憶體中沒有找到直接從硬碟讀取索引.
未命中緩衝的機率為：0.94%=269/28535*100%. 一般未命中機率在0.1之下比較好。目前已遠遠大於0.1，證明效果不好。若命中率在0.01以下，則建議適當的修改key_buffer_size值。

 

三、innodb_buffer_pool_size(預設128M)

innodb_buffer_pool_size:主要針對InnoDB表效能影響最大的一個參數。功能與Key_buffer_size一樣。InnoDB佔用的記憶體，除innodb_buffer_pool_size用於儲存頁面快取資料外，另外正常情況下還有大約8%的開銷，主要用在每個快取頁面幀的描述、adaptive hash等資料結構，如果不是安全關閉，啟動時還要恢複的話，還要另開大約12%的記憶體用於恢複，兩者相加就有差不多21%的開銷。假設：12G的innodb_buffer_pool_size，最多的時候InnoDB就可能佔用到14.5G的記憶體。若系統只有16G，而且只運行MySQL，且MySQL只用InnoDB，
那麼為MySQL開12G，是最大限度地利用記憶體了。
另外InnoDB和 MyISAM 儲存引擎不同， MyISAM 的 key_buffer_size 只能緩衝索引鍵，而 innodb_buffer_pool_size 卻可以快取資料塊和索引鍵。適當的增加這個參數的大小，可以有效減少 InnoDB 類型的表的磁碟 I/O 。
當我們操作一個 InnoDB 表的時候，返回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體地區中走一遭。
可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算快取命中率，並根據命中率來調整 innodb_buffer_pool_size 參數大小進行最佳化。值可以用以下命令查得：

show status like ‘Innodb_buffer_pool_read%‘;

比如查看當前系統中系統中

+---------------------------------------+---------+

| Innodb_buffer_pool_read_requests | 1283826 |
| Innodb_buffer_pool_reads | 519 |
+---------------------------------------+---------+
其命中率99.959%=（1283826-519）/1283826*100% 命中率越高越好。

 

四、innodb_additional_mem_pool_size(預設8M)

innodb_additional_mem_pool_size 設定了InnoDB儲存引擎用來存放資料字典資訊以及一些內部資料結構的記憶體空間大小，所以當我們一個MySQL Instance中的資料庫物件非常多的時候，是需要適當調整該參數的大小以確保所有資料都能存放在記憶體中提高訪問效率的。
這個參數大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL會記錄Warning資訊到資料庫的error log中，這時候你就知道該調整這個參數大小了。
查看當前系統mysql的error日誌 cat /var/lib/mysql/機器名.error 發現有很多waring警告。所以要調大為20M.
根據MySQL手冊，對於2G記憶體的機器，推薦值是20M。
32G記憶體的 100M

 

mysql在大資料量下效能調優相關參數