“This is the 14th day of my participation in the First Challenge 2022.

preface

Binary Log sync_binlog: Binary Log sync_binlog: Binary Log sync_binlog: Binary Log

The Binary Log of Mysql also includes the binlog_format, which will be added later.

Write process of Binary Log

Let’s first look at the official documentation describing the sync_binlog configuration.

sync_binlog

Command line format	–sync-binlog=#
System variables	sync_binlog
scope	Global
dynamic	Yes
SET_VARTips for	No
type	Integer
The default value	1
The minimum value	0
The maximum	2 ^ 32 = 4294967295

Controls how often the MySQL server synchronizes binary logs to disk.

• Sync_binlog =0: Disables the MySQL server from synchronizing binary logs to disk. Instead, the MySQL server relies on the operating system to flush binary logs to disk from time to time, just as it does with any other file. This setting provides the best performance, but if there is a power failure or an operating system crash, the server may have committed transactions that have not been flushed.

• Sync_binlog =1: Enable binary log to disk synchronization before committing transactions. This is the safest setting, but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or an operating system crash, the transactions that are lost in the binary log are only in the ready state. This allows regular automatic recovery of rollback transactions, thus ensuring that transactions are not lost from the binary log.

• Sync_binlog =N, where values other than 0 or 1: After N binary log submission groups are collected, binary logs are synchronized to disk. In the case of a power failure or an operating system crash, the server may have committed transactions that have not yet been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. Higher values improve performance, but increase the risk of data loss.

InnoDB for maximum possible persistence and consistency in the replication Settings used with transactions, use the following Settings:

• sync_binlog=1.
• innodb_flush_log_at_trx_commit=1.

warning

Many operating systems and some disk hardware cheat the refresh to disk operation. They might tell mysqld that a refresh has occurred, even if it hasn’t. In this case, transaction persistence is not guaranteed even with the recommended Settings, and in the worst case, power outages may corrupt InnoDB data. The use of battery-supported disk caching in SCSI disk controllers or disks themselves speeds up file refreshes and makes operations safer. You can also try to disable the caching of disk writes in the hardware cache.

summary

• Sync_binlog sets the type to unsigned Integer.
• The Binary Log is not set to 0,0 depending on the system operating intermittently fsync, which can be dangerous when a power failure or system crash occurs — the transaction is committed but the Binary Log is missing.
• Setting this parameter to 1 ensures maximum persistence and consistency and ensures subsequent replication and recovery. However, the performance is adversely affected. You can set this parameter when the IOPS required by the service is not high.
• Set the Binary Log to a value greater than 1 to improve performance. Instead of committing a transaction to fsync, it is equivalent to a batch flush, which is a smarter way to do it, but the Binary Log will be missing more if there is a power failure or a system crash. It is safer if the disk itself uses a battery-supported disk cache. Therefore, you can set this parameter when the IOPS required by services is high. In general, the value can be in the range from 100 to 1000.

Sync_binlog =0: the transaction is not fsync immediately, but it is written to the page cache of the file system. A Binary Log is stored in the mysql database cache during a transaction.

Let’s take a look at the cache configuration of the Binary Log at transaction run time.

binlog_cache_size

The command format	–binlog-cache-size=#
System variables	binlog_cache_size
The scope of	Golbal
dynamic	Yes
SET_VARTips for	No
type	Integer
The default value	32768
The minimum value	4096
Maximum (64-bit platform)	2 ^ 64 = 18446744073709547520
Maximum (32-bit platform)	2 ^ 32 = 4294967295
The block size	4096

The size of the memory buffer to hold binary log changes during a transaction. The value must be a multiple of 4096.

When binary logging is enabled ON the server (log_bin system variable is set to ON), a binary log cache is assigned to each client if the server supports any transactional storage engine. If the transaction’s data exceeds the space in the memory buffer, the excess data is stored in a temporary file. When binary log encryption is active on the server, the memory buffer is unencrypted, but (starting with MySQL 8.0.17) any temporary files used to hold the binary log cache are encrypted. After each transaction commits, the binary log cache is reset by clearing the memory buffer and truncating the temporary file (if used).

If you frequently use large transactions, you can increase this cache size for better performance by reducing or eliminating the need to write temporary files. Binlog_cache_use (service state variable – number of transactions cached using the Binary Log) and Binlog_cache_disk_use (Service state variable – Number of transactions that use temporary binary log cache but exceed binlog_CACHE_size value and use temporary file to store transaction statements.) A state variable can be used to resize this variable. See section 5.4.4, “Binary logging.”

Binlog_cache_size Specifies the size of the transaction cache only. The size of the statement cache is controlled by the binlog_STMT_cache_size system variable.

summary

• Binlog_cache_size Sets the type to unsigned Integer.
• Specifies the size to cache the Binary Log during each transaction. The default is 32K and must be a multiple of 4096. If this value is exceeded, temporary file storage is used.
• Try not to use large transactions in business. If the transaction is too large, you need to consider whether it is reasonable. Binlog_cache_size generally does not need to be modified; 32K is sufficient.
• When binlog_cache_size is insufficient, temporary files are used for storage, but performance deteriorates. We can set max_binlog_cache_size=binlog_cache_size so that temporary files are not used, as described below.

max_binlog_cache_size

The command format	–max-binlog-cache-size=#
System variables	max_binlog_cache_size
The scope of	Golbal
dynamic	Yes
SET_VARTips for	No
type	Integer
The default value	2 ^ 64 = 18446744073709547520
The minimum value	4096
The maximum	2 ^ 64 = 18446744073709547520
The block size	4096

If a transaction requires more than this many bytes of memory, the server generates a storage error for a multi-statement transaction requiring more than the ‘max_binlog_CACHE_size’ bytes. The minimum value is 4096. The maximum possible value is 16EiB (exbibytes). The maximum recommended value is 4GB. This is because MySQL currently cannot handle binary log locations larger than 4GB. The value must be a multiple of 4096.

Max_binlog_cache_size Sets only the size of the transaction cache; The upper limit of the statement cache is controlled by the max_binlog_STmT_cache_size system variable.

Session visibility max_BINlog_CACHE_size Matches the visibility of the binlog_cache_size system variable. In other words, changing its value only affects the new session started after changing the value.

conclusion

• Max_binlog_cache_size is a safe value that is typically set based on the amount of memory available to the server.

An overview of the

From the above configuration, we can obtain the Binary Log writing process:

1. At runtime, transactions are placed in the Binary Log cache for each transaction.
2. If sync_binlog=1, the cache will be released each time fsync is performed. If sync_binlog=0, it will be written directly to the system file’s Page cache, depending on the operating system to flush binary logs from time to time. If sync_binlog=N(N>1), the binlog cache held by each transaction will be released.

Therefore, the general process is shown as follows:

conclusion

Each transaction holds the binlog cache -> page cache of the file system -> disk. Specific execution policies can be controlled using sync_binlog.

use

• Sync_binlog: Set to 1 if maximum persistence and consistency is required, and adjust hardware for performance issues. If you run binary log to allow loss or otherwise control loss and want to optimize based on current server resources, set it to the [100,1000] range.
• Binlog_cahe_size: it has been mentioned before that transaction granularity should be controlled in actual services. In most cases, the default 32K is sufficient.