MySQL performance optimization (i)

MySQL parameters: Innodb_flush_log_at_trx_commit and Sync_binlog

Innodb_flush_log_at_trx_commit and Sync_binlog are two configuration parameters for MySQL, which are unique to the InnoDB engine. In practical applications, their configuration has a significant impact on MySQL performance.

1.innodb_flush_log_at_trx_commit

The Innodb_flush_log_at_trx_commit parameter specifies how often the InnoDB log is written after the transaction commits. In fact, this is not rigorous, different values have different meanings and performance.

①. When the Innodb_flush_log_at_trx_commit value is 0, log buffer writes every second to the log file and brushes (flush) to 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.

②.=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.

③.=2, each transaction commit writes to the log file, but does not immediately write to the 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 low, configuring 2 is sufficient and can be set to 0 for maximum performance. Some applications, such as payment services, have high consistency and integrity requirements, so even the slowest, it is best to set

2.sync_binlog

Sync_binlog is the frequency at which MySQL's binary logs (binary log) are synchronized to disk. MySQL server brushes writes to disk after binary log writes sync_binlog times.

If Autocommit is turned on, each statement is written once by binary log, otherwise each transaction is written once.

①. The default value is = 0, which does not actively synchronize, but relies on the operating system itself to flush the file contents to disk periodically. Set to 1 The safest, synchronize 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 the Sync_binlog is not configured to 1. In pursuit of high concurrency, performance can be set to 100 or directly with 0. And like Innodb_flush_log_at_trx_commit, for the payment of services such applications, or more recommended sync_binlog=1.

MySQL performance optimization (i)