« MySQL Performance: XtraDB-6 & others @dbSTRESS | Main | Butterfly & Summer... »

Thursday, 13 August, 2009

MySQL Performance: InnoDB plugin-1.0.4 & others @dbSTRESS

This post is an update of my previous one about XtraDB-6 performance - as InnoDB plugin-1.0.4 announce came the same day I did not have yet any benchmark results on that time :-)

To be short, the new InnoDB plugin looks very positive and have several very valuable improvement (and of course we expected to see them much more earlier, no? ;-) on the same time analyzing all latest updates - probably it's the first sign that things will go much more faster in the near future? ;-)

Anyway, what I liked with this release:

  • group commit is back! (and we should thank a lot Percona team for their efforts to get it fixed! ;-)
  • configurable number of I/O threads and I/O capacity (aligned now with XtraDB, Google patched and MySQL 5.4)
  • adaptive flushing (idea is similar to Percona's Adaptive Checkpoint, but more elegant)
  • read ahead algorithm changes
  • etc..

Full list of changes you may find from InnoDB changelog , (as well Mark Callaghan wrote a very good summary about, and hope you did not miss Yoshinori's post about his group commit tests ).

InnoDB plugin is missing a timer concurrency model for the moment. Probably it will come with the next release? ;-)

But what about results on dbSTRESS?..

Read-Only Workload

Observations :

  • with the latest Solaris 10 update7, MySQL is scaling better now on the read-only workload even with setting concurrency to zero!
  • InnoDB plugin looks very stable!

Observations :

  • while concurrency setting is not zero, only engines using a timer based model continue to keep workload
  • we may also ask as well: why use concurrency setting if the result is alredy "good enough"?.. - well, if you have only reads - it's ok.. But what if you also have writes? ;-)

Read+Write Workload

Observations :

  • as you may see, limiting thread concurrency on the read+write workload helps a lot to keep a growing load!
  • depending on how many active & concurrent sessions you have on your workload you may choose your optimal setting..

All other results as well all other details about this testing you may find from my full report: http://dimitrik.free.fr/db_STRESS_XtraDB_6_and_InnoDB_plugin_4_on_M5000_Aug2009.html

Long duration Read+Write Workload

I've also mentioned before the test is not log enough to reach the highest TPS level of each engine. For my other performance analyzing I've set up a long (1 hour) non-stop Read+Write test:

  • 5min Read-Only warm-up
  • 60min non-stop Read+Write
  • 32 concurrent sessions
  • 16 CPU cores
  • 50GB database

So, let me show a small graph:

The blue curve is representing a TPS level reached during this workload. There were all 4 engines tested one after other: MySQL 5.4, then InnoDB plugin-4, then MySQL.Perf build #45, and finally XtraDB-6. As you may see the throughput still continues to grow within 10-15 minutes, and only then reaches its higher level and become stable. So, the max (and stable!) Read+Write throughput will be:

Engine TPS
MySQL 5.4 8,200
InnoDB plugin-1.0.4 8,300
XtraDB-6 9,500
MySQL Perf build #45 11,500

Curiously I've already observed 12,500 TPS before with Perf build #5... (need to check if we did not break something with a time here..). But performance improvements made within the latest releases of XtraDB and InnoDB plugin are very impressive!

I may only say: Please, DON'T STOP! :-)

Any comments are welcome! :-)

Posted by Dimitri at 16:44
Categories: MySQL, Solaris, Tools/ dbSTRESS