Group Commit for the Binary Log - Source

You are viewing an old version of this article. View the current version here

Revision: 77772
User: Kristian Nielsen
Date: 2019-01-15 16:14

<<toc>>

<<product mariadb from=5.3>>
Group commit for the [[binary-log|binary log]] was first introduced in MariaDB 5.3
<</product>>

== Overview

In MariaDB 5.3 and above, the server supports group commit. This is an important optimization that helps MariaDB reduce the number of expensive disk operations that are performed.

== Durability

In ACID terminology, the "D" stands for durability. In order to ensure durability with group commit, ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## and/or ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]## should be set. These settings are needed to ensure that if the server crashes, then any transaction that was committed prior to the time of crash will still be present in the database after crash recovery.

Setting both ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## and ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]## provides the best guarantee of consistency after a crash.

If ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]## is set, but ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## is not set, then it is possible after a crash to end up in a state where a transaction present in the [[binary-log|binary log]] is missing from the [[xtradbinnodb-redo-log|InnoDB redo log]]. This will make the master different from the slaves, and will thus break replication.

Similarly, if ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## is set, but ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]## is not set, then it is possible after a crash to end up in a state where a transaction present in the [[xtradbinnodb-redo-log|InnoDB redo log]] is missing from [[binary-log|binary log]]. This again will cause master and slaves to differ and replication to break. In MariaDB 10.0 and above, this configuration can also cause the transaction to be missing from [[xtradb-and-innodb|InnoDB]] due to some optimizations made to the ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## option in those versions. In that case, it is recommended to always set ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]##. See [[binary-log-group-commit-and-innodb-flushing-performance/#non-durable-binary-log-settings|Non-durable Binary Log Settings]] for more information.

== Amortizing Disk Flush Costs

After every transaction ##[[commit|COMMIT]]##, the server normally has to flush any changes the transaction made to the [[xtradbinnodb-redo-log|InnoDB redo log]] and the [[binary-log|binary log]] to disk (i.e. by calling system calls such as ##fsync()## or ##fdatasync()## or similar). This helps ensure that the data changes made by the transaction are stored durably on the disk. Disk flushing is a time-consuming operation, and can easily impose limits on throughput in terms of the number of transactions-per-second (TPS) which can be committed.

The idea with group commit is to amortize the costs of each flush to disk over multiple commits from multiple parallel transactions. For example, if there are 10 transactions trying to commit in parallel, then we can force all of them to be flushed disk at once with a single system call, rather than do one system call for each commit. This can greatly reduce the need for flush operations, and can consequently greatly improve the throughput of transactions-per-second (TPS).

However, to see the positive effects of group commit, the workload must have sufficient parallelism. A good rule of thumb is that at least three parallel transactions are needed for group commit to be effective. For example, while the first transaction is waiting for its flush operation to complete, the other two transactions will queue up waiting for their turn to flush their changes to disk. When the first transaction is done, a single system call can be used to flush the two queued-up transactions, saving in this case one of the three system calls.

In addition to sufficient parallelism, it is also necessary to have enough transactions per second wanting to commit that the flush operations are a bottleneck. If no such bottleneck exists (i.e. transactions never or rarely
need to wait for the flush of another to complete), then group commit will provide little to no improvement.

== Changing Group Commit Frequency

<<product mariadb from=10.0>>
The frequency of group commits can be changed by configuring the ##[[replication-and-binary-log-system-variables/#binlog_commit_wait_usec|binlog_commit_wait_usec]]## and ##[[replication-and-binary-log-system-variables/#binlog_commit_wait_count|binlog_commit_wait_count]]## system variables, which were introduced in MariaDB 10.0.
<</product>>

== Measuring Group Commit Ratio

Two status variables are available for checking how
effective group commit is at reducing flush overhead. These are the ##[[replication-and-binary-log-status-variables#binlog_commits|Binlog_commits]]## and ##[[replication-and-binary-log-status-variables#binlog_group_commits|Binlog_group_commits]]## status variables. We can obtain those values with the following query:

<<sql>>
SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
<</sql>>

##[[replication-and-binary-log-status-variables#binlog_commits|Binlog_commits]]## is the total number of transactions committed to the [[binary-log|binary log]].

##[[replication-and-binary-log-status-variables#binlog_group_commits|Binlog_group_commits]]## is the total number of groups committed to the [[binary-log|binary log]]. As explained in the previous sections of this page, a group commit is when a group of transactions is flushed to the [[binary-log|binary log]] together by sharing a single flush system call. When ##[[replication-and-binary-log-server-system-variables#sync_binlog|sync_binlog=1]]## is set, then this is also the total number of flush system calls executed in the process of flushing commits to the [[binary-log|binary log]].

