Understand how group commit works with InnoDB to improve performance by reducing the number of disk syncs required during transaction commits.
When both innodb_flush_log_at_trx_commit=1 (the default) is set and the binary log is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3).
Durability of commits is not decreased — this is because even if the server crashes before the commit is written to disk by InnoDB, it are recovered from the binary log at next server startup (and it is guaranteed that sufficient information is synced to disk so that such a recovery is always possible).
The old behavior, with 3 syncs to disk per (group) commit (and consequently lower performance), can be selected with the new option. There is normally no benefit to doing this, however there are a couple of edge cases to be aware of.
If is set and the is enabled, but is set, then commits are not guaranteed durable inside InnoDB after commit. This is because if is set and if the server crashes, then transactions that were not flushed to the binary log prior to the crash are missing from the binary log.
In this specific scenario, can be set to ensure that transactions are durable in InnoDB, even if they are not necessarily durable from the perspective of the binary log.
One should be aware that if is set, then a crash is nevertheless likely to cause transactions to be missing from the binary log. This will cause the binary log and InnoDB to be inconsistent with each other. This is also likely to cause any to become inconsistent, since transactions are replicated through the . Thus it is recommended to set . With the improvements introduced in , this setting has much less penalty in recent versions compared to older versions of MariaDB and MySQL.
and only see transactions that have been flushed to the . With the improvements, there may be a small delay (defined by the system variable) between when a commit happens and when the commit are included in a backup.
Note that the backup will still be fully consistent with itself and the . This problem is normally not an issue in practice. A backup usually takes a long time to complete (relative to the 1 second or so that is normally set to), and a backup usually includes a lot of transactions that were committed during the backup. With this in mind, it is not generally noticeable if the backup does not include transactions that were committed during the last 1 second or so of the backup process. It is just mentioned here for completeness.
This page is licensed: CC BY-SA / Gnu FDL