mysql binlog日誌最佳化及思路

標籤：帶來   edm   patch   host   get   format   配置   ges   blank   

在資料庫安裝完畢，對於binlog日誌參數設定，有一些參數的調整，來滿足業務需求或使效能最大化。Mysql日誌主要對io效能產生影響，本次主要關注binlog 日誌。
 
查一下二進位日誌相關的參數  
 
mysql> show variables like ‘%binlog%‘;
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+

 
其中innodb_locks_unsafe_for_binlog 參數是innodb儲存引擎特有的與binlog相關的參數，預設是關閉的
 
binlog_cache_size:預設大小是37268即32K.在事務過程中容納二進位日誌SQL語句的緩衝大小。二進位日誌緩衝是伺服器支援事務儲存引擎並且伺服器啟用了二進位日誌(―log-bin選項)的前提下為每個用戶端分配的記憶體，

注意，是每個Client都可以分配設定大小的binlogcache空間。如果讀者朋友的系統中經常會出現多語句事務的華，可以嘗試增加該值的大小，以獲得更有的效能;

我們可以通過以下兩個狀態變數判斷binlog_cache_size的使用方式：
 
1)binlog_cache_use: The number of transactions that used the temporary binary log cache.

  ----使用二進位日誌緩衝的事務的數量

2)binlog_cache_disk_use: The number of transactions that used the binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store changes from the transaction.
 
  ----使用二進位日誌緩衝並且值達到了binlog_cache_size設定的值，用臨時檔案儲存體來自事務的變化這樣的事務數量

==

我們看看設定檔：

[@hostname ~]# grep ‘binlog‘ /data/mydata/my3306/my3306.cnf
binlog_cache_size = 64M  
binlog_format = mixed
max_binlog_cache_size = 128M
max_binlog_size = 200M
sync_binlog = 0

[@hostname ~]#

我們看看配置64M是否夠用？查看下運行情況：

mysql> show status like ‘binlog_%‘;
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Binlog_cache_disk_use | 0         |
| Binlog_cache_use      | 120402264 |
+-----------------------+-----------+
2 rows in set (0.00 sec)

運行情況Binlog_cache_use 表示binlog_cache記憶體方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache臨時檔案方式被用上了多少次。

Binlog_cache_disk_use現在等於0，表示記憶體cache是夠用的，從來不需要使用到臨時檔案。如果沒有long_transaction的話，覺得64M是偏大的。

---
 
Max_binlog_cache_size: 預設值是18446744073709547520，這個值很大，夠我們使用的了。此參數和binlog_cache_size相對應，是所代表的是binlog能夠使用的最大cache記憶體大小。

當我們執行多語句事務的時候，max_binlog_cache_size如果不夠大的話，系統可能會報出“Multi-statementtransactionrequiredmorethan‘max_binlog_cache_size‘bytesofstorage”的錯誤。

Max_binlog_size: 1073741824=1G  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倍甚至更多。

==========

Slow Query Log 相關參數及使用建議
--
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效能調優與架構設計》

mysql binlog日誌最佳化及思路