innodb_flush_log_at_trx_commit and sync_binlog control how ApsaraDB RDS for MySQL writes redo logs and binary logs to disk. Tuning these two parameters together lets you shift the balance between write throughput and data durability to match your workload.
Usage notes
-
Review what each parameter does before changing its value. Incorrect settings can cause data loss or performance degradation. For parameter definitions, see innodb_flush_log_at_trx_commit and sync_binlog in the MySQL documentation.
-
Change parameters during off-peak hours and monitor query performance after each change.
-
Avoid the following settings:
-
sync_binlog=0: Can cause jitter during binary log rotation. -
innodb_flush_log_at_trx_commit=0: An instance restart risks significant data loss.
-
Parameter reference
innodb_flush_log_at_trx_commit
Controls how InnoDB flushes the log buffer to disk. For the highest level of fault tolerance, set this parameter to 1 along with sync_binlog=1. Valid values: 0, 1, 2.
| Value | Behavior | Trade-off |
|---|---|---|
0 |
InnoDB writes and flushes the log buffer to disk once per second, regardless of commits. | Best write performance, but an instance crash can erase up to one second of committed transactions. |
1 *(default)* |
InnoDB writes and flushes the log buffer to disk on every commit. This is required for full ACID compliance. | Highest data safety, at the cost of increased I/O overhead in high-concurrency workloads. |
2 |
InnoDB writes the log buffer to the file system buffer on every commit; the operating system flushes to disk once per second. | Better throughput than 1 with stronger safety than 0, but an instance crash can still erase up to one second of committed transactions. |
sync_binlog
Controls how MySQL syncs binary logs to disk. For the highest level of fault tolerance, set this parameter to 1 along with innodb_flush_log_at_trx_commit=1. Valid values: 0, 1, N (any positive integer).
| Value | Behavior | Trade-off |
|---|---|---|
0 |
MySQL writes binary logs to the file system buffer after each commit and lets the operating system decide when to flush to disk. | Best write performance, but an instance crash can lose binary logs that were not yet flushed. |
1 *(default)* |
MySQL syncs binary logs to disk after every commit. | Highest data safety, with higher I/O overhead per transaction. |
N |
MySQL syncs binary logs to disk after every N commits. | A performance-safety compromise; an instance crash can lose binary logs for up to N transactions. |
Recommended settings
| Priority | innodb_flush_log_at_trx_commit |
sync_binlog |
When to use |
|---|---|---|---|
| Durability first | 1 |
1 |
Production databases where any data loss is unacceptable |
| Throughput first | 2 |
1000 |
Batch jobs, migrations, or event-driven workloads that can tolerate losing up to one second of redo logs and up to 1,000 binary log entries on an instance crash |
The throughput-first settings may cause data loss. After temporary workloads finish, reset both parameters to their default values (1) to restore full durability.
Configure the parameters
To change innodb_flush_log_at_trx_commit or sync_binlog, follow the steps in Modify the parameters of an ApsaraDB RDS for MySQL instance.
Tune for specific scenarios
Read-only instance replication lag
When a primary instance handles heavy write traffic, a read-only instance using the default settings (sync_binlog=1, innodb_flush_log_at_trx_commit=1) can fall behind because binary logs may not be immediately applied to the read-only instance.
Set the following values on the read-only instance to reduce lag:
-
sync_binlog=1000 -
innodb_flush_log_at_trx_commit=2
These settings reduce but do not eliminate replication lag.
Slow DTS data migration
During a DTS data migration job, frequent disk syncs for binary and redo logs can become a bottleneck on the required instance.
Set the following values on the required instance to speed up the migration:
-
sync_binlog=1000 -
innodb_flush_log_at_trx_commit=2
After the migration finishes, reset both parameters to 1 to restore full durability.
High-concurrency write performance (flash sales, big promotions)
With both parameters set to 1, every transaction triggers a disk flush. This becomes a throughput bottleneck under high-concurrency write loads such as flash sales or big promotions.
Set the following values during the peak period:
-
sync_binlog=1000 -
innodb_flush_log_at_trx_commit=2
After the event ends, reset both parameters to 1.
What's next
Use the parameter diagnostics feature to get automated parameter optimization recommendations for your instance.