As part of work on “High Performance MySQL, 3rd edition”, Baron asked me to compare different MySQL version in some simple benchmark, but on decent hardware.
So why not.
I took our Cisco UCS C250 and ran simple sysbench oltp read-write all data fits into memory workload.
Versions in question:
- MySQL 4.1
- MySQL 5.0
- MySQL 5.1 (with built-in InnoDB)
- MySQL 5.1 with InnoDB-plugin
- MySQL 5.5
- MySQL 5.6
All versions are vanilla MySQL, not Percona Server.
The results are there:
There is nothing unexpected, staring with InnoDB-plugin, MySQL scale much better on multi-cores, there is gain up to 1.7x in throughput.
What is interesting there, let’s take look on single thread results:
You can see that each new version is slower in single thread than previous.
It is actually easily explainable: each new version adds more features, which give more overhead.
Profiling MySQL 5.0 vs MySQL 4.1 couple years ago, I have seen biggest performance hit comes
from query parser. In MySQL 5.0 grammar became much bigger because of storage procedures and triggers.
And raw results, in tps (more is better)
| threads | MySQL 4.1 | MySQL 5.0 | MySQL 5.1 | MySQL 5.1+InnoDB-plugin | MySQL 5.5 | MySQL 5.6.2 |
|---|---|---|---|---|---|---|
| 1 | 685.52 | 639.73 | 596.01 | 593.51 | 531.07 | 525.80 |
| 2 | 1306.50 | 1221.40 | 1139.60 | 1139.20 | 1076.59 | 1018.64 |
| 4 | 2274.83 | 2167.92 | 2031.80 | 2042.51 | 1937.64 | 1830.80 |
| 8 | 3879.11 | 3746.29 | 3606.02 | 3681.36 | 3522.65 | 3320.10 |
| 16 | 4373.82 | 4527.04 | 4392.81 | 6130.70 | 5881.40 | 5572.50 |
| 32 | 4591.42 | 4864.46 | 4698.39 | 7762.32 | 7548.93 | 7138.54 |
| 64 | 4688.41 | 5078.02 | 4910.21 | 7535.65 | 7269.47 | 6994.04 |
Scripts and results are also on our Launchpad Benchmark project
Could you post the mysql configurations you used? Also may be helpful to call out that the server in question has only 12 cpu cores/24 threads, at first I was confused why performance went down from 32-64 thinking it was just a mysql scaling thing but may have more to do with the underlying hardware not being able to keep up.
Hi Vadim,
Can you also benchmark Percona, MariaDB, Drizzle? Those 3 are often seem as the improved-versions of MySQL. It’d be nice to see how they stack up against stock MySQL and each other.
It’s surprising to see 5.1+plugin actually performs better than 5.5, 5.6 in your benchmark, especially in high concurrency.
I thought 5.5 & 5.6 contain improvements especially for the high concurrency use case, so I expected them to be faster than 5.1+plugin.
Any ideas why they are actually slower?
nate,
I posted config file to Launchpad.
Andy,
MySQL 5.5 and 5.6 have good improvements for 256+ threads, I did not test to that amount of threads
in this benchmark.
In smaller threads, I am not sure why it is slower, probably just regular overhead of new features.
Vadim,
I wonder how MySQL 4.0 behaved… especially in single thread. I remember number of people saw regressions going to MySQL 4.1 and there might be a lot of reasons (like complex character set supports) for slowdown for 4.0->4.1
Other question is configuration – I see configuration file is large with a lot of stuff commented out. Do I understand correctly and you used same configuration for all versions, not enabling any special settings, such as multiple buffer pools on MySQL 5.5 and above ?
Also I’m not sure the config file posted (my.cnf) is correct at all. It contains:
innodb_log_file_size = 4000M
#innodb_log_file_size = 1024M
#innodb_log_file_size = 256M
innodb_log_files_in_group = 2
As you can see it is 4000M log file with 2 log file in group which is enabled, which will not work with MySQL before MySQL 5.6