innodb_flush_log_at_trx_commit

The innodb_flush_log_at_trx_commit system variable sets how frequently the redo log is flushed of transactions.

Releem automatically tunes innodb_flush_log_at_trx_commit and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.

There are only 3 or 4 (MariaDB only) value options when configuring innodb_flush_log_at_trx_commit. Configuring this variable dictates how committed transactions are handled before flushing. The InnoDB transaction log cannot continue to grow to an obscene size without performance implications. A large transaction log will also increase the recovery time after a crash, so it needs to be flushed regularly.

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:
innodb_flush_log_at_trx_commit=1

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.

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 and flushes that file every second.
  The fastest option, 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.
  The safest option. It is the most used setting as it is the safest and the default one, but a lot more disk write operations reduce database performance.
• 2 – The '2' setting writes the log buffer after each transaction commit but does not flush the buffer to the disk. It flushes to the disk once per second.
  This option offers a balance between performance and data safety. It reduces write-load a lot, but you can lose 1 second of data if there is a power failure or kernel crash.
• 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.