There was small delay in our releases, part of this time we worked on features I mentioned before:
– Moving InnoDB tables between servers
– Improve InnoDB recovery time
and rest time we played with performance trying to align XtraDB performance with MySQL 5.4 ® and also port all performance fixes to 5.0 tree.
So basically we made: new split-buffer-mutex patch, which separate global buffer pool mutex into several small mutexes, and ported some Google IO fixes.
Here are results what we have so far. As usually for benchmarks I used our workhorse Dell PowerEdge R900 with 16 cores and 32GB of RAM and RAID 10 on 8 SAS disks. And again as usually our tpcc-mysql scripts with 100W (about 10GB of data) and 16 connection. For in-memory benchmark I set InnoDB buffer pool to 16GB, and for io-bound benchmarks I made it 3GB.
Raw results and rest of mysql parameters are here
http://spreadsheets.google.com/ccc?key=tOWmcwiHDD6-zNeXwe2k3Og
And let me post some graphs here.
In-memory benchmark, results during run:
And averaged final result:
As you see XtraDB, 5.0.83-percona and MySQL 5.4 are almost equal, leaving 5.0.83-standard far behind.
If you wonder about dips during run, that’s know problem with checkpoint activity, and we did not used innodb_adaptive_checkpoint in this case for XtarDB / 5.0.83-percona. And, by the way, Yasufumi also made new patch for second implementation of adaptive_checkpoint, and he will write about it eventually. But this is topic for another benchmark.
And now IO-bound results
Again you can see that XtraDB, 5.0.83-percona and 5.4 are almost equal, with difference we have get some extra performance turning InnoDB readahead off.
You may wonder why one now can choose XtraDB if 5.4 has the same performance, let me refresh:
– XtraDB is based on InnoDB plugin 1.0.3, with all its benefits like FAST INDEX CREATION, table compression
– XtraDB will support moving tables between servers
– XtraDB will be released with fast recovery patch
– XtraDB has additional performance fixes i.e. extra UNDO and ROLLBACK segments (full list see here https://www.percona.com/docs/wiki/percona-xtradb:patch:start)
And now 5.0-tree will contain all performance fixes and show the same results as 5.4
Both 5.0.83-percona and XtraDB-rel6 are available in source code yet, but binaries should be ready in one-two weeks timeframe.
Thanks for all your work with this. I’m anxious to test replication performance and bugfixes with 5.0.83-percona.
Just wondering, I’m used to phpmyadmin doing a lot of the work for me. Does it support XtraDB or does it cause conflicts when either converting tables to XtraDB or creating new tables?
Congrats on yet another great job!
Can you also benchmark XtraDX & percona vs. Google patch?
Would be interesting to see how they stack up against each other in IO-bound workload.
Jason,
XtraDB is visible as InnoDB, and work as drop-in replacement if InnoDB, so you should not have any problem with it.
Andy,
It’s hard to build Google version with only performance patches. And old MySQL version Google patch is designed for makes comparison even complexer.
But I have no objections if someone run it 🙂
Thanks for your work, results seems very nice.
5.0.83-b17 is in the TESTING directory. Do you recommend to use it in production or there is a reason why it is still in the TESTING folder?
Nice work. Have you run iibench lately? I don’t know if the IO bound tpcc-mysql runs have sufficient IO bottlenecks to demonstrate all of the benefits of these changes.
What about 5.1.36?
Any chance that either the 5.0.83-percona or 5.1.36-xtradb builds will be released as FreeBSD packages?
Hi,
Nice work 🙂 For information, you switched the two urls at the top (innoDB recovery and moving tables).
Great job, folks!!
Such kind of improvement makes MySQL’s future even more confident! 🙂
I’m surprised you did not use Adaptive Checkpoint option.. Why?..
Rgds,
-Dimitri
miloska,
TESTING means what it means 🙂 you can use it for testing purposes, but I can’t say it production ready yet.
Mark,
no, did not run iibench recently… maybe soon.
Sander,
What about 5.1.36 ? If you mean benchmarks for standard 5.1.36 I expect the results are very close to 5.0.83
fred,
We will do FreeBSD when there is interest from customers for FreeBSD binaries, or when we have enough spare time (not going to be soon 🙂 ) to do them.
Dimitri,
Thanks! Any chance you can try new XtraDB in your benchmarks ?
As for adaptive checkpoint I did not use it here to have the same conditions for XtraDB and 5.4
Olivier,
Thank you, fixed.
Vadim,
XtraDB tests are already in my TODO list 🙂
but vacations first 🙂
Regarding adaptive checkpoint – you should test it anyway as it’s a unique feature of XtraDB and presents a real value comparing to others!
Rgds,
-Dimitri
From a developer perspective it is good to compare with 5.4 and the big Google patch. But for users, the comparisons are to code that you might be willing to run in production which means official 5.0 and possibly official 5.1. And in that regard, XtraDB is way ahead.
Dimitri,
Why don’t you include adaptive_checkpoint into 5.4 ? We provide this patch under BSD license.
Vadim,
if it depended only on me it will be already included 🙂
but probably it’s only a question of time…
Rgds,
-Dimitri
Good work!
Is it possible to compile xtradb for Solaris/Sparc, or does it depend on x86 instruction?
Thank you
Isaac
Dimitri,
As you ( by you I mean Sun / MySQL, not you personally 🙂 ) started to include third-party patches, I thought it is not a problem to include the small patch. But even if our patch can’t be included, it is really not hard to make similar fixes.
Though I totally understand this question is not to you (this time you personally).
Isaac,
I never tried to compile XtraDB on Solaris.
I guess if you have gcc with ATOMIC_BUILTINS, it may work.
As Dimitri said in percona-discussions at googlegroup, you may need GCC 4.3 or 4.4
Vadim,
thanks, I was hoping it’s my fault why I can’t find it in other directories 🙂
Talking about the RPMs, may I ask why there’s no SSL support in your builds?
Is there any particular problem with it? Can safely rebuild the RPM with SSL support if I want to?
I’d like to see a freebsd port too ;o)
That’s great to see so many efforts to move that horse !
miloska,
We do not use widely mysql with SSL, and therefore we can’t test it as needed. That’s only reason why. I think you can change .spec to build RPM with SSL support, I do not problems.