[翻譯]——MySQL Server Variable: sync_binlog (Doc ID 1501926.1)

本文對MySQL Server Variable: sync_binlog (Doc ID 1501926.1)這篇文章進行了翻譯，如有翻譯不當或錯誤的地方敬請指正，

  譯文地址：https://www.cnblogs.com/kerrycode/p/14167941.html

In this Document

Purpose

Scope

Details

   Performance Impact of sync_binlog

   Recommendation

References

APPLIES TO:

MySQL Server - Version 4.1 to 5.6 [Release 4.1 to 5.6]
Information in this document applies to any platform.

PURPOSE

Explain when and why to use the sync_binlog setting in MySQL Server.

解釋在MySQL Server中什么時候設定引數sync_binglog以及為什么要設定引數sync_binglog

SCOPE

Detailed general guidance for DBAs who are familiar with the option and looking for help on how to use it.

針對熟悉該選項或尋求有關使用該選項使用幫助的DBA的詳細的一份常規指南，

DETAILS

The sync_binlog setting was added in MySQL 4.1.3 and specifies how often MySQL Server synchronizes its binary log to disk. The supported values are 0 and larger with the maximum value being 4294967295 on 32-bit platform and 18446744073709547520 on 64-bit platforms. The meaning of the value of sync_binlog is:

從MySQL 4.1.3開始新增了sync_binlog引數設定，它控制了MySQL Server將其二進制日志同步到磁盤的頻率， 在32位平臺上，它支持的取值范圍為0到4294967295，最大值為4294967295，在64位平臺上，它支持的取值范圍為0到18446744073709547520，最大值為18446744073709547520， sync_binlog的值的含義如下：

In the following the word transaction is used even if not all storage engines support transactions. For those storage engine replace transaction with statement.

• sync_binlog = 0: This is the default in MySQL 5.6 and earlier as well as MySQL 5.7.6 and earlier. In this case MySQL relies on the operating system to flush the binary log from time to time. This gives the best performance, but if MySQL crashes the binary log will likely be missing several transactions and it will generally be necessary to reload the slave to ensure they are in sync with the master.
• sync_binlog = 1: This it the default in MySQL 5.7.7 and later. It is the safest value and recommended value. However particularly in MySQL 5.5 and earlier it is also the value with the largest performance impact (see also below). With this value the binary log is synchronized to disk using fdatasync() after every transaction. In MySQL 5.6 and later, this guarantees that in case of a crash, no transactions will be missing in the binary log. In MySQL 5.5 and earlier up to one transaction can be missing.
• sync_binlog > 1: In this case the synchronization to disk is done for every sync_binlog transactions.

在下文中，即使并非所有存盤引擎都支持事務，但是統一使用事務一詞，對于那些不支持事務的存盤引擎，請用陳述句（statement）替換事務，

·         sync_binlog = 0：這是MySQL 5.6和更低版本以及MySQL 5.7.6和更低版本中的默認設定，在這種情況下，MySQL依賴作業系統不時重繪二進制日志到磁盤（直接由作業系統的檔案系統自己控制它的快取的重繪），這樣可以提供最佳性能，但是如果MySQL突然崩潰，二進制日志可能會丟失一些事務，因此通常需要重新加載從屬服務器以確保它們與主服務器同步，

·         sync_binlog = 1：這是MySQL 5.7.7及更高版本的默認設定，這個是最安全的推薦值，但是，特別是在MySQL 5.5和更早的版本中，它也是對性能影響最大的值（請參見下文），使用此值，在每次事務之后，二進制日志將呼叫fdatasync（）同步到磁盤，在MySQL 5.6和更高版本中，這保證了在MySQL崩潰的情況下，二進制日志中不會丟失任何事務，在MySQL 5.5和更早的版本中，最多可能丟失一個事務的資料，

·         sync_binlog> 1：在這種情況下，每sync_binlog個值的事務后會完成與磁盤的同步，（如果sync_binlog值為2，意味著2個事務后完成磁盤同步，依此類推）

譯者注釋：

sync_binlog=0，表示 MySQL 不控制binlog的重繪，由檔案系統自己控制它的快取的重繪，這時候的性能是最好的，但是風險也是最大的，因為一旦系統Crash，在 binlog_cache 中的所有 binlog 資訊都會被丟失，

