innodb_flush_log_at_trx_commit

Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant). 1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3.

See also: System Variables for MariaDB Enterprise Server 10.6, in 10.5 ES, and in 10.4 ES

USAGE

The innodb_flush_log_at_trx_commit system variable can have its default value configured via SkySQL Configuration Manager.

DETAILS

The innodb_flush_log_at_trx_commit system variable configures how often the transactions are flushed to the redo log:

  • When it is set to 0, nothing is done on commit; rather the log buffer is written and flushed to the InnoDB redo log once a second. This gives better performance, but a server crash can erase the last second of transactions.

  • When it is set to 1, the log buffer is written to the InnoDB redo log file, and a flush to disk performed after each transaction. This is required for full ACID compliance.

  • When it is set to 2, the log buffer is written to the InnoDB redo log after each commit, but flushing takes place once a second. Performance is slightly better, but a OS or power outage can cause the last second's transactions to be lost.

  • When it is set to 3, InnoDB emulates the older implementation of group commit, with 3 syncs to disk per group commit.

PARAMETERS

Dynamic

Yes

Scope

Global

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

3

Default Value

1

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

1 (choices: 0, 1, 2)

AWS

HA (Primary/Replica)

10.5

1 (choices: 0, 1, 2)

AWS

Transactional Standalone

10.4

1 (choices: 0, 1, 2)

AWS

Transactional Standalone

10.5

1 (choices: 0, 1, 2)

AWS

Distributed Transactions

10.5

1 (choices: 0, 1, 2)

GCP

ColumnStore

10.5

1 (choices: 0, 1, 2)

GCP

ColumnStore Multi

10.5

1 (choices: 0, 1, 2)

GCP

Galera

10.4

2 (choices: 0, 1, 2)

GCP

Galera

10.5

2 (choices: 0, 1, 2)

GCP

HA (Primary/Replica)

10.4

1 (choices: 0, 1, 2)

GCP

HA (Primary/Replica)

10.5

1 (choices: 0, 1, 2)

GCP

HTAP

10.5

1 (choices: 0, 1, 2)

GCP

Transactional Standalone

10.4

1 (choices: 0, 1, 2)

GCP

Transactional Standalone

10.5

1 (choices: 0, 1, 2)

GCP

Distributed Transactions

10.5

1 (choices: 0, 1, 2)

PRIVILEGES

The innodb_flush_log_at_trx_commit system variable requires the SUPER privilege to dynamically set its value at runtime.

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES