《Mysql必讀MySQL數據庫服務器端核心參數詳解和推薦配置》要點：
本文介紹了Mysql必讀MySQL數據庫服務器端核心參數詳解和推薦配置，希望對您有用。如果有疑問，可以聯系我們。

MySQL手冊上也有服務器端參數的解釋,以及參數值的相關說明信息,現針對我們大家重點需要注意、需要修改或影響性能 的服務器端參數,作其用處的解釋和如何配置參數值的推薦,此事情拖了不少時間,為方便大家幫忙糾錯(注：總是斷斷續續寫的,可能存在筆誤),先以文章的方 式發布到網站,后續確定沒問題,將會通過新浪微博的微盤提供PPT下載,方便學習和參考.
l lower_case_table_names
Linux或類Unix平臺,對文件名稱大小寫敏感,也即對數據庫、表、存儲過程等對象名稱大小寫敏 感,為減少開發人員的開發成本,為此推薦大家設置該參數使對象名稱都自動轉換成小寫；
l max_connect_errors
max_connect_errors默認值為10,也即mysqld線程沒重新啟動過,一臺物理服務器只要連接 異常中斷累計超過10次,就再也無法連接上mysqld服務,為此建議大家設置此值至少大于等于10W； 若異常中斷累計超過參數設置的值,有二種解決辦法,執行命令：FLUSH HOSTS;或者重新啟動mysqld服務；

l interactive_timeout and wait_timeout
u interactive_timeout
處于交互狀態連接的活動被服務器端強制關閉,而等待的時間,單位：秒；
u wait_timeout
與服務器端無交互狀態的連接,直到被服務器端強制關閉而等待的時間,此參數只對基于TCP/IP或基于 Socket通信協議建立的連接才有效,單位：秒；
u 推薦設置
interactive_timeout = 172800
wait_timeout = 172800
l transaction-isolation and binlog-format
u transaction-isolation
可供設置的值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、
SERIALIZABLE,默認的值為： REPEATABLE-READ,事務隔離級別設置的不同,對二進制日志登記格
式影響非常大,詳細信息可見文章解讀MySQL事務的隔離級別和日志登記模式選擇技巧；
u binlog-format
復制的模式,可供設置的值：STATEMENT、ROW、MIXED(注：5.0.*只有命令行式復制),
5.1.*版本默認設置：MIXED；
u 推薦配置
① 只讀為主的業務應用場景
transaction-isolation = read-committed
binlog-format = mixed #5.1.*版本,5.0.*只能設置為 statement
① 非只讀為主的業務應用場景
transaction-isolation = repeatabled-read
binlog-format = mixed #5.1.*版本,5.0.*只能設置為 statement
l event_scheduler
事務調度默認是關閉狀態,也推薦源碼編譯的版本可不編譯進來,以及實際生產環境保持默認禁用 狀態,當真正需要用的時候,可以臨時打開,命令：SET GLOBAL event_scheduler=1;
l skip_external_locking
外部鎖,也即操作系統所實施的鎖,只對MyISAM引擎有效,且容易造成死鎖發生,為此我們一律禁用；
l innodb_adaptive_hash_index
InnoDB引擎會根據數據的訪問頻繁度,把表的數據逐漸緩到內存,若是一張表的數據大量緩存在 內存中,則使用散列索引(注：Hash Index)會更高效.InnoDB內有Hash Index機制,監控數據的訪 問情況,可以自動創建和維護一個Hash Index,以提供訪問效率,減少內存的使用；
l innodb_max_dirty_pages_pct
InnoDB主線程直接更新Innodb_buffer_pool_size中存在的數據,并且不實時刷回磁盤,而是等待 相關的處罰事件發生,則允許緩存空間的數據量不實時刷回磁盤的最大百分比.比例設置較小,有利于 減少mysqld服務出現問題的時候恢復時間,缺點則是需要更多的物理I/O,為此我們必須根據業務特點 和可承受范圍進行一個折中,一般范圍建議設置為5%~90%,像我們SNS游戲行業的寫非常厲害,綜合 各方面因素,設置為20%；
l innodb_commit_concurrency
含義：同一時刻,允許多少個線程同時提交InnoDB事務,默認值為0,范圍0-1000.
0 ― 允許任意數量的事務在同一時間點提交；
N>0 ― 允許N個事務在同一時間點提交；
注意事項：
① mysqld提供服務時,不許把 innodb_commit_concurrency 的值從0改為非0,或非0的值改為0；

