EmergencyEMERGENCY? Get 24/7 Help Now!

Testing the Group Commit Fix

 | July 13, 2011 |  Posted In: MySQL

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
1 21.51 21.99
2 30.62 31.26
4 44.32 51.42
8 52.30 95.41
16 56.65 191.81
32 59.96 344.57
64 60.05 633.89
128 58.18 1066.05
256 57.62 1669.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
1 352.83 371.17
2 682.41 676.39
4 1265.79 1288.49
8 2319.12 2288.33
16 3098.33 3409.97
32 3180.26 3662.40
64 3074.08 3860.01
128 2765.78 3829.70
256 2180.25 2145.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
1 38.19 33.11
2 49.35 47.32
4 87.88 86.97
8 170.42 170.58
16 332.21 331.26
32 630.22 654.35
64 1122.06 1116.02
128 1912.97 1917.77
256 1936.71 2004.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
1 59.45 468.59 33.11 21.99
2 85.15 1021.56 47.32 31.26
4 174.97 1672.97 86.97 51.42
8 339.09 2947.88 170.58 95.41
16 655.36 4536.96 331.26 191.81
32 1156.19 4523.90 654.35 344.57
64 2000.24 4496.20 1116.02 633.89
128 3066.64 4369.99 1917.77 1066.05
256 2108.14 2044.85 2004.13 1669.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
1 382.39 371.17 381.03
2 800.89 676.39 748.53
4 1385.48 1288.49 1309.64
8 2773.89 2288.33 2448.16
16 4395.82 3409.97 3522.43
32 4422.41 3662.40 3842.30
64 4389.14 3860.01 3888.56
128 4345.84 3829.70 3859.48
256 2877.98 2145.32 2188.38

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

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

13 Comments

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

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

  • 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?

  • Henrik,

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

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

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

  • 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

  • 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

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.