Changing innodb_flush_log_at_trx_commit in MySQL

Default value is 1, possible values are 0-2.

0 – Logs are written and flushed to disk once per second. Transactions that have not been flushed out can be lost as a result of a crash.
1 – Logs are written and flushed to disk every time a transaction is committed.
2 – Logs are written after each commit of a transaction and flushed to disk once per second. Transactions that have not been flushed out can be lost in the event of a failure.

For example, if a raid controller is used with a battery and a cache, then to improve write performance, you can specify the value 2, if without a battery and there is a possibility of a power outage, then you must leave the default value of 1, but this still does not guarantee against data loss for the last second, since for example some disks may tell MySQL that data writing has occurred, but the data may still be in the disk cache. If the value is different from 1, also a failure of the operating system can result in the loss of transactions in the last second. If set to 0, any MySQL crash can also erase transactions in the last second.

Let’s see the current value:

SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

You can change the value without restarting the MySQL server, but only for new connections:

SET GLOBAL innodb_flush_log_at_trx_commit=2;

Make sure that the value has changed by making a new connection to the MySQL server:

SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
connect
SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';

To prevent the value from being reset after restarting the MySQL server, we specify it in the configuration file:

[mysqld]
innodb_flush_log_at_trx_commit=2

See my other articles about MySQL

Leave a comment

Leave a Reply

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading