隨著 大量默認選項的改進, MySQL 5.6比以前版本需要調優的選項大為減少. 在本文中我將講述需要優化的配置項.

InnoDB設置

1.innodb_buffer_pool_size  —— 默認值為 128M. 這是最主要的優化選項,因為它指定 InnoDB 使用多少內存來加載數據和索引(data+indexes). 針對專用MySQL服務器,建議指定為物理內存的 50-80%這個范圍. 例如,擁有64GB物理內存的機器,緩存池應該設置為50GB左右.
如果將該值設置得更大可能會存在風險,比如沒有足夠的空閑內存留給操作系統和依賴文件系統緩存的某些MySQL子系統(subsystem),包括二進制日志(binary logs),InnoDB事務日志(transaction logs)等.

2.innodb_log_file_size —— 默認值為 48M. 有很高寫入吞吐量的系統需要增加該值以允許后臺檢查點活動在更長的時間周期內平滑寫入,得以改進性能. 將此值設置為4G以下是很安全的. 過去的實踐表明,日志文件太大的缺點是增加了崩潰時所需的修復時間,但這在5.5和5.6中已得到重大改進.

3.innodb_flush_method  —— 默認值為 fdatasync. 如果使用 硬件RAID磁盤控制器, 可能需要設置為 O_DIRECT. 這在讀取InnoDB緩沖池時可防止“雙緩沖(double buffering)”效應,否則會在文件系統緩存與InnoDB緩存間形成2個副本(copy).
如果不使用硬件RAID控制器,或者使用SAN存儲時, O_DIRECT 可能會導致性能下降.MySQL用戶手冊 和 Bug #54306  詳細地說明了這一點.

4.innodb_flush_neighbors —— 默認值為 1. 在SSD存儲上應設置為0(禁用) ,因為使用順序IO沒有任何性能收益. 在使用RAID的某些硬件上也應該禁用此設置,因為邏輯上連續的塊在物理磁盤上并不能保證也是連續的.

5.innodb_io_capacity and innodb_io_capacity_max —— 這些設置會影響InnoDB每秒在后臺執行多少操作. 如果你深度了解硬件性能(如每秒可以執行多少次IO操作),則使用這些功能是很可取的,而不是讓它閑著.

有一個很好的類比示例:  假如某次航班一張票也沒有賣出去 —— 那么讓稍后航班的一些人乘坐該次航班,有可能是很好的策略,以防后面遇到惡劣的天氣. 即有機會就將后臺操作順便處理了,以減少同稍后可能的實時操作產生競爭.

 有一個很簡單的計算:  如果每個磁盤每秒讀寫(IOPS)可以達到 200次, 則擁有10個磁盤的 RAID10 磁盤陣列IOPS理論上 =(10/2)* 200 = 1000. 我說它“很簡單”,是因為RAID控制器通常能夠提供額外的合并,并有效提高IOPS能力. 對于SSD磁盤,IOPS可以輕松達到好幾千.

 將這兩個值設置得太大可能會存在某些風險,你肯定不希望后臺操作妨礙了前臺任務IO操作的性能. 過去的經驗表明,將這兩個值設置的太高,InnoDB持有的內部鎖會導致性能降低(按我了解到的信息,在MySQL5.6中這得到了很大的改進).