② mysqld提供時,允許把 innodb_commit_concurrency 的值N>0改為M,且M>0；
l innodb_concurrency_tickets
含義：
同一時刻,能訪問InnoDB引擎數據的線程數,默認值為500,范圍1-4294967295.
補充說明：當訪問InnoDB引擎數據的線程數達到設置的上線,線程將會被放到隊列中,等待其他線程釋放ticket.
建議：
MySQL數據庫服務最大線程連接數參數max_connections,一般情況下都會設置在128-1024的范圍,再結合實際業務可能的最大事務并發度,innodb_concurrency_tickets保持默認值一般情況下足夠.
l innodb_fast_shutdown and innodb_force_recovery
innodb_fast_shutdown：
含義：設置innodb引擎關閉的方式,默認值為：1,正常關閉的狀態；
0 ― mysqld服務關閉前,先進行數據完全的清理和插入緩沖區的合并操作,若是臟數據
較多或者服務器性能等因素,會導致此過程需要數分鐘或者更長時間；
1 ― 正常關閉mysqld服務,針對innodb引擎不做任何其他的操作；
2 ― 若是mysqld出現崩潰,立即刷事務日志到磁盤上并且冷關閉mysqld服務；沒有提交
的事務將會丟失,但是再啟動mysqld服務的時候會進行事務回滾恢復；
innodb_force_recovery：
含義：
mysqld服務出現崩潰之后,InnoDB引擎進行回滾的模式,默認值為0,可設置的值0~6；
提示：
只有在需要從錯誤狀態的數據庫進行數據備份時,才建議設置innodb_force_recovery的值大于0. 若是把此參數作為安全選項,也可以把參數的值設置大于0,防止InnoDB引擎的數據變更,設置不同值的作用：
0 ― 正常的關閉和啟動,不會做任何強迫恢復操作；
1 ― 跳過錯誤頁,讓mysqld服務繼續運行.跳過錯誤索引記錄和存儲頁,嘗試用
SELECT * INOT OUTFILE ‘../filename' FROM tablename;方式,完成數據備份；
2 ― 阻止InnoDB的主線程運行.清理操作時出現mysqld服務崩潰,則會阻止數據恢復操作；
3 ― 恢復的時候,不進行事務回滾；
4 ― 阻止INSERT緩沖區的合并操作.不做合并操作,為防止出現mysqld服務崩潰.不計算
表的統計信息
5 ― mysqld服務啟動的時候不檢查回滾日志：InnoDB引擎對待每個不確定的事務就像提交
的事務一樣；
6 ― 不做事務日志前滾恢復操作；
推薦的參數組合配置：
innodb_fast_shutdown = 1
#若是機房條件較好可設置為0(雙路電源、UPS、RAID卡電池和供電系統穩定性)
innodb_force_recovery =0
#至于出問題的時候,設置為何值,要視出錯的原因和程度,對數據后續做的操作
l innodb_additional_mem_pool_size
含義：開辟一片內存用于緩存InnoDB引擎的數據字典信息和內部數據結構(比如：自適應HASH索引結構)；
默認值：build-in版本默認值為：1M；Plugin-innodb版本默認值為：8M；
提示：若是mysqld服務上的表對象數量較多,InnoDB引擎數據量很大,且 innodb_buffer_pool_size的值設置 較大,則應該適當地調整innodb_additional_mem_pool_size的值.若是出現緩存區的內存不足,則會直接向操作系統申請內存分 配,并且會向MySQL的error log文件寫入警告信息；
l innodb_buffer_pool_size
含義：開辟一片內存用于緩存InnoDB引擎表的數據和索引；
默認值：歷史默認值為：8M,現在版本默認值為：128M；
參數最大值：受限于CPU的架構,支持32位還是支持64位,另外還受限于操作系統為32位還是64位；
提示：
innodb_buffer_pool_size的值設置合適,會節約訪問表對象中數據的物理IO.官方手冊上建議專用 的數據庫服務器,可考慮設置為物理內存總量的80%,但是個人建議要看物理服務器的物理內存總量,以及考慮： 是否只使用InnoDB引擎、mysqld內部管理占用的內存、最大線程連接數和臨時表等因素,官方提供的80%值作為一個參考,舉而個例子方便大家作決 定(前提：物理服務器為mysqld服務專用,且只用InnoDB引擎,假設數據量遠大于物理內存)：
1).內存配置：24G 則 innodb_buffer_pool_size=18G
1).內存配置：32G 則 innodb_buffer_pool_size=24G
出現下列哪些情況,則可以考慮減小innodb_buffer_pool_size的值：
1).出現物理內存的競爭,可能導致操作系統的分頁；
2).InnoDB預分配額外的內存給緩沖區和結構管理,當分配的總內存量超過innodb_buffer_pool_size值的10%；
3).地址空間要求必須為連續的,在windows系統有一個嚴重問題,DLL需要加載在特定的地址空間；
4).初始化緩沖區的時間消耗,與緩沖區的大小成正比.官方提供的數據 Linux X86 64位系統 初始化 innodb_buffer_pool_size=10G 大概需要6秒鐘；
l innodb_flush_log_at_trx_commit AND sync_binlog
innodb_flush_log_at_trx_commit = N：
N=0 C 每隔一秒,把事務日志緩存區的數據寫到日志文件中,以及把日志文件的數據刷新到磁盤上；
N=1 C 每個事務提交時候,把事務日志從緩存區寫到日志文件中,并且刷新日志文件的數據到磁盤上；

