MySQL 5.6初始配置調優

原文日期: 2013年09月17日

翻譯日期: 2014年06月01日

InnoDB設定

如果将該值設定得更大可能會存在風險,比如沒有足夠的空閑記憶體留給作業系統和依賴檔案系統緩存的某些MySQL子系統(subsystem),包括二進制日志(binary logs),InnoDB事務日志(transaction logs)等.

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

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

将這兩個值設定得太大可能會存在某些風險,你肯定不希望背景操作妨礙了前台任務IO操作的性能. 過去的經驗表明,将這兩個值設定的太高,InnoDB持有的内部鎖會導緻性能降低(按我了解到的資訊,在MySQL5.6中這得到了很大的改進).

複制(Replication)

假如伺服器要支援主從複制,或按時間點恢複,在這種情況下,我們需要:

其他配置(Misc)

NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,

NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

防火牆是更合适的解決方案,通常我将3306端口屏蔽,不管是公網的還是内網的端口,隻有特定的應用程式可以通路和連接配接到MySQL. 

我通常會設定 max_connect_errors=100000, 這樣我可以避免任何“雙重配置”,保證它不會礙事.

通常不可避免地這個值會被設定得更大,但讓我有點緊張的是, 16核的機器在IO阻塞的情況下也隻有大約 2x~10x 的連接配接執行能力. 

你可能希望,許多打開的連接配接都是空閑并休眠的. 但如果他們都處于活躍狀态的話,可能會建立大量新的線程(thread-thrash).

如果條件允許,可以為應用程式配置優化資料庫連接配接池(connection-pools)來解決這個問題,而不是打開并保持大量連接配接; 

當然那些不使用連接配接池(non-pooled ), 迅速打開,執行任務後又盡可能快地關閉連接配接的應用也是可行的. 

總結(Conclusion)

假設MySQL伺服器的配置為:

64GB實體記憶體

硬體RAID控制器(假設每秒IO可達 2000 IOPS)

需要主從複制(Replication)

新的應用(eg. 非遺留系統)

有防火牆保護

不需要基于域名(hostnames,主機名)的授權

全球化應用,并不想固定在某一時區.

則配置可能如下所示:

希望本文說清了主要的問題. 如果有其他建議,請聯系原作者.