innodb_flush_log_at_trx_commit
And sync_binlog
is MySQL's two configuration parameters, the former is unique to the InnoDB engine. These two parameters are discussed together because their configuration has a significant impact on MySQL performance in real-world applications.
1. Innodb_flush_log_at_trx_commit
In short, the innodb_flush_log_at_trx_commit
parameter specifies how often InnoDB logs are written after the transaction commits. In fact, it is not rigorous, and look at the significance and performance of the different values.
- When
innodb_flush_log_at_trx_commit
the value is taken 0
, log buffer writes to the log file every second and writes (flush) to the disk. However, each transaction commit has no effect, that is, the write operation of log buffer has no relation to the transaction commit operation. In this case, MySQL performs best, but if the mysqld process crashes, it usually results in the last 1s of log loss.
- When the value is taken
1
, log buffer is written to the log file and written to disk each time the transaction commits. This is also the default value. This is the safest configuration, but it is also the slowest because disk I/O is required for each transaction.
- When a value is taken
2
, each transaction commit is written to the log file, but it is not immediately written to disk, and the log file is brushed once per second to disk. At this point, if the mysqld process crashes, the data is not lost because the log has been written to the system cache, and in the case of an operating system crash, it usually results in the last 1s of log loss.
"The last 1s" is not absolute, sometimes it loses more data, "said the above. Sometimes due to scheduling problems, brushing per second (Once-per-second flushing) does not guarantee 100% execution. For some applications where data consistency and integrity requirements are not high, configuration is sufficient, and 2
if for maximum performance, it can be set to 0
. Some applications, such as payment services, require a high level of consistency and completeness, so even the slowest is best set to 1
.
2. Sync_binlog
Sync_binlog is the frequency at which MySQL's binary logs (binary log) are synchronized to disk. MySQL server sync_binlog
brushes writes to disk after each write of binary log.
If autocommit
turned on, each statement is written once by binary log, otherwise each transaction is written once. The default value is 0
not to actively synchronize, but relies on the operating system itself to flush the file contents to disk periodically. is set to be 1
the safest, synchronizing a binary log once per statement or transaction, even if it crashes, it loses at most one statement or transaction log, but is therefore also the slowest.
In most cases, the consistency of the data is not strictly required, so it will not be sync_binlog
configured 1
. In order to pursue high concurrency, improve performance, can be set to 100
or directly 0
. and innodb_flush_log_at_trx_commit
, as with the application of payment services, it is more recommended sync_binlog = 1
.
MySQL parameters: Innodb_flush_log_at_trx_commit and Sync_binlog