XtraDB benchmarks – 1.5X gain in IO-bound load

XtraDB benchmarks – 1.5X gain in IO-bound load

PREVIOUS POST
NEXT POST

I guess it is first reaction on new storage engine – show me benefits. So there is benchmark I made on one our servers. It is Dell 2950 with 8CPU cores and RAID10 on 6 disks with BBU, and 32GB RAM on board with CentOS 5.2 as OS. This is quite typical server we recommend to run MySQL on. What is important I used Noop IO scheduler, instead of default CFQ. Disclaimer: Please note you may not get similar benefits on less powerful servers, as most important fixes in XtraDB are related to multi-core and multi-disks utilization. Also results may be different if load is CPU bound.


I compared MySQL 5.1.30 trees – MySQL 5.1.30 with standard InnoDB, MySQL 5.1.30 with InnoDB-plugin-1.0.2 and MySQL 5.1.30 with XtraDB (all plugins statically compiled in MySQL)

For benchmarks I used scripts that emulate TPCC load and datasize 40W (about 4GB in size), 20 client connections. Please note I used innodb_buffer_pool_size = 2G and innodb_flush_method=O_DIRECT to emulate IO bound load.

InnoDB parameters:

And for XtraDB I additionally used:

So what is in result:

Result is in NOTPM (New Order Transactions Per Minute), more is better. As you see XtraDB is somewhat 1.5x better than InnoDB in standard 5.1.30 and even more than InnoDB-plugin-1.0.2

And there is CPU utilization for all tested engines:

As you see XtraDB also utilizes CPUs better.

Finally let me show you why I took NOOP IO scheduler instead of CFQ, there are result for XtraDB with both:

4X difference is just giant one. And it is important to remember as Linux kernels 2.6.18+ (which are used on CentOS / RedHat 5.2) are coming with CFQ scheduler as default.
So

should be one of first things to do on new server (sure you also need to change kernel startup parameter to make it automatic after reboot).

PREVIOUS POST
NEXT POST

Share this post