N=2 C 每事務提交的時候,把事務日志數據從緩存區寫到日志文件中；每隔一秒,刷新一次日志文件,但不一定刷新到磁盤上,而是取決于操作系統的調度；
sync_binlog = N：
N>0 ― 每向二進制日志文件寫入N條SQL或N個事務后,則把二進制日志文件的數據刷新到磁盤上；
N=0 ― 不主動刷新二進制日志文件的數據到磁盤上,而是由操作系統決定；
推薦配置組合：
N=1,1 ― 適合數據安全性要求非常高,而且磁盤IO寫能力足夠支持業務,比如充值消費系統；
N=1,0 ― 適合數據安全性要求高,磁盤IO寫能力支持業務不富余,允許備庫落后或無復制；
N=2,0或2,m(0<m<100) ― 適合數據安全性有要求,允許丟失一點事務日志,復制架構的延遲也能接受；
N=0,0 ― 磁盤IO寫能力有限,無復制或允許復制延遲稍微長點能接受,例如：日志性登記業務；
l innodb_file_per_table
啟用單表空間,減少共享表空間維護成本,減少空閑磁盤空間釋放的壓力.另外,大數據量情況下 的性能,也會有性能上的提升,為此建議大家使用獨立表空間 代替 共享表空間的方式；
l key_buffer_size
key_buffer_size只能緩存MyISAM或類MyISAM引擎的索引 數據,而innodb_buffer_pool_size不僅能緩存索引數據,還能緩存元數據,但是對于我們只使用InnoDB引擎的數據庫系統而言,此 參數值也不能設置過于偏小,因為臨時表可能會使用到此鍵緩存區空間,索引緩存區推薦：64M；
l query_cache_type and query_cache_size
n query_cache_type=N
N=0 ―- 禁用查詢緩存的功能；
N=1 ―- 啟用產訊緩存的功能,緩存所有符合要求的查詢結果集,除SELECT SQL_NO_CACHE.., 以及不符合查詢緩存設置的結果集外；
N=2 ―- 僅僅緩存SELECT SQL_CACHE …子句的查詢結果集,除不符合查詢緩存設置的結果集外；
n query_cache_size
查詢緩存設置多大才是合理?至少需要從四個維度考慮：
① 查詢緩存區對DDL和DML語句的性能影響；
② 查詢緩存區的內部維護成本；
③ 查詢緩存區的命中率及內存使用率等綜合考慮
④ 業務類型

《Mysql必讀MySQL數據庫服務器端核心參數詳解和推薦配置》是否對您有啟發，歡迎查看更多與《Mysql必讀MySQL數據庫服務器端核心參數詳解和推薦配置》相關教程，學精學透。維易PHP學院為您提供精彩教程。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/13272.html