Completely master the process of writing Binary Log in MySql

This article brings you relevant knowledge about the process of writing Binary Log in mysql, including issues related to "sync_binlog", "binlog_cache_size" and "max_binlog_cache_size". I hope it will be helpful to everyone.

Binary Log writing process

Let’s first take a look at the official document’s description of sync_binlog configuration.

sync_binlog

##Command line format--sync-binlog=#System variablessync_binlogscope of influenceGlobalDynamicYesSET_VAR prompt appliesNo##Type##Default value1Minimum value0Maximum value2^32=4294967295Control how often the MySQL server synchronizes binary logs to disk.

Integer

sync_binlog=0: Disables the MySQL server from synchronizing the binary log to disk. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time, just as it would do any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, the server may have committed transactions that have not yet been flushed.

• sync_binlog=1: Enables binary log to disk synchronization before committing a transaction. This is the safest setting, but may have a negative impact on performance due to increased disk writes. In the event of a power failure or operating system crash, the transactions lost in the binary log are only in the prepared state. This allows regular automatic recovery to roll back transactions, thus guaranteeing that transactions will not be lost from the binary log.

• sync_binlog=N, which is a value other than 0 or 1: After N binary log submission groups are collected, the binary log will be synchronized to disk. In the event of a power failure or operating system crash, the server may have committed transactions that have not yet been flushed to the binary log. This setting may have a negative impact on performance due to increased disk writes. Higher values ​​improve performance but increase the risk of data loss.
InnoDB

To obtain the greatest possible durability and consistency in a replication setup 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 cheats Flush to disk operation. They may tell mysqld that a refresh has occurred even though it hasn't happened yet. In this case, transaction durability is not guaranteed even with the recommended settings, and in the worst case, a power outage may corrupt the InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or the disk itself speeds up file refreshes and makes operations more secure. You can also try disabling caching of disk writes in the hardware cache.

Summary

The sync_binlog setting type is unsigned Integer.

Generally it is not set to 0. 0 depends on system operation and irregular fsync. It is more dangerous when a power failure or system crash occurs - the transaction is submitted but the Binary Log is missing.

• It is safer to set it to 1, obtain the maximum possible durability and consistency, and ensure subsequent master-slave replication and recovery. However, it is detrimental to performance and can be set when the IOPS required by the business is not high.
• The purpose of setting a value greater than 1 is to improve performance. Instead of committing a transaction, fsync is equivalent to batch flushing. It is a smart way, but if there is a power failure or system crash, the Binary Log will be missing. There will be more. It would be safer if the disk itself used a battery-backed disk cache. Therefore, it can be set when the IOPS required by the business is relatively high, but generally it will not be set too large and can be in the [100, 1000] range.
• In addition, through the description of sync_binlog=0, we can also roughly feel that in fact, when the transaction is submitted, although there is no fsync immediately, it has actually been written to the page cache of the file system. , then in fact, mysql will also have a cache to cache the Binary Log generated in the transaction when the transaction is running.
• Let’s continue to look at the cache related configuration of Binary Log when the transaction is running.

binlog_cache_size

##Command format--binlog-cache-size=#System variablebinlog_cache_sizeScopeGolbalDynamicYesSET_VAR prompt appliesNo

##Type

Integer

Default value

32768

Minimum value

4096

Maximum value (64-bit platform)

2^64=18446744073709547520

Maximum value (32-bit platform)

2^32=4294967295

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 a server (the log_bin system variable is set to ON), each client is assigned a binary log cache if the server supports any transaction storage engine. If a 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 not encrypted, but (starting with MySQL 8.0.17) any temporary files used to hold the binary log cache are encrypted. After each transaction is committed, 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 status variable - the number of transactions using the Binary Log cache) and Binlog_cache_disk_use (service status variable - the number of transactions using the temporary binary log cache but exceeding the binlog_cache_size value and using temporary files to store transaction statements.) status variables can be used to adjust this variable size. See Section 5.4.4, “Binary Logs”.

binlog_cache_sizeOnly sets the size of the transaction cache; the size of the statement cache is controlled by the binlog_stmt_cache_size system variable.

Summary

• binlog_cache_size setting type is unsigned Integer.
• is used to indicate the size used to cache Binary Log during each transaction. The default is 32k and must be a multiple of 4096. If this value is exceeded, temporary file storage will be used.
• Try not to use large transactions in business. If the transaction is too large, you need to consider whether it is reasonable. Generally, there is no need to modify binlog_cache_size, 32k is enough.
• When binlog_cache_size is not enough, temporary files will be used for storage, but the performance will be lower. We can set max_binlog_cache_size=binlog_cache_size so that temporary files will not be used, which will be introduced below.

max_binlog_cache_size

##Command format--max-binlog-cache-size=System variablemax_binlog_cache_sizeRangeGolbalDynamicYesSET_VAR prompt ApplicableNoTypeIntegerDefault value2^ 64=18446744073709547520Minimum value4096Maximum value2^64=18446744073709547520Block size4096

If a transaction requires more than this many bytes of memory, the server will generate a multi-statement transaction requiring more than 'max_binlog_cache_size' bytes of storage error. 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_sizeOnly sets the size of the transaction cache; the upper limit of the statement cache is controlled by the max_binlog_stmt_cache_size system variable.

Visibility of sessions max_binlog_cache_size Matches the visibility of the binlog_cache_size system variable; in other words, changing its value will only affect new sessions started after the value is changed.

Summary

• max_binlog_cache_size is a safe value, generally set according to the memory that can be allocated by the server.

Overview

From the above configuration, we can conclude that the Binary Log writing process is roughly:

1. The transaction is changed to runtime and placed into the Binary Log cache of each transaction.
2. After the transaction is submitted, it is performed according to the configuration. If sync_binlog=1, the cache will be released every time fsync is performed. If sync_binlog=0, it will be written directly to the page cache of the system file, relying on the operating system to flush the binary log from time to time. If sync_binlog=N (N>1), it is equivalent to batch flushing. Of course, the binlog cache held by each transaction will be released.

So the general process is as follows:

Summary

So far we have a general understanding of the process of writing Mysql to Binary. We need Through: binlog cache held by each transaction -> page cache of the file system -> disk. The specific execution strategy can be controlled through sync_binlog.

Use

• sync_binlog: If you need to obtain maximum durability and consistency, set it to 1. As for performance issues, you can adjust the hardware and other methods; if you run binary log, loss is allowed Or if you lose control through other methods and want to optimize based on the current server resources, set it in the [100,1000] range.
• binlog_cahe_size: As mentioned before, in actual business, attention needs to be paid to controlling the transaction granularity. In most cases, the default 32k is enough.

Recommended learning: mysql video tutorial

The above is the detailed content of Completely master the process of writing Binary Log in MySql. For more information, please follow other related articles on the PHP Chinese website!