MySQL資料庫服務優化參數設置參考

MySQL服務優化參數設置參考

 

l 通用類:

 

key_buffer_size

 

含義:用於索引塊的緩沖區大小,增加它可得到更好處理的索引(對所有讀和多重寫)。

 

影響:對於MyISAM表的影響不是很大,MyISAM會使用系統的緩存來存儲數據,所以大量使用MyISAM表的機器內存很快就會耗盡。但是,如果你將該值設得過大(例如,大於總內存的50%),系統將轉換為頁並變得極慢。MySQL依賴操作系統來執行數據讀取時的文件系統緩存,因此你必須為文件系統緩存留一些空間。

 

建議:先設置為內存的25%,觀察性能變化。

 

table_open_cache

 

含義:為所有線程打開表的數量。

 

影響:增加該值能增加mysqld要求的文件描述符的數量。可以避免頻繁的打開數據表產生的開銷。打開一個表的開銷可能很大,因為MyISAM會把MYI文件的文件頭標識為正在使用,所以在內存中做這個操作比較好。,因為每個線程都需要打開表,連接數越大這個值要越大。

 

建議:我們有300多個表的話,大約2048差不多瞭。

 

 

 

thread_cache_size

 

含義:緩存可重用的線程數。

 

影響:這個參數設置線程的緩存,線程的創建和銷毀的開銷可能很大,因為每個線程的連接/斷開都需要。如果應用程式中有大量的跳躍並發連接並且線程較多的話,就要加大它的值。它的目的是在通常的操作中無需創建新線程。

 

建議:通常至少設置為16。

 

 

 

query_cache_size

 

含義:指定MySQL查詢結果緩沖區的大小。

 

影響:如果應用程式有大量讀,而且沒有應用程式級別的緩存,那麼這很有用。不過不要設置太大,因為維護它也需要不少開銷,這會導致MySQL變慢。

 

建議:通常設置為 32-512Mb。設置完之後最好是跟蹤一段時間,查看是否運行良好。在一定的負載壓力下,如果緩存命中率太低瞭,就啟用它,如果命中率已經不錯瞭,就可以把他調小一點。對於2G的內存,先從16M開始,一倍一倍的增加,直到命中率比較穩定為止。

 

query_cache_limit

 

含義:緩存單條SQL的結果集上限。默認4KB。

 

影響:當一條SQL返回的結果集大於這個限制的時候,將不被MySQL緩存。

 

建議:設置為1M即可。

 

query_cache_min_res_unit

 

含義:緩存為每個數據集存放的最小內存大小,默認4KB。

 

影響:如果太小的話,將會導致MySQL經常訪問內存塊去獲取信息,如果設置太大則浪費內存。

 

建議:如果SQL返回的結果集都很小的話,參數也可以調小一點,避免內存浪費。如果結果集大部分都大於4KB的話,則考慮加大參數。

 

back_log

 

含義:在MySQL的連接請求等待隊列中允許存放的最大連接請求數。系統默認值為50。

 

影響:如果系統在一個短時間內有很多連接,則需要增大該參數的值,該參數值指定到來的TCP/IP連接的偵聽隊列的大小。不同的操作系統在這個隊列大小上有它自 己的限制。試圖設定back_log高於你的操作系統的限制將是無效的。

 

建議:對於Linux系統推薦設置為小於512的整數。

 

sort_buffer_size

 

含義:為每個需要進行排序的線程分配該大小的一個緩沖區。

 

影響:增加這值加速ORDER BY或GROUP BY操作。不過該參數對應的分配內存是每連接獨占的,如果有100個連接,那麼實際分配的總共排序緩沖區大小為100×sort_buffer_size。

 

建議:一般設置為2M觀察變化再調整。

 

read_buffer_size

 

含義:順序查詢操作所能使用的緩沖區大小。

 

影響:和sort_buffer_size一樣,該參數對應的分配內存也是每連接獨享。

 

建議:一般設置為2M再觀察變化。

 

read_rnd_buffer_size

 

含義:隨機查詢操作所能使用的緩沖區大小。

 

影響:每個線程獨享。

 

建議:一般設置為2M再觀察變化。

 

query_cache_type

 

含義:查詢緩沖類型。

 

影響:為1是使用緩沖,2是除非使用SQL_CACHE才進行緩沖。對於緩沖而言,數據並不是實時的,有一定的延時。但是對於實時性要求不高的查詢短時間內多次執行,是不劃算的,這個時候就需要緩存。並且緩存中是區分空格和大小寫的,如果大小寫不一致和空格不一致,也會認為是不同的SQL,不會利用到緩存。雖然不設置查詢緩沖,有時可能帶來性能上的損失,但有一些SQL語句需要實時地查詢數據,或並不經常使用(可能一天就執行一兩次),這樣就需要把緩沖關瞭,可以采用臨時關閉的方法SELECT SQL_NO_CACHE。

 

建議:一般設置為1。

 

l MyISAM類:

 

myisam_sort_buffer_size

 

MyISAM表發生變化時重新排序所需的緩沖。一般64M足矣。

 

l InnoDB類:

 

innodb_buffer_pool_size

 

對InnoDB的效率影響很大。因為InnoDB會把盡可能多的數據和索引緩存在緩沖區,這個類似與Oracle的Buffer Pool:如果隻采用InnoDB,可以把這個參數調大一點,大約內存的70%左右。

 

當然,如果數據量不會暴增並且不是特別大,這個參數還是不要太大瞭,浪費空間。

 

innodb_additional_pool_size

 

對資料庫性能影響不是很大,至少內存足夠的機器上不會有什麼影響。

 

innodb_log_file_size

 

在高寫入負載尤其是數據集很大的時候,這個值非常重要,值越高性能越好,不過可能會增加數據恢復的時候。我設置為128M。

 

innodb_log_buffer_size

 

默認的設置在中等強度的寫入負載及短事物處理時,性能還可以。但是存在大量更新操作或者負載較大時,就要慢慢增加這個參數的值瞭。不過不要設置太大,會浪費內存。它每秒都會刷新一次,所以不用設置超過1s所需的內存空間,16M足夠瞭。

 

innodb_flush_logs_at_trx_commit

 

這個參數對InnoDB及其重要,設置不好的話會比MyISAM慢1000倍!默認是1,這就是說每次更新事務都會被提交到磁盤,這是非常消耗資源的,硬盤和內存的速度是明顯數量級的差距。

 

設置為0是最快的,但是很不安全,全部在緩存中,一掉電全沒瞭。

 

設置為1很不好,每次都去寫硬盤,沒有必要。

 

設置為2是比較好的,日志不刷新到磁盤上,隻刷新到操作系統緩存上。然後每秒鐘寫緩存。相對於現在資料庫每秒4K條左右的SQL,性能已經可以提高不少。

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。