MySQL數據庫服務器端核心參數詳解和推薦配置之二
2
MySQL手冊上也有服務器端參數的解釋，以及參數值的相關說明信息，現針對我們大家重點需要注意、需要修改或影響性能的服務器端參數，作其用處的
解釋和如何配置參數值的推薦，此事情拖了不少時間，為方便大家幫忙糾錯（注：總是斷斷續續寫的，可能存在筆誤），先以文章的方式發布到網站，后續確定沒問
題，將會通過新浪微博的微盤提供PPT下載，方便學習和參考。
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_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秒鐘；
文章出處：http://www.mysqlops.com/2011/10/26/mysql-variable-two.html
fmms
13年前提問