Help Center/ Relational Database Service_RDS for MySQL/ Best Practices/ Detailed Explanation of innodb_flush_log_at_trx_commit and sync_binlog
Updated on 2026-05-12 GMT+08:00
View PDF
Share

Detailed Explanation of innodb_flush_log_at_trx_commit and sync_binlog

innodb_flush_log_at_trx_commit and sync_binlog are key parameters for controlling the disk write policy and data security of RDS for MySQL. When they are set to different parameter values, there are different impacts on performance and security.

Table 1 Parameters

Parameter

Allowed Values

Description

innodb_flush_log_at_trx_commit

0, 1, and 2

Controls the balance between strict ACID compliance for commit operations and high performance when commit-related I/O operations are rearranged and performed in batches. The default value is 1. For details, see Parameter Explanation.

sync_binlog

0 to 4,294,967,295

Controls how often the binlog is synchronized to disk. RDS for MySQL flushes binlogs to the disk itself or by relying on the OS.

Parameter Explanation

  • innodb_flush_log_at_trx_commit
    • 0: The log buffer is written to the log file once per second and the log file is then flushed to disk. In this mode, committing a transaction does not trigger a disk flush.
    • 1: RDS for MySQL writes the log buffer to the log file at each transaction commit and flushes it to disk. This is the default setting.
    • 2: RDS for MySQL writes the log buffer to the log file at each transaction commit but does not flush it to disk. In this mode, RDS for MySQL performs a disk flush once per second.
    • Setting the parameter to 0 offers the fastest performance but lower security. Any mysqld process crash can erase the last second of transactions.
    • Setting it to 1 is the safest but slowest choice. In the event of a crash, you may lose at most one statement or transaction from the binary log.
    • Setting it to 2 improves speed and security compared to the value 0. Only an OS crash or a power outage can erase the last second of transactions.
  • sync_binlog=1 or N

    By default, the binary log is not synchronized to disk after every write. In the event of a crash, the last statement in the binary log may get lost.

    To prevent this issue, you can use the sync_binlog global variable (1 is the safest but also the slowest option) to ensure that the binlog is synchronized to disk after every N writes.

Recommended Configurations

Table 2 Recommended configurations

innodb_flush_log_at_trx_commit

sync_binlog

Description

1

1

High data security and strong disk write capability

1

0

High data security and insufficient disk write capability. Standby lagging behind or no replication is allowed.

2

0/N (0 < N < 100)

Low data security. A small amount of transaction log loss and replication delay is allowed.

0

0

Limited disk write capability. No replication or long replication delay is allowed.
  • When both innodb_flush_log_at_trx_commit and sync_binlog are set to 1, the system provides the highest security but the lowest write performance. In the event of a mysqld process crash or a server failure, you may lose at most one statement or transaction from the log buffer. However, because this configuration triggers frequent disk writes, it is also the slowest choice.
  • When sync_binlog is set to N (N > 1) and innodb_flush_log_at_trx_commit is set to 2, RDS for MySQL can achieve the highest write performance under this configuration.