——MySQL Server Variable: sync_binlog (Doc ID 1501926.1)


本文是对




MySQL Server Variable: sync_binlog (Doc ID 1501926.1)




这篇文章的翻译,如有翻译不当或错误的地方敬请指正。



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


潇湘隐者
我还没有学会写个人说明!
下一篇

centos7.6 使用 kubeadm搭建k8s (1.18.2版本)高可用集群

你也可能喜欢

评论已经被关闭。

插入图片