sync_binlog=1，多個事務同時提交，雖然binlog是順序IO，它同樣很大的影響MySQL 和 IO 性能，雖然可以通過 group commit 的補丁緩解，但是重繪的頻率過高對 IO 的影響也非常大，對于高并發事務的系統來說，有些文章介紹“sync_binlog” 設定為 0 和設定為 1 的系統寫入性能差距可能高達 5 倍甚至更多，

If you are primarily using InnoDB as the storage engine, you should also consider the value of innodb_flush_log_at_trx_commit. If innodb_flush_log_at_trx_commit is set to 2 or 0, flushing of the InnoDB log files to disk is delayed and in that case, it may also be acceptable to delay the flushing of the binary log.

如果你主要使用InnoDB作為存盤引擎，你應該也要考慮系統變數innodb_flush_log_at_trx_commit的值，如果系統變數innodb_flush_log_at_trx_commit設定為0或2，則會延遲將InnoDB的redo日志重繪到磁盤，在這種情況下，也可能延遲重繪二進制日志，

sync_binlog的性能影響

Using MySQL 5.0 through 5.5 but not 5.6 and later, there's one important performance effect of setting sync_binlog to any value other than 0. It disables InnoDB concurrent transaction commit (group commit), slowing down transaction throughput. This makes it preferable to mostly use the 0 setting in cases where performance matters.

使用MySQL 5.0到5.5而不是5.6及更高的MySQL版本，將sync_binlog設定為0以外的任何值都會產生嚴重的性能影響，它會禁用InnoDB并發事務提交（組提交），從而降低事務吞吐量，因此，在性能很重要的情況下，最好使用0設定，

Any of the sync times greater than 0 has the advantage that the OS won't cache lots of the log, so there won't be a corresponding big burst of disk I/O when the flush for a whole gigabyte of log happens. You can mitigate that effect by setting max_binlog_size to say 100M.

任何sync_binlog值大于0的同步設定都有一個優點，即作業系統不會快取大量的二進制日志，因此，當重繪整個1Gb大小的二進制日志時，不會有相應的磁盤I/O大量爆發，您可以通過將max_binlog_size設定更小的值，例如100M來減輕這種影響，

The best solution is to use a write caching disk controller with battery backup. This will greatly improve performance for both binary log and InnoDB fsyncs. Even better, have that caching write to an SSD.

最好的解決方案是使用帶有電池備份的寫快取磁盤控制器，這將大大提高二進制日志和InnoDB fsync的性能，更好的方案是將快取寫入SSD，

建議

To provide the best safety of the data and to minimize the chance of a corrupted binary log, set the value of sync_binlog to 1:

為了提供最佳的資料安全性并最大程度地減少二進制日志損壞的可能性，請將sync_binlog的值設定為1：

[mysqld]
sync_binlog = 1

If performance is paramount use a value of 0 or greater than 1. If you don't need concurrent transaction commits you could set sync_binlog = 10 instead of 1. Or 100. That will reduce the number of fsyncs to one tenth or one hundredth of the current rate. Or 1000. An optimal balance between performance and durability could then perhaps depend on your transaction rate. If you average ten transactions per second and want to average one flush per second you could set it to 10.

如果性能至關重要，請使用0或大于1的值，如果不需要并發事務提交（組提交），你可以將sync_binlog = 10而不是1或100.這會將呼叫fsync的數量減少到當前數量的十分之一或百分之一，甚至有可能是千分之一，性能和可靠性之間的最佳平衡可能取決于您的事務的速度，如果您平均每秒進行十次事務處理，并且希望平均每秒進行一次重繪，則可以將引數sync_binlog設定為10，

You can get more tricky by observing that sync_binlog is a dynamic variable, so you can change it at runtime. You could set it to 0 most of the time to get concurrent transaction commits. Then you could have a cron job that changes it to 10 for a few seconds, so MySQL probably will do an fsync, then go back to the faster 0 setting.

通過觀察，你會發現sync_binlog是一個動態變數，可以在運行時動態進行更改，這將變得更加棘手，您通常可以在大部分時間將其設定為0，以獲取并發事務提交，然后您可能會通過執行cron作業，將其更改為10或其它值，因此MySQL可能會執行fsync，然后回傳到更快的0設定，

