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.
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.
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/
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?
Andy, Henrik,
I updated post with results you asked. Please check it.
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.)
Thanks Vadim.
Looking forward to the update.
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
Don’t stop at 256 threads! We wanna see what comes after that.
Vadim, can you please fix image links? They are now 404’d
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