MySQL parameters: Innodb_flush_log_at_trx_commit and Sync_binlog

innodb_flush_log_at_trx_commitAnd 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.

1. 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.
2. 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.
3. 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