MySQL5.6基本配置詳解

這篇文章主要介紹了MySQL5.6基本優化配置,詳細分解了MySQL5.6需要優化的配置項,最終給出了一個優化案例,需要的朋友可以參考下

隨著大量預設選項的改進, 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中這得到了很大的改進).

innodb_lru_scan_depth -默認值為1024. 這是mysql 5.6中引入的一個新選項. Mark Callaghan  提供了一些配置建議. 簡單來說,如果增大了innodb_io_capacity 值, 應該同時增加innodb_lru_scan_depth.

複製(Replication)

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

##1.log -bin —— 啟用二進位日誌. 預設情況下二進位日誌不是事故安全的(not crash safe),但如同我以前的文章所說, 我建議大多數用戶應該以穩定性為目標. 在這種情況下,你還需要啟用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.

2.expire-logs-days —— 預設舊日誌會一直保留. 我推薦設定為1-10 天. 保存更長的時間並沒有太多用處,因為從備份中恢復會快得多.

#3.server-id —— 在一個主從複製系統(replication topology )中的所有伺服器都必須設定唯一的server-id.

4.binlog_format=ROW  —— 修改為基於行的複製. 我最近寫的另一篇基於行的複製,裡面敘述了我真的很喜歡它的原因,因為它可以透過減少資源鎖定來提高效能. 另外還需要啟用兩個附加設定:  transaction-isolation=READ-COMMITTED and  innodb_autoinc_lock_mode = 2.

其他設定(Misc)

1. timezone=GMT  將時區設定為格林尼治時間. 越來越多的系統管理員建議將所有伺服器都設定為格林尼治時間(GMT). 我個人非常喜歡這點,因為現在幾乎所有的業務都是全球化的. 設定為你本地的時區似乎是有點武斷的.

2.character-set-server=utf8mb4 and collat​​ion-server=utf8mb4_general_ci  如之前的文章所講述的, utf8 編碼對新應用來說是更好的預設選項. 您也可以設定skip-character-set-client-handshake 以忽略應用程式想要設定的其他字元集(character-set).

3.sql-mode —— MySQL預設對不規範的資料很寬容,並且會靜默地截斷資料. 在我之前的一篇文章中, 我提到新應用程式最好設定為：  

STRICT_TRANS_TABLES,ERROR_FOR_pISION_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.

4.skip-name-resolve —— 停用反向網域解析. DNS解析在某些系統上可能有點慢/不穩定,所以如果不需要基於主機名稱的授權,我建議避免這種解析.

5.max_connect_errors —— Todd Farmer 寫道:“[這個功能]提供了沒有實際意義的暴力訪問攻擊保護」. 事實上當設定skip-name-resolve 時, max_connect_errors 甚至不起作用(見上一段所述).

 防火牆是更合適的解決方案,通常我將3306埠屏蔽,不管是公網的還是內網的連接埠,只有特定的應用程式可以存取和連接到MySQL.
 我通常會設定max_connect_errors=100000, 這樣我可以避免任何「雙重設定」,保證它不會礙事.

6.max-connections ——預設值是151. 我看到很多用戶將他設定得比較大,大多在300 ~ 1000之間.
 通常不可避免地這個值會被設定得更大,但讓我有點緊張的是, 16核的機器在IO阻塞的情況下也只有大約2x~10x 的連接執行能力.
 你可能希望,許多打開的連接都是空閒並休眠的. 但如果他們都處於活躍狀態的話,可能會創建大量新的線程(thread-thrash).
 如果條件允許,可以為應用程序配置優化數據庫連接池(connection-pools)來解決這個問題,而不是打開並保持大量連接;
 當然那些不使用連接池(non-pooled ), 迅速打開,執行任務後又盡可能快地關閉連接的應用也是可行的.
 從5.5開始的另一個解決方案(在MySQL社群版和企業版之間有一些差異) 是使用執行緒池外掛程式.

總結(Conclusion)

假設MySQL伺服器的設定為:
1.64GB實體記憶體
2.硬體RAID控制器(假設每秒IO可達2000 IOPS)
3.需要主從複製(Replication)
4.新的應用(eg. 非遺留系統)
5.有防火牆保護
6.不需要基於網域名稱(hostnames,主機名稱)的授權
7.全球化應用程式,並不想固定在某一時區.
8.想要程式可靠且穩定(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

以上是MySQL5.6基本配置詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！