Another good solution is to purchase a small SSD and use that for the binary logs and InnoDB logs as well as possibly for temporary files. The large number of flushes and writes mean that the expected lifetime of the drive will be quite short, a year or two, perhaps significantly less. But it's easy and cheaper than a write caching controller.

另一個好的解決方案是購買一個小的SSD，并將其用于二進制日志和InnoDB的redo日志以及可能用到的臨時檔案，大量的重繪和寫入意味著SSD驅動器的預期壽命將會很短，有可能一到兩年，甚至可能更少，但是它比寫快取控制器更容易，更便宜，

It's also of use to consider the cases in which you might need the binary log. The most important one would be if a power outage or some other mishap corrupted your data files or disks irretrievably. That is uncommon but it does happen sometimes. InnoDB is normally safe against simple power outages, provided drive write caching is turned off (and if you use a caching controller, provided it has and uses its battery). So you could pick either innodb_flush_log_at_trx_commit = 1 or sync_binlog =1 to handle the uncommon case, while still being faster in normal use.

在考慮可能需要二進制日志的情況下，它也是很有用，最重要的是在發生斷電或其他一些意外損壞了您的資料檔案或磁盤的情況下，雖然這并不常見，但有時也確實會發生，如果驅動器寫快取已關閉（如果使用快取控制器，并且它配備有電池），那么簡單的停電的情況下，InnoDB通常也是安全的，因此，您可以選擇設定引數innodb_flush_log_at_trx_commit = 1或sync_binlog = 1來防止不常見的情況，同時在正常使用下仍然可以更快地處理，

A replication slave server provides a great deal of protection, covering you against single machine failures. MySQL 5.5 and later offers the option to have semi-synchronous replication, which will block the client until the transaction has made it to at least one slave (unless no slaves confirms within rpl_semi_sync_master_timeout milliseconds). Without semi-synchronous replication, it's normal for the I/O thread of the replication slave servers to be only milliseconds behind their master unless they are heavily loaded and for many cases that will be sufficient protection against failure of the master server's disks. The SQL thread is what is usually considered when discussing replication lag, but that delay doesn't matter for protection, just the I/O thread. You can also configure the slave to have robust durability settings without affecting master performance.

復制的從庫提供了很多保護，可以防止單機故障， MySQL 5.5及更高版本提供了具有半同步復制的選項，它將阻塞客戶端的事務提交，直到事務至少已經復制到一個從庫為止（除非沒有從庫在rpl_semi_sync_master_timeout引數指定的時間（毫秒）內進行確認），譯者注：主庫在執行完客戶端提交的事務后不是立刻回傳給客戶端，而是等待至少一個從庫接收到并寫到relay log中才回傳給客戶端，如果沒有半同步復制，復制的從庫的I/O執行緒通常要比其主庫落后幾毫秒，除非它們的負載很重，在許多情況下，即使主服務器磁盤發生故障，它都能提供足夠的保護，SQL執行緒是討論復制延遲時通常要考慮的，但是這種延遲對于資料保護并不重要，僅對I/O執行緒很重要，您還可以配置從庫以具有可靠的持久性設定，而不會影響主服務器的性能

譯者注：這里將slave server翻譯成從庫，而不是從屬服務器，從習慣上跟自然一點

REFERENCES

https://dev.mysql.com/doc/refman/en/replication-options-master.html#sysvar_rpl_semi_sync_master_timeout
NOTE:1450190.1 - How to Recover From a Replication Error?
NOTE:1375415.1 - Error: Error In Log_event::Read_log_event(): 'Event Too Big', Data_len: Event_type:
NOTE:1024111.1 - MEM Warning: Binary Logging Not Synchronized To Disk At Each Write
NOTE:1024113.1 - InnoDB log buffer flushes to disk after each transaction
https://dev.mysql.com/doc/refman/en/replication-options-binary-log.html#sysvar_sync_binlog
https://dev.mysql.com/doc/refman/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
https://dev.mysql.com/doc/refman/en/glossary.html#glos_group_commit
https://dev.mysql.com/doc/refman/en/replication-options-binary-log.html#sysvar_max_binlog_size
https://dev.mysql.com/doc/refman/en/replication-semisync.html

標籤：其他

上一篇：Mysql查看執行計劃及索引使用

下一篇：MySQL 資料完整性