MySQL5.6基本設(shè)置詳細說明

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

InnoDB設(shè)置

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

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

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

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

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

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

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

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

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

復(fù)制(Replication)

假如服務(wù)器要支持主從復(fù)制,或按時間點恢復(fù),在這種情況下,我們需要:

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

2.expire-logs-days —— 默認舊日志會一直保留. 我推薦設(shè)置為 1-10 天. 保存更長的時間并沒有太多用處,因為從備份中恢復(fù)會快得多.

3.server-id —— 在一個主從復(fù)制體系(replication topology )中的所有服務(wù)器都必須設(shè)置唯一的 server-id.

4.binlog_format=ROW —— 修改為基于行的復(fù)制. 我最近寫的另一篇 基于行的復(fù)制 ,里面敘述了我真的很喜歡它的原因,因為它可以通過減少資源鎖定提高性能. 此外還需要啟用兩個附加設(shè)置: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.

其他配置(Misc)

1.timezone=GMT 將時區(qū)設(shè)置為格林尼治時間. 越來越多的系統(tǒng)管理員建議將所有服務(wù)器都設(shè)置為 格林尼治時間(GMT). 我個人非常喜歡這點,因為現(xiàn)在幾乎所有的業(yè)務(wù)都是全球化的. 設(shè)置為你本地的時區(qū)似乎是有點武斷的.

2.character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci 如之前的 文章所講述的 ,utf8 編碼對新應(yīng)用來說是更好的默認選項. 您還可以設(shè)置 skip-character-set-client-handshake 以忽略應(yīng)用程序想要設(shè)置的其他字符集(character-set).

3.sql-mode —— MySQL默認對不規(guī)范的數(shù)據(jù)很寬容,并且會靜默地截斷數(shù)據(jù). 在我 之前的一篇文章中, 我提到新應(yīng)用程序最好設(shè)置為：

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解析在某些系統(tǒng)上可能有點慢/不穩(wěn)定,所以如果不需要基于主機名的授權(quán),我建議避免這種解析.

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

防火墻是更合適的解決方案,通常我將3306端口屏蔽,不管是公網(wǎng)的還是內(nèi)網(wǎng)的端口,只有特定的應(yīng)用程序可以訪問和連接到MySQL.
我通常會設(shè)置 max_connect_errors=100000, 這樣我可以避免任何“雙重配置”,保證它不會礙事.

6.max-connections ——默認值是151. 我看到很多用戶將他設(shè)置得比較大,大多在 300 ~ 1000之間.
通常不可避免地這個值會被設(shè)置得更大,但讓我有點緊張的是, 16核的機器在IO阻塞的情況下也只有大約 2x~10x 的連接執(zhí)行能力.
你可能希望,許多打開的連接都是空閑并休眠的. 但如果他們都處于活躍狀態(tài)的話,可能會創(chuàng)建大量新的線程(thread-thrash).
如果條件允許,可以為應(yīng)用程序配置優(yōu)化數(shù)據(jù)庫連接池(connection-pools)來解決這個問題,而不是打開并保持大量連接;
當然那些不使用連接池(non-pooled ), 迅速打開,執(zhí)行任務(wù)后又盡可能快地關(guān)閉連接的應(yīng)用也是可行的.
從5.5開始的另一種解決方案(在MySQL社區(qū)版和企業(yè)版之間有一些差異) 是使用 線程池插件.

總結(jié)(Conclusion)

假設(shè)MySQL服務(wù)器的配置為:
1.64GB物理內(nèi)存
2.硬件RAID控制器(假設(shè)每秒IO可達 2000 IOPS)
3.需要主從復(fù)制(Replication)
4.新的應(yīng)用(eg. 非遺留系統(tǒng))
5.有防火墻保護
6.不需要基于域名(hostnames,主機名)的授權(quán)
7.全球化應(yīng)用,并不想固定在某一時區(qū).
8.想要程序可靠穩(wěn)定(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基本配置詳解的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！