September 22, 2014

Performance improvements in Percona 5.0.83 and XtraDB

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 http://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.

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. Bryan says:

    Thanks for all your work with this. I’m anxious to test replication performance and bugfixes with 5.0.83-percona.

  2. Jason says:

    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?

  3. Andy says:

    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.

  4. Vadim says:

    Jason,

    XtraDB is visible as InnoDB, and work as drop-in replacement if InnoDB, so you should not have any problem with it.

  5. Vadim says:

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

  6. miloska says:

    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?

  7. Mark Callaghan says:

    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.

  8. Sander says:

    What about 5.1.36?

  9. fred says:

    Any chance that either the 5.0.83-percona or 5.1.36-xtradb builds will be released as FreeBSD packages?

  10. Olivier says:

    Hi,

    Nice work :) For information, you switched the two urls at the top (innoDB recovery and moving tables).

  11. Dimitri says:

    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

  12. Vadim says:

    miloska,

    TESTING means what it means :) you can use it for testing purposes, but I can’t say it production ready yet.

  13. Vadim says:

    Mark,

    no, did not run iibench recently… maybe soon.

  14. Vadim says:

    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

  15. Vadim says:

    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.

  16. Vadim says:

    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

  17. Vadim says:

    Olivier,

    Thank you, fixed.

  18. Dimitri says:

    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

  19. Mark Callaghan says:

    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.

  20. Vadim says:

    Dimitri,

    Why don’t you include adaptive_checkpoint into 5.4 ? We provide this patch under BSD license.

  21. Dimitri says:

    Vadim,

    if it depended only on me it will be already included :-)
    but probably it’s only a question of time…

    Rgds,
    -Dimitri

  22. Isaac says:

    Good work!

    Is it possible to compile xtradb for Solaris/Sparc, or does it depend on x86 instruction?

    Thank you
    Isaac

  23. Vadim says:

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

  24. Vadim says:

    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

  25. miloska says:

    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?

  26. Nicolae Namolovan says:

    I’d like to see a freebsd port too ;o)

    That’s great to see so many efforts to move that horse !

  27. Vadim says:

    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.

Speak Your Mind

*