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倍甚至更多。

 

大家都知道，MySQL的複製（Replication），實際上就是通過將Master端的Binlog通過利用IO線程通過網路複製到Slave端，然後再通過SQL線程解析Binlog中的日誌再應用到資料庫中來實現的。所以，Binlog量的大小對IO線程以及Msater和Slave端之間的網路都會產生直接的影響。

MySQL中Binlog的產生量是沒辦法改變的，只要我們的Query改變了資料庫中的資料，那麼就必須將該Query所對應的Event記錄到Binlog中。那我們是不是就沒有辦法最佳化複製了呢？當然不是，在MySQL複製環境中，實際上是是有8個參數可以讓我們控制需要複製或者需要忽略而不進行複製的DB或者Table的，分別為：

Binlog_Do_DB：設定哪些資料庫（Schema）需要記錄Binlog；

Binlog_Ignore_DB：設定哪些資料庫（Schema）不要記錄Binlog；

Replicate_Do_DB：設定需要複製的資料庫（Schema），多個DB用逗號（“,”）分隔；

Replicate_Ignore_DB：設定可以忽略的資料庫（Schema）；

Replicate_Do_Table：設定需要複製的Table；

Replicate_Ignore_Table：設定可以忽略的Table；

Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以帶萬用字元來進行設定；

Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可帶萬用字元設定；

 

通過上面這八個參數，我們就可以非常方便按照實際需求，控制從Master端到Slave端的Binlog量儘可能的少，從而減小Master端到Slave端的網路流量，減少IO線程的IO量，還能減少SQL線程的解析與應用SQL的數量，最終達到改善Slave上的資料延時問題。

實際上，上面這八個參數中的前面兩個是設定在Master端的，而後面六個參數則是設定在Slave端的。雖然前面兩個參數和後面六個參數在功能上並沒有非常直接的關係，但是對於最佳化MySQL的Replication來說都可以啟到相似的功能。當然也有一定的區別，其主要區別如下：

如果在Master端設定前面兩個參數，不僅僅會讓Master端的Binlog記錄所帶來的IO量減少，還會讓Master端的IO線程就可以減少Binlog的讀取量，傳遞給Slave端的IO線程的Binlog量自然就會較少。這樣做的好處是可以減少網路IO，減少Slave端IO線程的IO量，減少Slave端的SQL線程的工作量，從而最大幅度的最佳化複製效能。當然，在Master端設定也存在一定的弊端，因為MySQL的判斷是否需要複製某個Event不是根據產生該Event的Query所更改的資料

 

所在的DB，而是根據執行Query時刻所在的預設Schema，也就是我們登入時候指定的DB或者運行“USEDATABASE”中所指定的DB。只有當前預設DB和配置中所設定的DB完全吻合的時候IO線程才會將該Event讀取給Slave的IO線程。所以如果在系統中出現在預設DB和設定需要複製的DB不一樣的情況下改變了需要複製的DB中某個Table的資料的時候，該Event是不會被複製到Slave中去的，這樣就會造成Slave端的資料和Master的資料不一致的情況出現。同樣，如果在預設Schema下更改了不需要複製的Schema中的資料，則會被複製到Slave端，當Slave端並沒有該Schema的時候，則會造成複製出錯而停止。

而如果是在Slave端設定後面的六個參數，在效能最佳化方面可能比在Master端要稍微遜色一點，因為不管是需要還是不需要複製的Event都被會被IO線程讀取到Slave端，這樣不僅僅增加了網路IO量，也給Slave端的IO線程增加了RelayLog的寫入量。但是仍然可以減少Slave的SQL線程在Slave端的日誌應用量。雖然效能方面稍有遜色，但是在Slave端設定複製過濾機制，可以保證不會出現因為預設Schema的問題而造成Slave和Master資料不一致或者複製出錯的問題。

 

Slow Query Log 相關參數及使用建議

再來看看SlowQueryLog的相關參數配置。有些時候，我們為了定位系統中效率比較地下的Query語句，則需要開啟慢查詢日誌，也就是SlowQueryLog。我們可以如下查看系統慢查詢日誌的相關設定：

mysql> show variables like 'log_slow%';+------------------+-------+| Variable_name | Value |+------------------+-------+| log_slow_queries | ON |+------------------+-------+1 row in set (0.00 sec)mysql> show variables like 'long_query%';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| long_query_time | 1 |+-----------------+-------+1 row in set (0.01 sec)

 

“log_slow_queries”參數顯示了系統是否已經開啟SlowQueryLog功能，而“long_query_time”參數則告訴我們當前系統設定的SlowQuery記錄執行時間超過多長的Query。在MySQLAB發行的MySQL版本中SlowQueryLog可以設定的最短慢查詢時間為1秒，這在有些時候可能沒辦法完全滿足我們的要求，如果希望能夠進一步縮短慢查詢的時間限制，可以使用Percona提供的microslow-patch（件成為mslPatch）來突破該限制。mslpatch不僅僅能將慢查詢時間減小到毫秒層級，同時還能通過一些特定的規則來過濾記錄的SQL，如僅記錄涉及到某個表的SlowQuery等等附加功能。考慮到篇幅問題，這裡就不介紹mslpatch給我們帶來的更為詳細的功能和使用，大家請參考官方介紹（http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/）

開啟SlowQueryLog功能對系統效能的整體影響沒有Binlog那麼大，畢竟SlowQueryLog的資料量比較小，帶來的IO損耗也就較小，但是，系統需要計算每一條Query的執行時間，所以消耗總是會有一些的，主要是CPU方面的消耗。如果大家的系統在CPU資源足夠豐富的時候，可以不必在乎這一點點損耗，畢竟他可能會給我們帶來更大效能最佳化的收穫。但如果我們的CPU資源也比較緊張的時候，也完全可以在大部分時候關閉該功能，而只需要間斷性的開啟SlowQueryLog功能來定位可能存在的慢查詢。

MySQL的其他日誌由於使用很少（QueryLog）或者效能影響很少，我們就不在此過多分析了，至於各個儲存引擎相關的日誌，我們留在後面“常用儲存引擎最佳化”部分再做相應的分析。

 

轉自 《MySQL效能調優與架構設計》