原文鏈接: What to tune in MySQL 5.6 after installation
隨著 大量默認選項的改進, MySQL 5.6比以前版本需要調優的選項大為減少. 在本文中我將講述需要優化的配置項.
InnoDB設置
innodb_buffer_pool_size —— 默認值為 128M. 這是最主要的優化選項,因為它指定 InnoDB 使用多少內存來加載數據和索引(data+indexes). 針對專用MySQL服務器,建議指定為物理內存的 50-80%這個范圍. 例如,擁有64GB物理內存的機器,緩存池應該設置為50GB左右.
innodb_log_file_size —— 默認值為 48M. 有很高寫入吞吐量的系統需要增加該值以允許後台檢查點活動在更長的時間周期內平滑寫入,得以改進性能. 將此值設置為4G以下是很安全的. 過去的實踐表明,日志文件太大的缺點是增加了崩潰時所需的修復時間,但這在5.5和5.6中已得到重大改進.innodb_flush_method —— 默認值為 fdatasync. 如果使用 硬件RAID磁盤控制器, 可能需要設置為 O_DIRECT. 這在讀取InnoDB緩沖池時可防止“雙緩沖(double buffering)”效應,否則會在文件系統緩存與InnoDB緩存間形成2個副本(copy).
MySQL用戶手冊 和 Bug #54306 詳細地說明了這一點.innodb_flush_neighbors —— 默認值為 1. 在SSD存儲上應設置為0(禁用) ,因為使用順序IO沒有任何性能收益. 在使用RAID的某些硬件上也應該禁用此設置,因為邏輯上連續的塊在物理磁盤上並不能保證也是連續的.innodb_io_capacity and innodb_io_capacity_max —— 這些設置會影響InnoDB每秒在後台執行多少操作. 在 以前的一篇文章 裡我描述了大多數寫IO(除了寫InnoDB日志)是後台操作的. 如果你深度了解硬件性能(如每秒可以執行多少次IO操作),則使用這些功能是很可取的,而不是讓它閒著. 有一個很好的類比示例: 假如某次航班一張票也沒有賣出去 —— 那麼讓稍後航班的一些人乘坐該次航班,有可能是很好的策略,以防後面遇到惡劣的天氣. 即有機會就將後台操作順便處理了,以減少同稍後可能的實時操作產生競爭.
innodb_lru_scan_depth - 默認值為 1024. 這是mysql 5.6中引入的一個新選項. Mark Callaghan 提供了 一些配置建議. 簡單來說,如果增大了 innodb_io_capacity 值, 應該同時增加 innodb_lru_scan_depth.復制(Replication)
以前的文章所說, 我建議大多數用戶應該以穩定性為目標. 在這種情況下,你還需要啟用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.expire-logs-days —— 默認舊日志會一直保留. 我推薦設置為 1-10 天. 保存更長的時間並沒有太多用處,因為從備份中恢復會快得多.server-id —— 在一個主從復制體系(replication topology )中的所有服務器都必須設置唯一的 server-id.binlog_format=ROW —— 修改為基於行的復制. 我最近寫的另一篇 基於行的復制 ,裡面敘述了我真的很喜歡它的原因,因為它可以通過減少資源鎖定提高性能. 此外還需要啟用兩個附加設置: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.其他配置(Misc)
timezone=GMT 將時區設置為格林尼治時間. 越來越多的系統管理員建議將所有服務器都設置為 格林尼治時間(GMT). 我個人非常喜歡這點,因為現在幾乎所有的業務都是全球化的. 設置為你本地的時區似乎是有點武斷的.character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci 如之前的 文章所講述的 ,utf8 編碼對新應用來說是更好的默認選項. 您還可以設置 skip-character-set-client-handshake 以忽略應用程序想要設置的其他字符集(character-set).sql-mode —— MySQL默認對不規范的數據很寬容,並且會靜默地截斷數據. 在我 之前的一篇文章中, 我提到新應用程序最好設置為： STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,
skip-name-resolve —— 禁用反向域名解析. DNS解析在某些系統上可能有點慢/不穩定,所以如果不需要基於主機名的授權,我建議避免這種解析.max_connect_errors —— Todd Farmer 寫道 :“[這個功能]提供了沒有實際意義的暴力訪問攻擊保護”. 事實上當設置skip-name-resolve 時, max_connect_errors 甚至不起作用(見上一段所述). 防火牆是更合適的解決方案,通常我將3306端口屏蔽,不管是公網的還是內網的端口,只有特定的應用程序可以訪問和連接到MySQL.
max_connect_errors=100000, 這樣我可以避免任何“雙重配置”,保證它不會礙事.
max-connections ——默認值是151. 我看到很多用戶將他設置得比較大,大多在 300 ~ 1000之間. 通常不可避免地這個值會被設置得更大,但讓我有點緊張的是, 16核的機器在IO阻塞的情況下也只有大約 2x~10x 的連接執行能力.
從5.5開始的另一種解決方案(在MySQL社區版和企業版之間有一些差異) 是使用 線程池插件.
總結(Conclusion)
可靠穩定(durable).
則配置可能如下所示:
# InnoDB settings innodb_buffer_pool_size=50G innodb_log_file_size=2G innodb_flush_method=O_DIRECT innodb_io_capacity=2000 innodb_io_capacity_max=6000 innodb_lru_scan_depth=2000 # Binary log/replication log-bin sync_binlog=1 sync_relay_log=1 relay-log-info-repository=TABLE master-info-repository=TABLE expire_logs_days=10 binlog_format=ROW transaction-isolation=READ-COMMITTED innodb_autoinc_lock_mode = 2 # Other timezone=GMT character-set-server=utf8 collation-server=utf8_general_ci sql-mode="STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY" skip-name_resolve max-connect-errors=100000 max-connections=500 # Unique to this machine server-id=123
希望本文說清了主要的問題. 如果有其他建議,請聯系原作者.
