Wednesday, 16 December, 2009
MySQL Performance: Breaking limits with XtraDB !
I've just finished my benchmark report about InnoDB current hottest internal contentions on the dbSTRESS workload. Since InnoDB-plugin-1.0.4 there is an excellent feature was added by Innobase team to monitor internal mutex lock waits: a compact output of "SHOW MUTEX STATUS" become much more useful then before! and helps greatly to understand InnoDB limits!
I've integrated innodbMUTEX stats within dim_STAT monitoring and was able to see performance limits from completely different axes!
Initially I've retested again:
- MySQL Perf-build5
- MySQL 5.4
on the same workload and using X4450 server (Intel CPU, 16cores, 16GB RAM (unfortunately all more powerful servers were busy :-)) as well it was sad for me that I've not used the updated XtraDB-8 code during my latest tests (there was something wrong with uploaded sources during announces, and I've discovered it only after discussion with Yasufumi)..
And obtained results are looking like this now:
placing XtraDB-8 in the #1 position!
NOTE: lock waits stats are not available for MySQL 5.4 and build5, but
you may compare plugin-1.0.4 and XtraDB-8 profilings - they are very
different the one from other! And observing their lock waits levels you
may easily understand why XtraDB today out-performs all others!..
Going further with XtraDB
And then I continued my investigations mainly with XtraDB, because:
- Because it has already combined together all most important improvements from MySQL 5.4 and InnoDB-plugin!
- And it has already several "ongoing" improvements integrated! :-)
- And it has its own valuable ideas! :-)
- It even has now a separated purge thread feature implemented! (it was not announced in the list of features for XtraDB-8, but you may find a new configuration parameter innodb_use_purge_thread (=0/1))
- It already implements buffer mutex split (and that's why we did not observe buffer lock waits!)
- It already implements extended rollback segments!
- And it already implements some fixes for dictionary locks!
So, until all these features are not shipped "officially" - it's very easy to validate their impact on your workload with XtraDB, and I'm pretty sure - more cases will be tested, better MySQL/InnoDB will be in the future! Don't wait! :-)
I'll avoid to copy & paste my report to the blog post, and will just say you that I was able to improve performance by 37% (!) again by using available in XtraDB features!
All details you may find within my benchmark report: http://dimitrik.free.fr/db_STRESS_XtraDB-8_Performance_Dec2009.html
Any comments ate welcome! And I'll be happy if you may share any lock waits observations regarding your own workloads (production or testing - doesn't matter)!
As the last word I want just to say: kudos Percona! :-) XtraDB once again become the performance #1 and going ahead of the all today's available InnoDB engine implementations, and it's great to see such a kind of positioning few days before of incoming XtraDB anniversary! :-)