MariaDB innodb_flush_log_at_trx_commit Explained: Durability, Performance, and Best Practices

Introduction

The innodb_flush_log_at_trx_commit system variable controls when InnoDB writes and flushes transaction redo logs to disk.

Every committed transaction generates redo log records that help MariaDB recover data after an unexpected shutdown. This setting determines how aggressively those log records are synchronized with persistent storage.

Choosing the correct value involves balancing two important goals:

Transaction durability

Database performance

A safer configuration typically performs more disk writes, while a faster configuration may increase the risk of losing recently committed transactions during a system crash.

This guide explains how innodb_flush_log_at_trx_commit works, the differences between its available values, recommended configurations, and common tuning mistakes.

Test Environment

Operating System : AlmaLinux 10
PHP Version      : PHP 8.3
Web Server       : Nginx
PHP-FPM          : Enabled
Architecture     : x86_64
Last Tested      : July 2026

What Is innodb_flush_log_at_trx_commit?

This variable determines when MariaDB writes redo log data to the operating system and when it forces the operating system to flush that data to physical storage.

View the current value:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Example output:

+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_flush_log_at_trx_commit   | 1     |
+----------------------------------+-------+

How It Works

Each transaction generally follows this process:

Application
      │
      ▼
SQL Transaction
      │
      ▼
Redo Log Buffer
      │
      ▼
Redo Log File
      │
      ▼
Disk Storage

The value of innodb_flush_log_at_trx_commit determines when the last two steps occur.

Configuration Values

Value = 0

innodb_flush_log_at_trx_commit = 0

Behavior:

Redo logs are written and flushed approximately once per second.

Transactions may be lost if the server crashes before the next flush.

Advantages:

Highest write performance.

Lowest disk synchronization overhead.

Disadvantages:

Lowest transaction durability.

Value = 1

innodb_flush_log_at_trx_commit = 1

Behavior:

Every transaction commit writes and flushes the redo log immediately.

Advantages:

Maximum durability.

Fully ACID-compliant behavior.

Recommended for production databases.

Disadvantages:

More disk I/O.

Slightly lower write performance.

Value = 2

innodb_flush_log_at_trx_commit = 2

Behavior:

Every commit writes the redo log to the operating system.

The operating system flushes it to disk approximately once per second.

Advantages:

Better performance than value 1.

Lower disk synchronization overhead.

Disadvantages:

Some committed transactions may be lost after an operating system crash or power failure.

Comparison

| Value | Performance | Durability | Typical Use                              |
| ----- | ----------- | ---------- | ---------------------------------------- |
| 0     |     Highest |     Lowest | Development and testing                  |
| 1     |       Lower |    Highest | Production systems                       |
| 2     |        High |       Good | Performance-focused production workloads |

Check the Current Value

SHOW VARIABLES

LIKE 'innodb_flush_log_at_trx_commit';

Change the Configuration

Edit the MariaDB configuration file.

sudo nano /etc/my.cnf

Example:

[mysqld]

innodb_flush_log_at_trx_commit = 1

Save the file.

Restart MariaDB

Apply the configuration.

sudo systemctl restart mariadb

Verify:

sudo systemctl status mariadb

Expected output:

Active: active (running)

Verify the New Value

SHOW VARIABLES

LIKE 'innodb_flush_log_at_trx_commit';

Common Issues

Choosing Value 0 for Production

Although value 0 provides the best performance, unexpected crashes may cause the loss of recently committed transactions.

Assuming Value 2 Is Fully Durable

Value 2 still relies on the operating system to flush data to disk. If the operating system crashes or power is lost before the flush occurs, recent transactions may not be recoverable.

Blaming This Variable for Slow Queries

This variable affects transaction commit behavior, not SQL execution speed.

Slow queries are more commonly caused by:

Missing indexes

Inefficient SQL

Small innodb_buffer_pool_size

Storage bottlenecks

Relationship with Other Variables

| Variable                         | Purpose                                   |
| -------------------------------- | ----------------------------------------- |
| `innodb_log_file_size`           | Size of redo log files                    |
| `innodb_buffer_pool_size`        | Caches table data and indexes             |
| `innodb_flush_log_at_trx_commit` | Controls redo log flushing                |
| `innodb_flush_method`            | Determines how data is written to storage |

These settings should be considered together when optimizing InnoDB performance.

Best Practices

Use 1 for production systems that require maximum data durability.

Consider 2 only when a small risk of data loss is acceptable in exchange for improved write performance.

Use 0 mainly for development, testing, or benchmark environments.

Benchmark write-heavy workloads before changing the setting.

Verify the configuration after restarting MariaDB.

Related Articles

MariaDB Explained

MariaDB innodb_buffer_pool_size Explained

MariaDB innodb_log_file_size Explained

MariaDB innodb_flush_method Explained

MariaDB max_connections Explained

Conclusion

The innodb_flush_log_at_trx_commit variable determines when InnoDB writes and flushes redo log data, directly affecting transaction durability and write performance.

For most production environments, a value of 1 remains the recommended choice because it provides the strongest protection against data loss. Development and testing environments may prefer 0 for maximum performance, while 2 offers a compromise for workloads where slightly reduced durability is acceptable.