Comments (30)

  • Martin Reply

    Good news! That is a huge benefit – just changing the plugin… 🙂

    December 18, 2008 at 3:28 am
  • erkan Reply

    Great,
    have you seen similar performanceimprovements (cencerning the noop-scheduler) for the std.InnoDB?

    December 18, 2008 at 4:04 am
  • Nils Reply

    Is there a specific reason why for having 3 logfiles in the group instead of the default 2?

    December 18, 2008 at 4:10 am
  • figvam Reply

    Interesting new data about the scheduler performance. I believe deadline scheduler was always the way to go for typical MySQL workload.
    Have you tested it against the noop scheduler?

    December 18, 2008 at 5:09 am
  • rickross Reply

    Very interesting data, and your hardware sounds quite like the hardware we’re just setting up for a new db server.

    What filesystem and RAID-related settings have you used in the test setup? Are you using hardware RAID, or is it software only?

    Rick

    December 18, 2008 at 6:23 am
  • rtfm Reply

    Looks very well !

    Any chance to do same benchmark (same hardware) with FreeBSD 7.1 ?

    December 18, 2008 at 7:45 am
  • Matt Reply

    Good stuff, a note on noop, depending on the hardware I have seen mixed results with this. For instance certain drives/controllers do better with noop, and others worse… i have even seen boosts in performance using deadline. It all seems to be very hardware dependent.

    December 18, 2008 at 8:08 am
  • peter Reply

    Hi,

    One thing I wanted to add if you’re looking for similar gains with MySQL 5.0 you can use Percona Patches https://www.percona.com/percona-lab.html.

    You can see these benchmark results here https://www.percona.com/files/presentations/percona-patches-opensql-2008.pdf

    December 18, 2008 at 9:42 am
  • peter Reply

    Matt,

    Indeed schedulers are workload specific and hardware specific and workload specific and we will write more about it. In this case we simply showed we had to change a scheduler from default to get very significant performance increase.

    The problem with Schedulers on OS level is they do not know a lot about underlaying hardware often assuming it is single drive you’re dealing with optimizing for seeks etc. If it is RAID with large number of hard drives, or SSD the assumptions IO schedulers operate are just wrong. NOOP reduces extra kernel load and just lets hardware do its magic. It does not always makes it best though it also can be side effect.

    December 18, 2008 at 9:58 am
  • peter Reply

    Another couple of notes here

    1) Even though Vadim called it IO bound benchmark, it is not completely the case. With data just 2x from the memory size this is mixed benchmark which loads both CPU and IO subsystem intensively, so it both cares about IO performance but also about contention issues. It would be interesting to see results for completely CPU bound and IO bound benchmarks too. I would guess at this data size TPC-C should be write prevailing… which is different from what a lot of people in reality have.

    2) The “emulated” IO workload, by small buffer pool size and innodb_flush_method=O_DIRECT is close but not always the same as real IO workload because there is OS cache, which is bypassed for Data but not for logs. When logs can be completely cached in memory this can a bit improve log write performance.

    December 18, 2008 at 10:06 am
  • Wilfried Schobeiri Reply

    I’ve been using deadline on my servers, and found it to have significant benefits (along the same order as noop in the above benchmark). Why noop instead of deadline?

    December 18, 2008 at 11:15 am
  • Vadim Reply

    Just a note if you want to use TPCC scripts you can get them from your perconatools project on Launchpad, https://launchpad.net/perconatools

    December 18, 2008 at 11:48 am
  • Vadim Reply

    Nils,

    No, there is no specific requirement to have 3 innodb_log_file

    December 18, 2008 at 11:51 am
  • Vadim Reply

    rtfm,

    We have no plans to run FreeBSD 7.1 on this hardware anytime soon.

    December 18, 2008 at 12:04 pm
  • mrk Reply

    Vadim, benchmarks of XtraDB on FreeBSD 7.0 or 7.1 is a very very very interesting things 🙂
    Can you publish any conditions which can motivate your team to do this?

    December 18, 2008 at 2:51 pm
  • Vadim Reply

    mkr,

    Basically we need time :). Beside that we just have no FreeBSD on board, so we need again time to install it. What is not very convenient for us, as current servers are in semy-production stage, and re-installing them back and forth is not good enough.
    If someone could give us equal server Dell 2950 with installed FreeBSD, we could run it there.

    December 18, 2008 at 3:35 pm
  • Kevin Burton Reply

    What’s the y-axis in the last graph?

    If you’re saying noop is 4x faster it probably depends on the RAID controller doing its own IO scheduling and CFQ confusing it…

    In our benchmarks noop always loses (obviously).

    Though on SSD it was faster.

    December 18, 2008 at 8:22 pm
  • mrk Reply

    Vadim, I can make some donation (paypal or webmoney) which will help you to buy one more HDD, and you’ll able to boot FreeBSD on the same server to run these and future benchmarks. These benchmarks (and Percona MySQL/plugins builds) are important for FreeBSD community, I hope %)

    December 18, 2008 at 9:07 pm
  • Kevin Burton Reply

    Vadim.

    I would be willing to donate some money (via paypal) to purchase a second hard drive to run these benchmarks on AmigaOS.

    They are important to our community 😛

    December 18, 2008 at 9:37 pm
  • Vadim Reply

    Kevin,

    Y-axis is NOTPM – New Order Transaction Per Minute, more is better.

    Sure, you are right, best scheduler depends on hardware. MegaRAID on Dell boxes seems quite smart here.

    December 18, 2008 at 9:38 pm
  • Kevin Burton Reply

    What controller did you test this on? Just curious

    December 18, 2008 at 9:47 pm
  • Vadim Reply

    mrk,

    regarding benchmarks – I think we need 6 disks there, one not enough :). Also server really lives in DataCenter and goes into production really soon. But point taken, we will try to organize things, I appreciate your interest.

    please drop me your contact to vadim @ thisblogdomain.

    December 18, 2008 at 9:56 pm
  • Vadim Reply

    Kevin,

    Controller LSI MegaRAID (PERC6)

    December 18, 2008 at 9:56 pm
  • GaryB Reply

    What about deadline vs cfq? Can we get more specifics about the Dell 2950? BIOS, PERC firmware version etc, disk model?

    December 19, 2008 at 6:15 pm
  • TS Reply

    Very nice work. BTW, can you release official XtraDB binaries for Solaris 10 and OpenSolaris 2008.11? The move toward ZFS is inevitable and native Linux ZFS is probably 2 years away, and there will be more and more people running opensolaris on x86_64 boxes.

    December 24, 2008 at 2:44 pm
  • Vadim Reply

    TS,

    We will do that as soon as we see interest from customers, so far we saw OpenSolaris only in non-production environment.

    December 24, 2008 at 3:16 pm
  • Daniel Reply

    I’d be very curious to see if anyone has performed any tests (w/ xtradb and noop scheduler) on machines utilizing the 3ware 9000 series controllers? The results are quite incredible and make me a bit trigger happy to try this on a few of our production db boxes.

    Thanks,

    December 26, 2008 at 11:48 am
  • Brad Fino Reply

    I too would like to see Percona w/ XtraDB binaries for Solaris 10 🙂

    February 5, 2009 at 9:20 pm
  • Vadim Reply

    Brad,

    You already contacted us I guess – you can easily make it happens 🙂

    February 5, 2009 at 9:23 pm
  • uzi4u Reply

    Yea,64bit Solaris/OpenSolaris binaries would be great 🙂

    June 22, 2009 at 9:48 am

Leave a Reply