What is innodb_flush_log_at_trx_commit in MySQL
This article explains the role of the
innodb_flush_log_at_trx_commit configuration parameter in
MySQL, detailing how it balances ACID compliance and database
performance. We will explore its three settings (0, 1, and 2), their
impact on data durability, and how to choose the right value for your
application’s specific needs.
Understanding the Parameter
The innodb_flush_log_at_trx_commit parameter controls
how strictly MySQL adheres to the Durability (the “D” in ACID) of
transactions. Specifically, it dictates when transaction log buffer
contents are written to the redo log file and when those files are
flushed (synced) to physical disk storage.
This setting represents a direct trade-off between write performance and data safety.
The Three Configuration Values
You can configure this parameter to one of three values:
0, 1, or 2.
Value 1 (Default: Full ACID Compliance)
- Behavior: The log buffer is written to the redo log file and flushed to disk at every transaction commit.
- Data Safety: Highest. In the event of a MySQL crash, operating system crash, or power outage, no committed transactions are lost.
- Performance: Lowest. Because every commit requires a synchronous disk write (fsync), disk I/O becomes a bottleneck.
Value 0 (Maximum Performance)
- Behavior: The log buffer is written to the redo log file and flushed to disk once per second. Nothing happens at transaction commit.
- Data Safety: Lowest. If the MySQL service crashes, the operating system crashes, or a power outage occurs, you can lose up to one second of committed transactions.
- Performance: Highest. Transaction commits do not wait for disk I/O, allowing for very high write throughput.
Value 2 (Balanced Compromise)
- Behavior: The log buffer is written to the redo log file at every transaction commit, but the flush (sync) to disk occurs only once per second.
- Data Safety: Medium. If the MySQL process crashes, no data is lost because the data was already written to the operating system cache. However, if the operating system crashes or there is a power outage, you can lose up to one second of transactions.
- Performance: High. Writes to the operating system
cache are memory-to-memory operations, making this setting significantly
faster than
1.
Comparison Summary
| Value | Write to OS Cache | Flush to Physical Disk | Data Loss Risk (MySQL Crash) | Data Loss Risk (OS/Power Crash) |
|---|---|---|---|---|
| 0 | Once per second | Once per second | Up to 1 second | Up to 1 second |
| 1 | Every commit | Every commit | None | None |
| 2 | Every commit | Once per second | None | Up to 1 second |
How to Choose the Right Setting
The optimal setting depends on your application’s tolerance for data loss versus its performance requirements:
- Use
1(Default) for critical databases where data loss is unacceptable, such as financial transactions, user accounts, and e-commerce checkouts. - Use
2for applications that require high performance but can tolerate up to one second of data loss in the rare event of a total system failure (e.g., analytics, logging systems, or non-critical application states). - Use
0rarely, typically only for staging environments, temporary database instances, or data loading operations where speed is paramount and the data can be easily recreated.