|
本文對MySQL Server Variable: sync_binlog (Doc ID 1501926.1)這篇文章進行了翻譯，如有翻譯不當或錯誤的地方敬請指正。
譯文地址：https://www.cnblogs.com/kerrycode/p/14167941.html
In this Document Performance Impact of sync_binlog
APPLIES TO: MySQL Server - Version 4.1 to 5.6 [Release 4.1 to 5.6] Explain when and why to use the sync_binlog setting in MySQL Server. 解釋在MySQL Server中什么時候設(shè)置參數(shù)sync_binglog以及為什么要設(shè)置參數(shù)sync_binglog Detailed general guidance for DBAs who are familiar with the option and looking for help on how to use it. 針對熟悉該選項或?qū)で笥嘘P(guān)使用該選項使用幫助的DBA的詳細的一份常規(guī)指南。 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參數(shù)設(shè)置，它控制了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.
在下文中，即使并非所有存儲引擎都支持事務(wù)，但是統(tǒng)一使用事務(wù)一詞。對于那些不支持事務(wù)的存儲引擎，請用語句（statement）替換事務(wù)。 · sync_binlog = 0：這是MySQL 5.6和更低版本以及MySQL 5.7.6和更低版本中的默認設(shè)置。在這種情況下，MySQL依賴操作系統(tǒng)不時刷新二進制日志到磁盤（直接由操作系統(tǒng)的文件系統(tǒng)自己控制它的緩存的刷新）。這樣可以提供最佳性能，但是如果MySQL突然崩潰，二進制日志可能會丟失一些事務(wù)，因此通常需要重新加載從屬服務(wù)器以確保它們與主服務(wù)器同步。 · sync_binlog = 1：這是MySQL 5.7.7及更高版本的默認設(shè)置。這個是最安全的推薦值。但是，特別是在MySQL 5.5和更早的版本中，它也是對性能影響最大的值（請參見下文）。使用此值，在每次事務(wù)之后，二進制日志將調(diào)用fdatasync（）同步到磁盤。在MySQL 5.6和更高版本中，這保證了在MySQL崩潰的情況下，二進制日志中不會丟失任何事務(wù)。在MySQL 5.5和更早的版本中，最多可能丟失一個事務(wù)的數(shù)據(jù)。 · sync_binlog> 1：在這種情況下，每sync_binlog個值的事務(wù)后會完成與磁盤的同步。（如果sync_binlog值為2，意味著2個事務(wù)后完成磁盤同步，依此類推） 譯者注釋： sync_binlog=0，表示 MySQL 不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新。這時候的性能是最好的，但是風險也是最大的。因為一旦系統(tǒng)Crash，在 binlog_cache 中的所有 binlog 信息都會被丟失。 sync_binlog=1，多個事務(wù)同時提交，雖然binlog是順序IO，它同樣很大的影響MySQL 和 IO 性能。雖然可以通過 group commit 的補丁緩解，但是刷新的頻率過高對 IO 的影響也非常大。對于高并發(fā)事務(wù)的系統(tǒng)來說，有些文章介紹“sync_binlog” 設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達 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作為存儲引擎，你應(yīng)該也要考慮系統(tǒng)變量innodb_flush_log_at_trx_commit的值，如果系統(tǒng)變量innodb_flush_log_at_trx_commit設(shè)置為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設(shè)置為0以外的任何值都會產(chǎn)生嚴重的性能影響。它會禁用InnoDB并發(fā)事務(wù)提交（組提交），從而降低事務(wù)吞吐量。因此，在性能很重要的情況下，最好使用0設(shè)置。 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的同步設(shè)置都有一個優(yōu)點，即操作系統(tǒng)不會緩存大量的二進制日志，因此，當刷新整個1Gb大小的二進制日志時，不會有相應(yīng)的磁盤I/O大量爆發(fā)。您可以通過將max_binlog_size設(shè)置更小的值，例如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: 為了提供最佳的數(shù)據(jù)安全性并最大程度地減少二進制日志損壞的可能性，請將sync_binlog的值設(shè)置為1： [mysqld]
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. 如果性能至關(guān)重要，請使用0或大于1的值。如果不需要并發(fā)事務(wù)提交（組提交），你可以將sync_binlog = 10而不是1或100.這會將調(diào)用fsync的數(shù)量減少到當前數(shù)量的十分之一或百分之一。甚至有可能是千分之一。性能和可靠性之間的最佳平衡可能取決于您的事務(wù)的速度。如果您平均每秒進行十次事務(wù)處理，并且希望平均每秒進行一次刷新，則可以將參數(shù)sync_binlog設(shè)置為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. 通過觀察，你會發(fā)現(xiàn)sync_binlog是一個動態(tài)變量，可以在運行時動態(tài)進行更改，這將變得更加棘手。您通?？梢栽诖蟛糠謺r間將其設(shè)置為0，以獲取并發(fā)事務(wù)提交。然后您可能會通過執(zhí)行cron作業(yè)，將其更改為10或其它值，因此MySQL可能會執(zhí)行fsync，然后返回到更快的0設(shè)置。
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驅(qū)動器的預(yù)期壽命將會很短，有可能一到兩年，甚至可能更少。但是它比寫緩存控制器更容易，更便宜。 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. 在考慮可能需要二進制日志的情況下，它也是很有用。最重要的是在發(fā)生斷電或其他一些意外損壞了您的數(shù)據(jù)文件或磁盤的情況下。雖然這并不常見，但有時也確實會發(fā)生。如果驅(qū)動器寫緩存已關(guān)閉（如果使用緩存控制器，并且它配備有電池），那么簡單的停電的情況下，InnoDB通常也是安全的。因此，您可以選擇設(shè)置參數(shù)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. 復(fù)制的從庫提供了很多保護，可以防止單機故障。 MySQL 5.5及更高版本提供了具有半同步復(fù)制的選項，它將阻塞客戶端的事務(wù)提交，直到事務(wù)至少已經(jīng)復(fù)制到一個從庫為止（除非沒有從庫在rpl_semi_sync_master_timeout參數(shù)指定的時間（毫秒）內(nèi)進行確認）。譯者注：主庫在執(zhí)行完客戶端提交的事務(wù)后不是立刻返回給客戶端，而是等待至少一個從庫接收到并寫到relay log中才返回給客戶端。如果沒有半同步復(fù)制，復(fù)制的從庫的I/O線程通常要比其主庫落后幾毫秒，除非它們的負載很重，在許多情況下，即使主服務(wù)器磁盤發(fā)生故障，它都能提供足夠的保護。SQL線程是討論復(fù)制延遲時通常要考慮的，但是這種延遲對于數(shù)據(jù)保護并不重要，僅對I/O線程很重要。您還可以配置從庫以具有可靠的持久性設(shè)置，而不會影響主服務(wù)器的性能 譯者注：這里將slave server翻譯成從庫，而不是從屬服務(wù)器。從習(xí)慣上跟自然一點
https://dev./doc/refman/en/replication-options-master.html#sysvar_rpl_semi_sync_master_timeout |
|
|
![clip_image002[8]](http://image109.360doc.com/DownloadImg/2022/02/1808/239624342_1_2022021808201369.jpg "clip_image002[8]"){kind=small}
