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, but a lot more disk write operations reduce database performance. - 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.
The fastest option. 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.
Releem recommends '2' by default - it is affordable in most web apps. You can change it to '1' manually if you have critical and sensitive data.
Considering the intensity and type of your work, you can use them in your transactions.