innodb_flush_log_at_trx_commit

Overview

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.

USAGE

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

The innodb_flush_log_at_trx_commit system variable can be set in a configuration file:

[mariadb]
innodb_flush_log_at_trx_commit=1

The innodb_flush_log_at_trx_commit system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL innodb_flush_log_at_trx_commit=1;

When the innodb_flush_log_at_trx_commit system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

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.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--innodb_flush_log_at_trx_commit[=#]

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

3

Product Default Value

1

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

1 (choices: 0, 1, 2)

Single Node Analytics

10.6

1 (choices: 0, 1, 2)

Replicated Transactions

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

Single Node Transactions

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

GCP

Multi-Node Analytics

10.6

1 (choices: 0, 1, 2)

Single Node Analytics

10.6

1 (choices: 0, 1, 2)

Replicated Transactions

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

Single Node Transactions

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

1 (choices: 0, 1, 2)

Enterprise Server With Replica(s)

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

Enterprise Server Single Node

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

GCP

ColumnStore Data Warehouse

10.6

1 (choices: 0, 1, 2)

Enterprise Server With Replica(s)

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

1 (choices: 0, 1, 2)

Enterprise Server Single Node

10.4

1 (choices: 0, 1, 2)

10.5

1 (choices: 0, 1, 2)

10.6

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.

EXAMPLES

Full ACID Compliance

For full ACID compliance, set the innodb_flush_log_at_trx_commit system variable to 1. To also enable the greatest fault tolerance for the instance, enable the Binary Log and set the sync_binlog system variable to 1:

[mariadb]
innodb_flush_log_at_trx_commit=1
log_bin=mariadb-bin
sync_binlog=1

Dynamically Resetting Global Value to Default

The global value of the innodb_flush_log_at_trx_commit system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL innodb_flush_log_at_trx_commit=0;

SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
SET GLOBAL innodb_flush_log_at_trx_commit=DEFAULT;

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

Invalid Value for Variable

If a user tries to set the innodb_flush_log_at_trx_commit system variable to an invalid value, then the specific behavior depends on the value of the sql_mode system variable.

If the sql_mode system variable does not contain STRICT_ALL_TABLES and a user tries to set the innodb_flush_log_at_trx_commit system variable to an invalid value, then the operation will appear to succeed, but a warning will be raised with the ER_TRUNCATED_WRONG_VALUE error code. The warning will point out that the value was truncated, which means that the value was automatically set to the closest invalid value:

SET GLOBAL innodb_flush_log_at_trx_commit=-1;

SHOW WARNINGS;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_flush_log_at_trx_commit value: '-1' |
+---------+------+----------------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
SET GLOBAL innodb_flush_log_at_trx_commit=4;

SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_flush_log_at_trx_commit value: '4' |
+---------+------+---------------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 3     |
+--------------------------------+-------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the innodb_flush_log_at_trx_commit system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');

SET GLOBAL innodb_flush_log_at_trx_commit=-1;
ERROR 1231 (42000): Variable 'innodb_flush_log_at_trx_commit' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the innodb_flush_log_at_trx_commit system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL innodb_flush_log_at_trx_commit='';
ERROR 1232 (42000): Incorrect argument type to variable 'innodb_flush_log_at_trx_commit'
SET GLOBAL innodb_flush_log_at_trx_commit='infinity';
ERROR 1232 (42000): Incorrect argument type to variable 'innodb_flush_log_at_trx_commit'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

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