July 25, 2014

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:

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.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

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. Vadim Tkachenko says:

    Andy,

    Depends on what do you want to do in cloud… And by cloud I guess you mean EC2.
    It may be OK for mostly read-only workloads, but I would not expect encouraging results in write-intensive tasks.
    Or you may try galera replication :)
    http://www.mysqlperformanceblog.com/2011/07/14/percona-server-5-1-with-galera-replication/

  4. 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?

  5. Vadim Tkachenko says:

    Andy, Henrik,

    I updated post with results you asked. Please check it.

  6. Vadim Tkachenko says:

    Henrik,

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

  7. 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.)

  8. Andy says:

    Thanks Vadim.

    Looking forward to the update.

  9. 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.

  10. Andy says:

    Vadim,

    1) Can you also show the results with no bin-log at all? Like you did here: http://www.mysqlperformanceblog.com/2009/02/02/pretending-to-fix-broken-group-commit/

    Before the latest group commit fix turning on bin-log resulted in over 95% performance drop. I want to see how much performance drop does bin-log cause now with the group commit fix.

    2) In your results did you use innodb_support-xa=0 or =1? Can you break out the results for each setting?

    Thanks

  11. tobi says:

    Don’t stop at 256 threads! We wanna see what comes after that.

  12. Evgeny says:

    Vadim, can you please fix image links? They are now 404′d

  13. Dimitri 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

Speak Your Mind

*