innodb_flush_log_at_trx_commit in MySQL

MySQL's InnoDB storage engine uses redo logs to help maintain transaction durability and support crash recovery. The innodb_flush_log_at_trx_commit variable controls how transaction log data is written and flushed to storage. (dev.mysql.com)

It is an important setting because it involves a trade-off between performance and durability.

What Is innodb_flush_log_at_trx_commit?

You can check the current value with:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

The setting accepts three values: 0, 1, and 2. Its default value is 1. (dev.mysql.com)

Value 1

With the default value of 1, the log is written and flushed to disk at each transaction commit.

This provides the strongest durability and is the recommended setting when full transactional durability is required. (dev.mysql.com)

innodb_flush_log_at_trx_commit=1

Value 0

With 0, InnoDB writes and flushes the log approximately once per second rather than at every transaction commit.

This can reduce disk I/O, but transactions whose log records have not yet been flushed can be lost if the server crashes. (dev.mysql.com)

Value 2

With 2, the log is written after each transaction commit but flushed to disk approximately once per second.

This can provide better performance than 1, but recently committed transactions may still be lost after an unexpected crash. (dev.mysql.com)

Performance vs Durability

The three values represent different priorities:

1 → Strongest durability
2 → Reduced flush frequency
0 → More aggressive performance trade-off

For applications where losing recently committed transactions is unacceptable, 1 is generally the appropriate choice.

For workloads where performance is more important and a limited amount of recent transaction loss is acceptable, 0 or 2 may be considered. (dev.mysql.com)

Change the Setting

The variable is dynamic in supported MySQL versions, so it can be changed at runtime:

SET GLOBAL innodb_flush_log_at_trx_commit = 1;

For a persistent server configuration, add the setting to the MySQL configuration file as appropriate for your environment.

Recommended Setting

For production systems that require strong transactional durability, MySQL recommends:

innodb_flush_log_at_trx_commit=1

In replication environments using InnoDB transactions and binary logging, MySQL also recommends sync_binlog=1 for durability and consistency. (dev.mysql.com)

Important Consideration

The setting cannot completely overcome storage hardware or operating-system behavior. MySQL notes that if the operating system or storage hardware incorrectly reports that data has been flushed, transaction durability may still be affected. (dev.mysql.com)

Therefore, database durability also depends on reliable storage and an appropriate backup strategy.

Conclusion

innodb_flush_log_at_trx_commit is an important MySQL performance and reliability setting.

Use 1 when transactional durability is the priority. Values 0 and 2 can reduce flushing overhead but increase the possibility of losing recent transactions following an unexpected failure.

For most production databases where data consistency is critical, keeping the default value of 1 is the safest approach.