Tuning innodb_flush_log_at_trx_commit variable

Basic Details

By flushing the log buffer to the storage, you can achieve maximum durability for a transaction. But, it has a significant impact on the performance of the disk. If you want to make sure it remains the same, you can use innodb_flush_log_at_trx_commit.
This parameter allows you to control how frequently the log buffer flushes to disk. It has different settings, which are given below:

  • 0 – The '0' setting does nothing during the transaction commitment. However, it writes the log buffer to the log file. It also flushes that file every second.
    But, there is a risk that if the system crashes, the flush is not guaranteed. To avoid problems, you should keep a backup of the latest committed transactions.

  • 1 – The '1' setting writes the log buffer to the log file, AND it does it to durable storage after every committed transaction. It is the most used setting as it is the safest and the default one.

  • 2 – The '2' setting write the log buffer to the file but does not flush the buffer. It flushes to the disk once per second.

  • 3 – When set to '3,' it emulates the older commit implementations with 3 syncs to disk per group. (MariaDB only)

Using the value "1" value is the best practice in terms of safety. That is because it does not follow the ACID properties otherwise. So, a transaction should always be at risk.

There is also a '3' value. It is also safe as it offers consistency similar to '1'. But, the benefit of '0' and '2' is that they are faster.

Considering the intensity and type of your work, you can use them in your transactions.

innodb_flush_log_at_trx_commit – Usage

There are two ways to set innodb_flush_log_at_trx_commit. The simple way to do it is in the configuration file.

Here's how you can activate it in MariaDB/MySQL/Percona:

You can also set the system variable dynamically at runtime with the following execution:
SET GLOBAL innodb_flush_log_at_trx_commit=1;

Once you set it dynamically at runtime, it's value will be changed when you restart the server. To avoid this, you will have to set in a configuration file also.