Thus the extent to which group commit is effective at reducing the number of flush system calls on the binary log can be determined by the ratio between these two status variables. ##[[replication-and-binary-log-status-variables#binlog_commits|Binlog_commits]]## will always be as equal to or greater than ##[[replication-and-binary-log-status-variables#binlog_group_commits|Binlog_group_commits]]##. The greater the difference is between these status variables, the more effective group commit was at reducing flush overhead.

To calculate the group commit ratio, we actually need the values of these status variables from two snapshots. Then we can calculate the ratio with the following formula:

##transactions/group commit## = (##Binlog_commits## (//snapshot2//) - ##Binlog_commits## (//snapshot1//))/(##Binlog_group_commits## (//snapshot2//) - ##Binlog_group_commits## (//snapshot1//))

For example, if we had the following first snapshot:

And the following second snapshot:

Then we would have:

##transactions/group commit## = (220 - 120) / (145 - 120) = 100 / 25 = 4 ##transactions/group commit##

If your group commit ratio is too close to 1, then it may help to [[group-commit-for-the-binary-log/#changing-group-commit-frequency|change your group commit frequency]].

== Use of Group Commit with Parallel Replication

In MariaDB 10.0 and above, group commit is also used to enable [[parallel-replication/#conservative-mode-of-in-order-parallel-replication|conservative mode of in-order parallel replication]].

== Effects of Group Commit on InnoDB Performance

In MariaDB 10.0 and above, when both ##[[xtradbinnodb-server-system-variables#innodb_flush_log_at_trx_commit|innodb_flush_log_at_trx_commit=1]]## (the default) is set and the [[binary-log|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). See [[binary-log-group-commit-and-innodb-flushing-performance|Binary Log Group Commit and InnoDB Flushing Performance]] for more information.

<<product MariaDB from=10.0>>
The [[binary-log-group-commit-and-innodb-flushing-performance|InnoDB flushing performance improvements]] related to group commit were first introduced in MariaDB 10.0.
<</product>>

== Status Variables

##[[replication-and-binary-log-status-variables#binlog_commits|Binlog_commits]]## is the total number of transactions committed to the [[binary-log|binary log]].

##[[replication-and-binary-log-status-variables#binlog_group_commits|Binlog_group_commits]]## is the total number of groups committed to the [[binary-log|binary log]].

##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_count|Binlog_group_commit_trigger_count]]## is the total number of group commits triggered because of the number of [[binary-log|binary log]] commits in the group reached the limit set by the system variable ##[[replication-and-binary-log-system-variables/#binlog_commit_wait_count|binlog_commit_wait_count]]##.

##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_lock_wait|Binlog_group_commit_trigger_lock_wait]]## is the total number of group commits triggered because a [[binary-log|binary log]] commit was being delayed because of a lock wait where the lock was held by a prior binary log commit. When this happens the later binary log commit is placed in the next group commit.

##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_timeout|Binlog_group_commit_trigger_timeout]]## is the total number of group commits triggered because of the time since the first [[binary-log|binary log]] commit reached the limit set by the system variable ##[[replication-and-binary-log-system-variables/#binlog_commit_wait_usec|binlog_commit_wait_usec]]##.

<<product MariaDB from=10.0>>
The ##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_count|Binlog_group_commit_trigger_count]]##, ##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_lock_wait|Binlog_group_commit_trigger_lock_wait]]##, and ##[[replication-and-binary-log-status-variables/#binlog_group_commit_trigger_timeout|Binlog_group_commit_trigger_timeout]]## status variables were first introduced in MariaDB 10.0.18 and MariaDB 10.1.5.
<</product>>

To query these variables, use a statement such as:

<<sql>>
SHOW GLOBAL STATUS LIKE 'Binlog_%commit%';
<</sql>>

== See Also

* [[http://worklog.askmonty.org/worklog/Server-Sprint/?tid=116|MWL#116 - Efficient group commit for binary log]]
* [[parallel-replication|Parallel Replication]]
* [[binary-log-group-commit-and-innodb-flushing-performance|Binary Log Group Commit and InnoDB Flushing Performance]]
* [[what-is-mariadb-53|What is MariaDB 5.3]]
* [[http://www.facebook.com/note.php?note_id=10150211546215933|Group commit benchmark]]

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Group Commit for the Binary Log - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB