MySQL日誌設定最佳化

前言

在安裝完MySQL之後，肯定是需要對MySQL的各種參數選項進行一些最佳化調整的。雖然MySQL系統的伸縮性很強，既可以在有很充足的硬體資源環境下高效的運行，也可以在極少資源環境下很好的運行，但不管怎樣，儘可能充足的硬體資源對MySQL的效能提升總是有協助的。在這一節我們主要分析一下MySQL的日誌（主要是Binlog）對系統效能的影響，並根據日誌的相關特性得出相應的最佳化思路。

日誌產生的效能影響

由於日誌的記錄帶來的直接效能損耗就是資料庫系統中最為昂貴的IO資源。

在之前介紹MySQL物理架構的章節中，我們已經瞭解到了MySQL的日誌包括錯誤記錄檔（ErrorLog），更新日誌（UpdateLog），二進位日誌（Binlog），查詢日誌（QueryLog），慢查詢日誌（SlowQueryLog）等。當然，更新日誌是老版本的MySQL才有的，目前已經被二進位日誌替代。

在預設情況下，系統僅僅開啟錯誤記錄檔，關閉了其他所有日誌，以達到儘可能減少IO損耗提高系統效能的目的。但是在一般稍微重要一點的實際應用情境中，都至少需要開啟二進位日誌，因為這是MySQL很多儲存引擎進行增量備份的基礎，也是MySQL實現複製的基本條件。有時候為了進一步的效能最佳化，定位執行較慢的SQL語句，很多系統也會開啟慢查詢日誌來記錄執行時間超過特定數值（由我們自行設定）的SQL語句。

一般情況下，在生產系統中很少有系統會開啟查詢日誌。因為查詢日誌開啟之後會將MySQL中執行的每一條Query都記錄到日誌中，會該系統帶來比較大的IO負擔，而帶來的實際效益卻並不是非常大。一般只有在開發測試環境中，為了定位某些功能具體使用了哪些SQL語句的時候，才會在短時間段內開啟該日誌來做相應的分析。所以，在MySQL系統中，會對效能產生影響的MySQL日誌（不包括各儲存引擎自己的日誌）主要就是Binlog了。

Binlog 相關參數及最佳化策略

我們首先看看Binlog的相關參數，通過執行如下命令可以獲得關於Binlog的相關參數。當然，其中也顯示出了“innodb_locks_unsafe_for_binlog”這個Innodb儲存引擎特有的與Binlog相關的參數：

mysql> show variables like '%binlog%'; +--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size| 4294967295 |
| max_binlog_size| 1073741824 |
| sync_binlog| 0|
+--------------------------------+------------+

“binlog_cache_size"：在事務過程中容納二進位日誌SQL語句的緩衝大小。二進位日誌緩衝是伺服器支援事務儲存引擎並且伺服器啟用了二進位日誌(—log-bin選項)的前提下為每個用戶端分配的記憶體，注意，是每個Client都可以分配設定大小的binlogcache空間。如果讀者朋友的系統中經常會出現多語句事務的華，可以嘗試增加該值的大小，以獲得更有的效能。當然，我們可以通過MySQL的以下兩個狀態變數來判斷當前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use。

“max_binlog_cache_size”：和"binlog_cache_size"相對應，但是所代表的是binlog能夠使用的最大cache記憶體大小。當我們執行多語句事務的時候，max_binlog_cache_size如果不夠大的話，系統可能會報出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯誤。

“max_binlog_size”：Binlog日誌最大值，一般來說設定為512M或者1G，但不能超過1G。該大小並不能非常嚴格控制Binlog大小，尤其是當到達Binlog比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進入當前日誌，直到該事務結束。這一點和Oracle的Redo日誌有點不一樣，因為Oracle的Redo日誌所記錄的是資料檔案的物理位置的變化，而且裡面同時記錄了Redo和Undo相關的資訊，所以同一個事務是否在一個日誌中對Oracle來說並不關鍵。而MySQL在Binlog中所記錄的是資料庫邏輯變化資訊，MySQL稱之為Event，實際上就是帶來資料庫變化的DML之類的Query語句。

“sync_binlog”：這個參數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的效能損耗，而且還影響到MySQL中資料的完整性。對於“sync_binlog”參數的各種設定的說明如下：

sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。

sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。

在MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟排清指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失。而當設定為“1”的時候，是最安全但是效能損耗最大的設定。因為當設定為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際資料沒有任何實質性影響。從以往經驗和相關測試來看，對於高並發事務的系統來說，“sync_binlog”設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。

1 2 3 下一頁