MySQL Benchmarks – MySQL 5.0.92 vs 5.1.55 vs 5.5.10 vs Percona 5.1.55 vs Percona 5.5.8 vs MariaDB 5.2.5
Summary
Sysbench tests resulted in some peculiar outcomes where MySQL 5.0.92 had better numbers. But this could be due to how Sysbench queries are run (prepared statements) and the differences in how MySQL’s query cache operates after MySQL 5.1.17+ versions.
For MyISAM storage engine, there hasn’t been much improvement across various MySQL versions. MariaDB 5.2.x series is the only one to focus somewhat on MyISAM performance by introducing Segmented Key Cache. You can see the difference in MyISAM read only benchmarks once past 12 to 16 cpu cores. MariaDB 5.2.5 also came out on top in InnoDB tests for DEADLINE and NOOP scheduler configurations at 16 cpu core mark. While MariaDB 5.2.5 InnoDB performance stopped scaling past 16 cpu cores, it’s drop at 24 cpu cores was still higher than all other MySQL versions.
Percona 5.1.55 closely resembled MySQL 5.1.55 but with much improved InnoDB performance. MySQL 5.1.55 InnoDB performance stopped scaling past 8 to 12 cpu cores. While Percona 5.1.55 InnoDB performance continued to scale up to 16 to 24 cpu cores.
MySQL 5.5.10 InnoDB scaling performance seem to center around 8 to 24 cpu cores. MySQL 5.5.10 InnoDB performance just edges out Percona 5.5.8-20 beta at 8 to 24 cpu cores and Percona 5.5.8-20 beta leads at 48 to 64 cpu cores. In both cases my.cnf settings used eight InnoDB buffer pool instances. I believe Percona folks did mention that Percona 5.5.8-20 beta might scale better with a single InnoDB buffer pool instances due to the way Percona tuned InnoDB scaling. I did try with just a single InnoDB buffer pool instance but the results were very close but lower than with 8 instances set.
Majority of vBulletin forum server and wordpress setups are still using MyISAM tables for reasons outlined above. Most I have come across are usually single or dual Intel quad core based with hyperthreading – so server configurations in the 8 to 16 cpu core range. For vBulletin folks looking at converting some of the suggested tables from MyISAM to InnoDB, be sure to do the necessarily research and reading of the differences in how to tune, and administer InnoDB tables and to understand the hardware resource requirements for optimal performance.
For both MyISAM and InnoDB performance, I’m currently leaning towards MariaDB 5.2.x series – just make sure to set the Segmented Key Cache options for potentially better MyISAM performance.
For vBulletin and wordpress users, the future looks bright when you combine better performing MySQL server versions with web acceleration technologies like Litespeed Cache and Varnish cache.
Special thanks to Stewart (woostar) for allowing me access to his new server for a few days. It’s not everyday you can get access to 64 cpu core server which costs US$20,000+ to build. The server has grown on me and don’t want to let it go – there’s just so many more benchmarks and MySQL configuration settings I could run. But I’ll continue testing on other server configurations when I have the chance, so be sure to follow me on twitter for notice when such benchmark results come out.
