Testing the Group Commit Fix

As you may know, Kristian Nielsen made a fix for the Group Commit Problem which we many times wrote about. The fix came into MariaDB 5.3 and Mark Callaghan tested it recently . We ported this patch to Percona Server (it is not in the main branch yet), and here are the results of my testing of the new Group Commit in Percona Server 5.1.

As background information, the problem appears when you have strict durability and recover-ability requirements, that is innodb_flush_log_at_trx_commit=1, sync_binlog=1 and you do not have storage that provides fast syncs (i.e. you do not have a battery-backed cache on your RAID card). This scenario may also appear when being on battery and your RAID card dies, automatically switching from write-back to write-through cache mode.

We benchmarked the systems with sysbench multi-table and oltp read-write workload. All scripts, raw results and config files are available on our launchpad results page. The hardware used in the tests was a HP ProLiant DL380 G6 with HP Smart Array RAID10 over 8 disks and ability to disable/enable write-back cache online.

There are two versions tested: Percona Server 5.1.57 – released and Percona Server 5.1.57 with the Group Commit Fix applied.

The first graph shows the results when we have a slow fsync (write-back cache is disabled), innodb_flush_log_at_trx_commit=1 and sync_binlog=1

or in tabular format:

As you may see, the difference is pretty obvious.

I also ran a second test to measure whether there is any improvement when the battery cache is enabled:

As you can see, even with cache enabled, the fix provides throughput improvements in a multi-concurrent workload.

The last test was done with a disabled sync_binlog=0 and without write-back cache:

In this case, there is no improvement when using the fix.

In order to summarize: the fix improves performance substantially  when both innodb_flush_log_at_trx_commit=1 and sync_binlog=1 options are set and write-back cache is not enabled.

Write-back cache is must-have for an environment demanding performance, of course, yet batteries periodically die, and the fix provides a way to handle traffic until the battery is fixed.

We will include the Group Commit Fix in an upcoming Percona Server release.

It is also interesting to see how Flash cards will handle innodb_flush_log_at_trx_commit=1 and sync_binlog=1, I may run that benchmark sometime in the future.

UPDATE 14-Jul-2011 By requests from comments there is also result to compare binary logs enabled and disabled. All results are for binaries with group commit fix.

Results with disabled write cache

As you can see, enabling binary logs with system without writeback cache impacts performance significantly.

And the similar with writeback cache enabled

There is still performance difference binlogs vs no-binlogs, but it is much smaller when you have writeback cache.