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.
5.5.18-23.0Ported MariaDB Group commit for the binary log patch
binlog_commits¶| Command Line: | Yes |
|---|---|
| Scope: | Session |
| Variable Type: | Numeric |
This variable shows the total number of transactions committed to the binary log.
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.
For general inquiries, please send us your question and someone will contact you.
