MariaDB starting with 5.3

Group commit for the binary log was first introduced in MariaDB 5.3

Group commit is an important optimisation in MariaDB 5.3 when the server is run with innodb_flush_logs_at_trx_commit=1 and/or sync_binlog=1. These settings are needed to ensure that if a server crashes, any transaction that was reported as committed prior to the time of crash will still be present in the database after crash recovery ("durability", the "D" in ACID). Both settings are also necessary to be able to recover into a consistent state at all after a crash; if both are not set, it is possible after a crash to end up in a state where a transaction in the binary log is missing in InnoDB/XtraDB, or vice versa, which breaks replication for slaves.

When these settings are in effect, the server does an fsync() (or fdatasync() or similar) call on the XtraDB transaction log file / binary log file during COMMIT in order to ensure that data is stored durably on the disk. An fsync() is a time-consuming operation, and can easily limit throughput in terms of the number of commits per second which can be sustained.

The idea with group commit is to amortise the costs of each fsync() over multiple commits from multiple parallel transactions. If there are say 10 transactions in parallel trying to commit, we can force all of them to disk at once with a single fsync(), rather than do one fsync() for each. This can greatly reduce the need for fsync() calls, and consequently greatly improve the commits-per-second throughput.

Group commit works automatically; there are no options needed to enable it, and there are no negative consequences (performance or reliability or otherwise) from it.

However, to see the positive effects of group commit, the workload must have sufficient parallelism. Normally, at least three parallel transactions are needed for group commit to be effective: While the first transaction is waiting for fsync() to complete, the other two transactions will queue up waiting for their turn to call fsync(). When the first transaction is done, a single fsync() can be used for the two queued-up transactions, saving in this case one of the three fsync()s.

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

New Status Variables

For the binary log, two new status variables are available for checking how effective group commit is at reducing the number of fsync() calls needed:

binlog_commits

  • Variable Name: binlog_commits
  • Scope: Session
  • Data Type: Number

This is the total number of transactions committed to the binary log.

binlog_group_commits

  • Variable Name: binlog_group_commits
  • Scope: Session
  • Data Type: Number

This is the total number of group commits done to the binary log (a group commit is when a group of transactions are written into the binary log together, sharing a single fsync() call). When sync_binlog=1, then this is the number of fsync()'s done for transaction commits to the binary log

Thus the extent to which group commit is effective at reducing the number of fsync() calls on the binary log can be determined by the ratio between these two status variables. binlog_commits will always be as high or higher than binlog_group_commits; the bigger the difference, the more effective group commit was at reducing fsync() overhead.

To query these variables, use for example a statement like this:

SHOW STATUS LIKE 'binlog_%commits';

(Information about the effectiveness of group commit inside XtraDB can be obtained from similar status variables like innodb_data_fsyncs; however, the data may be slightly harder to interpret as XtraDB/InnoDB also uses fsync() sometimes for other purposes than (group) commits).

For more information about the design and implementation of group commit, see MWL#116.

See Also:

Comments

Comments loading...