Testing the Group Commit Fix

PREVIOUS POST
NEXT POST

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:

Threads Nofix Group Commit fix
121.5121.99
230.6231.26
444.3251.42
852.3095.41
1656.65191.81
3259.96344.57
6460.05633.89
12858.181066.05
25657.621669.11

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:

Threads Nofix Group Commit fix
1352.83371.17
2682.41676.39
41265.791288.49
82319.122288.33
163098.333409.97
323180.263662.40
643074.083860.01
1282765.783829.70
2562180.252145.32

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:

Threads Nofix Group Commit fix
138.1933.11
249.3547.32
487.8886.97
8170.42170.58
16332.21331.26
32630.22654.35
641122.061116.02
1281912.971917.77
2561936.712004.13

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

thread trx=1, No binlog trx=0, No binlog trx=1, Binlog sync=0 trx=1, Binlog sync=1
159.45468.5933.1121.99
285.151021.5647.3231.26
4174.971672.9786.9751.42
8339.092947.88170.5895.41
16655.364536.96331.26191.81
321156.194523.90654.35344.57
642000.244496.201116.02633.89
1283066.644369.991917.771066.05
2562108.142044.852004.131669.11

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

And the similar with writeback cache enabled

thread trx=1, No binlog trx=1, Binlog sync=0 trx=1, Binlog sync=1
1382.39371.17381.03
2800.89676.39748.53
41385.481288.491309.64
82773.892288.332448.16
164395.823409.973522.43
324422.413662.403842.30
644389.143860.013888.56
1284345.843829.703859.48
2562877.982145.322188.38

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

PREVIOUS POST
NEXT POST

Comments

  1. Holger Thiel says

    This is a very important fix.

    MySQL Server 5.5 has a fix for the InnoDB Group Commit (innodb_support_xa=1). But sync_binlog=1 is still a performance killer – also with a battery-backed cache controller.

    Some graphics “MySQL Server 5.5 versus Percona Server 5.5″ for these cases are interesing.

  2. Andy says

    Vadim,

    Yes by cloud I meant something like EC2 where IO is slow and there’s no BBU.

    Galera is interesting. But doesn’t it require binlog too? If it does it’d suffer the same problem, no? Looking forward to your Galera benchmarks.

    Another option is handlersocket. But it’d require replacing all SQL and not all operations are supported.

  3. Andy says

    Thanks Vadim.

    With 128 threads, even with the latest group commit fix turning on binlog still results in a 70% performance drop (from 3000tps to 1000tps) when there is no BBU.

    So I guess it’s probably still not a good idea to run MySQL in a cloud?

  4. Vadim Tkachenko says

    Henrik,

    You probably misread results. Second graph is with write-back cache, and there is visible difference.

  5. Henrik Ingo says

    Interesting that there is absolutely no difference with the writeback cache on. I always assumed there would still be a small gain from group commit also in that case. I suppose I’m also interested to see that test re-done with more than 256 threads – maybe something happens there?

    In the same spirit as previous commenter, I think an interesting baseline is also to compare against a run with sync_binlog=0 or no-binlog, and possibly also innodb_flush_log_at_trx_commit=0. This would give a “best case” baseline to see how much MySQL can do when there are no synchronous fsyncs at all. It’s not a valid measurement for production use, rather it sets a goal for group commit developers to try to achieve. (Of course, it’s only an ideal to strive towards, not realistically achievable.)

  6. Vadim Tkachenko says

    Andy,

    As I still have all setup I can run benchmark without binlogs,
    I will update results.

    I use innodb-support-xa=1, as this is option that provides full recovery from binary logs.

  7. says

    Vadim,

    there is something wrong in the results you’ve obtained in the last test: performance simply cannot be better with binlog sync=1 comparing to sync=0 — it may be equal (in the best), but not better..

    Rgds,
    -Dimitri

Leave a Reply

Your email address will not be published. Required fields are marked *