Configure innodb_flush_log_at_trx_commit for Durability vs Speed (2026)

The default setting for innodb_flush_log_at_trx_commit makes your database ACID compliant, but it’s also the slowest.

Let’s see what this actually looks like in practice. Imagine a simple transaction in MySQL:

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
COMMIT;

When innodb_flush_log_at_trx_commit is set to 1 (the default), here’s the sequence of events that makes it so durable, and so slow:

1. The INSERT happens: The data is written to the InnoDB buffer pool.
2. The COMMIT is issued: This is where the magic (and the overhead) happens.
3. InnoDB writes the transaction log to the OS buffer: The details of the INSERT are written to the transaction log file (ib_logfile0, ib_logfile1, etc.) in memory managed by the operating system. This is fast.
4. InnoDB flushes the OS buffer to disk: This is the critical, slow step. fsync() is called on the transaction log file, forcing the operating system to write the data from its buffer to the physical disk. This guarantees that even if the server crashes, the transaction is safely on disk.
5. InnoDB flushes the data file to disk: Separately, the actual data page in the buffer pool is eventually written to the users.ibd file. This is also flushed to disk.

The fsync() operation in step 4 is the bottleneck. It’s a synchronous disk write, meaning the CPU has to wait for the physical disk to complete the operation before the COMMIT can return success to the client. On a busy system, these fsync() calls can pile up, leading to high I/O wait times and a perception of slow database performance.

So, why would anyone ever change this? Because sometimes, you can tolerate a tiny risk of data loss for a massive speedup.

There are two other common values for innodb_flush_log_at_trx_commit:

• 2:

  • What happens: The transaction log is written to the OS buffer (step 3), but it’s not immediately fsync()'d to disk. Instead, InnoDB relies on the operating system’s periodic flushing of its buffers to disk (typically every second). The data file is still flushed separately.
  • The trade-off: If the MySQL server crashes, you might lose transactions that were committed within the last second. However, if the operating system crashes or the server loses power, you will lose those transactions because the OS buffer is volatile. The advantage is that COMMIT operations are much faster as they don’t involve a synchronous fsync() for every transaction.
  • Configuration:

    SET GLOBAL innodb_flush_log_at_trx_commit = 2;
    

    This setting is applied immediately to all new connections. For persistent changes, edit your my.cnf or my.ini file.

• 0:

  • What happens: The transaction log is written to the OS buffer, and InnoDB attempts to flush it to disk approximately once per second. This is a background process. The data file is also flushed separately.
  • The trade-off: This is the fastest option for commits, but it offers the least durability. If the MySQL server crashes, you can lose up to one second’s worth of transactions. If the operating system crashes or the server loses power, you will definitely lose transactions that were in the OS buffer and not yet flushed. This is essentially the same risk as setting innodb_flush_log_at_trx_commit=2, but the flushing interval is managed by InnoDB rather than the OS.
  • Configuration:

    SET GLOBAL innodb_flush_log_at_trx_commit = 0;
    

    Again, this is global and immediate. Edit my.cnf for persistence.

The choice between 1, 2, and 0 is a direct tuning knob for your application’s specific needs:

• 1 (Default): Maximum ACID durability. Use when data loss is absolutely unacceptable (e.g., financial transactions, critical user data). Performance is sacrificed for safety.
• 2: Good durability, significantly better performance than 1. Suitable for applications where losing a few seconds of data in a catastrophic system failure (OS crash, power loss) is acceptable, but losing data due to a MySQL server crash is not. Many web applications fall into this category.
• 0: Maximum performance, minimum durability. Use only when data loss is highly acceptable, such as in caching layers or for temporary data where re-generating it is trivial.

The crucial part to understand about innodb_flush_log_at_trx_commit = 2 is that it still guarantees that committed transactions are safe from MySQL server crashes. The durability guarantee is only broken if the operating system itself crashes or the server loses power before the OS has flushed its buffers to disk. This is a subtle but important distinction. The OS typically flushes its buffers to disk every few seconds, even without a direct fsync() call from an application.

When you set innodb_flush_log_at_trx_commit to 2 or 0, you are essentially trading the guarantee of synchronous, per-transaction disk writes for improved throughput, accepting a small window of potential data loss only in scenarios of complete system failure (OS/power outage).

The next thing you’ll likely grapple with is how to monitor the impact of these changes, often by looking at metrics like Innodb_os_log_written and Innodb_log_waits in SHOW GLOBAL STATUS.