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

MySQL version notes:

General Notes

• All my.cnf MySQL server configuration files are optimized specifically for the hard disk environment specific to the server consisting of 6x 64GB Intel X25-E SLC based SSD in raid 10 on Adaptec 5805 PCI-E SAS/SATA raid controller with battery back ups and Write Back cache enabled. If you use slower SATA or SAS/SCSI, then settings I used may not be optimal for your hard disk configuration. Usually, for SSD disks NOOP or DEADLINE scheduler would perform better. For SATA/SCSI/SAS, DEADLINE scheduler would perform better.
• For MyISAM tests the query_cache was enabled, but for InnoDB tests query_cache was disabled for better performance.
• For MySQL server performance forks such as Percona, MariaDB and MySQL 5.5, the InnoDB performance can be further improved by raising the innodb_log_buffer size at the expense of InnoDB recovery speed. However for comparisons I kept it at 128MB for all MySQL versions.

MySQL 5.0.92 vs 5.1.55

• MySQL 5.0.92 my.cnf and MySQL 5.1.55 my.cnf used for MyISAM.
• MySQL 5.0.92 my.cnf and MySQL 5.1.55 my.cnf used for InnoDB.
• MySQL 5.0.92 only tested default CFQ scheduler with queue length of 128.
• I noticed MySQL 5.0.92 had sometimes better performance than 5.1.55 for Sysbench tests and it could be related to the way in which Sysbench runs these tests and the differences in how MySQL below 5.1.7 and above 5.1.17+ versions’ query cache functions. I found this information from Matt Wood’s blog. Sysbench uses prepared statements which are mainly non-repeating selects. Query cache in MySQL versions below 5.1.7 ignored prepared statements but query cache in MySQL versions 5.1.17+ and higher can cache prepared statements. Unlike, in Matt’s results, I tried using –db-ps-mode=disable but tests results for MySQL 5.1.55 were pretty much the same as the default. So the testing in Sysbench used the default method throughout.
• MySQL 5.1.55 MyISAM read only performance was best with NOOP scheduler up to 32 cpu cores and DEADLINE at 48 and 64 cpu cores. While InnoDB performance are mixed at 4 cpu cores = NOOP, at 8 cpu cores = CFQ, at 16 cpu cores DEADLINE and NOOP near tie. Between 24 to 64 cpu cores = CFQ. For MySQL 5.1.55 MyISAM read/write performance it was pretty close between all three schedulers but DEADLINE just edged ahead.
• MySQL 5.155 InnoDB read/write performance CFQ led at 8 and 32-64 cpu cores. At 16 cpu cores pretty much even between the three schedulers.
• MySQL 5.1.55 overall favoured DEADLINE scheduler for MyISAM as well as InnoDB.

MariaDB 5.2.5

• MariaDB 5.2.5 MySQL my.cnf used for MyISAM.
• MariaDB 5.2.5 MySQL my.cnf used for InnoDB.
• For MyISAM engine, the introduction of Segmented Key Cache definitely improves MyISAM performance. I tested key_cache_segments values for 16, 32 and 64 and chose 32 as best value for tests. MariaDB 5.2.5 MyISAM performance and scaling for read only tests led the way coming out on top by as much as 20-28% at 12 and 16 cpu cores.
• MariaDB folks recommend CFQ or NOOP scheduler, but found MyISAM performance best with DEADLINE while InnoDB performance slightly better with CFQ scheduler.
• MariaDB InnoDB performance came out on top for all MySQL versions across all three schedulers.
• MariaDB 5.2.5 overall favoured DEADLINE or CFQ scheduler for MyISAM and CFQ scheduler for InnoDB.

MySQL 5.5.10

• MySQL 5.5.10 my.cnf used for MyISAM.
• MySQL 5.5.10 my.cnf used for InnoDB.
• Eight InnoDB buffer pool instances were used – a feature similar to MariaDB 5.2.x series Segmented Key Cache as a work around to reduce thread contention in the key cache buffer.
• MySQL 5.5.10 MyISAM scaling with seems to have performance drop off once past 12 cpu cores unless you reinstall Sysbench against the MySQL 5.5.10 client library due to incompatibilities with MySQL 5.5.10 and earlier MySQL 5.5 versions. But even after, MyISAM on CFQ scheduler seem to have unusually low scaling past 12 cpu cores. But due to time constraints unable to spend more time investigating.
• Sysbench read/write tests for InnoDB performance was definitely worse with DEADLINE scheduler. CFQ scheduler offered best InnoDB performance for Sysbench read/write tests at 8 and 24 cpu cores. While MySQL 5.5.10 InnoDB read/write performance was much lower at 8, 12 and 16 cpu cores. All three schedulers were close between 32 to 64 cpu cores. MySQL 5.5.10 InnoDB ready only performance favoured DEADLINE or NOOP.
• MySQL 5.5.10 overall favoured DEADLINE for MyISAM and NOOP scheduler for InnoDB.

Percona 5.1.55 12.6 & 5.5.8-20 beta

• Percona 5.1.55 12.6 my.cnf and 5.5.8-20 beta my.cnf used for MyISAM.
• Percona 5.1.55 12.6 my.cnf and 5.5.8-20 beta my.cnf used for InnoDB.
• For Percona 5.5.8-20 beta, eight InnoDB buffer pool instances were used – a feature similar to MariaDB 5.2.x series Segmented Key Cache as a work around to reduce thread contention in the key cache buffer.
• Like all other MySQL versions besides MariaDB 5.2.5, MyISAM performance is pretty much unchanged in terms of cpu core scalability.
• Percona has specifically mentioned they did not make any changes on MyISAM side for performance so their focus is on InnoDB performance which is evident in the results.
• Percona 5.1.55 MyISAM read only performance seem to not scale but keep an almost consistent result up until 32 cpu cores. But lags behind both MariaDB 5.2.5 and MySQL 5.5.10. Percona 5.1.55 MyISAM read/write performance came very close to matching MySQL 5.1.55 both of which performed better than MariaDB 5.2.5 and MySQL 5.5.10.
• Percona MyISAM and InnoDB read only scaling is interesting particularly with Percona 5.5.8-20 beta where there was a lower peak max transactions and requests per second but it linearly scaled all the way to 64 cores whereas other MySQL versions peaked at higher transactions and requests per second up to around 12-24 cpu cores and dropped off to similar levels by 64 cpu cores.
• Percona 5.5.8 InnoDB read/write tests peak at 16 cpu cores, then drops at 24 and 32 cpu cores and then a rises again at 48 and 64 cpu cores.
• Percona 5.1.55 overall favoured CFQ scheduler for MyISAM and NOOP for InnoDB.
• Percona 5.5.8-20 beta overall favoured CFQ scheduler for MyISAM and NOOP for InnoDB.