Binary Log Group Commit

In cases when strict durability and recoverability is required and the storage that provides fast syncs is unavailable, setting up the variables innodb_flush_log_at_trx_commit =1 and sync_binlog =1 can result in big performance drop.

Note

Variable innodb_flush_log_at_trx_commit makes sure that every transaction is written to the disk and that it can survive the server crash. In case the binary log is used for replication sync_binlog makes sure that every transaction written to the binary log matches the one executed in the storage engine. More information about these variables can be found in the MySQL documentation.

Performance drop happening when these variables are enabled is caused by additional fsync() system calls on both binary and XtraDB REDO log when committing a transaction, that are needed to store the additional information on the disk. Binary Log Group Commit feature can use a single fsync() call to force data to the storage for multiple concurrently committing transactions, which provides throughput improvements in a write-concurrent workload.

Because there are no negative effects of this feature, it has been enabled by default and can’t be disabled. Effects of this feature can be measured by the binlog_commits and binlog_group_commits status variables. The bigger the difference between these two variables the bigger is the performance gained with this feature.

Version Specific Information

  • 5.5.18-23.0

    Ported MariaDB Group commit for the binary log patch

Status Variables

variable binlog_commits
Command Line:Yes
Scope:Session
Variable Type:Numeric

This variable shows the total number of transactions committed to the binary log.

variable binlog_group_commits
Command Line:Yes
Scope:Session
Variable Type:Numeric

This variable shows the total number of group commits done to the binary log.

© Copyright Percona LLC and/or its affiliates 2009-2014.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.2.2.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.