Describes the group commit optimization, which improves performance by committing multiple transactions to the binary log in a single disk I/O operation.
The server supports group commit. This is an important optimization that helps MariaDB reduce the number of expensive disk operations that are performed.
In ACID terminology, the "D" stands for durability. In order to ensure durability with group commit, and/or 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 and provides the most durability and the best guarantee of consistency after a crash.
If is set, but is not set to 1 or 3, then it is possible after a crash to end up in a state where a transaction present in a server's is missing from the server's . If the server is a , then that means that the server can become inconsistent with its replicas, since the replicas may have replicated transactions from the primary's that are no longer present in the primary's local data.
If is set to 1 or 3, but is not set, then it is possible after a crash to end up in a state where a transaction present in a server's is missing from the server's . If the server is a , then that also means that the server can become inconsistent with its replicas, since the server's replicas would not be able to replicate the missing transactions from the server's .
Setting when is not set can also cause the transaction to be missing from the server's due to some optimizations added in those versions. In that case, it is recommended to always set . If you can't do that, then it is recommended to set to 3, rather than 1. See for more information.
After every transaction , the server normally has to flush any changes the transaction made to the and the 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.
The frequency of group commits can be changed by configuring the and system variables.
Two status variables are available for checking how effective group commit is at reducing flush overhead. These are the and status variables. We can obtain those values with the following query:
is the total number of transactions committed to the .
is the total number of groups committed to the . As explained in the previous sections of this page, a group commit is when a group of transactions is flushed to the together by sharing a single flush system call. When is set, then this is also the total number of flush system calls executed in the process of flushing commits to the .
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. will always be as equal to or greater than . 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 is also used to enable .
When both (the default) is set and the 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 for more information.
is the total number of transactions committed to the .
is the total number of groups committed to the .
is the total number of group commits triggered because of the number of commits in the group reached the limit set by the system variable .
is the total number of group commits triggered because a 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.
is the total number of group commits triggered because of the time since the first commit reached the limit set by the system variable .
To query these variables, use a statement such as:
This page is licensed: CC BY-SA / Gnu FDL
SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Binlog_commits | 120 |
| Binlog_group_commits | 120 |
+----------------------+-------+
2 rows in set (0.00 sec)SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Binlog_commits | 220 |
| Binlog_group_commits | 145 |
+----------------------+-------+
2 rows in set (0.00 sec)SHOW GLOBAL STATUS LIKE 'Binlog_%commit%';