[翻译]——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

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

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

解释在MySQL Server中什么时候设置参数sync_binglog以及为什么要设置参数sync_binglog

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

针对熟悉该选项或寻求有关使用该选项使用帮助的DBA的详细的一份常规指南。

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翻译成从库，而不是从属服务器。从习惯上跟自然一点

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