We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting.
This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple.
We tested 10G and 100G data sets which was CPU bound and IO bound box on the Dell 2950 box w 16G of RAM which we used for testing. Even though box had 8 cores it is little use for MySQL as only one query is ran concurrently, same can be told about 8 hard drives which this box had.
MySQL Also was very slow running some queries so we changed scripts a bit to kill extremely long running queries to get results for others this means we can’t really get a valid TPC-H result from MySQL,though at least we get to see performance of individual queries.
We also packaged the toolset we used for benchmarks so you can repeat them if you like. It can be downloaded here
So let us first take a look at MySQL 5.1.23 vs 6.0.4 results for 10GB data set which “fits in memory”. The cut-off time for this test was 1 hour so query taking over 1 hour has NA. Times are given in seconds.
Ratio is MySQL 6.0 time divided by MySQL 5.1 time so if it is less than 1 MySQL 6.0 is faster if more than 1 slower:
|Query||MySQL 5.0.23||MySQL 6.0.4||Ratio|
As you can see for 10G results there is only one query which does not complete within an hour in 5.1 and MySQL 6.0 can complete all queries within an hour.
We can also see MySQL 6.0 improving query 20 speed dramatically, the rest of the queries is however close and MySQL 6.0 is even significantly slower than 5.1 for
number of queries. Honestly I expected more from MySQL 6.0 optimizer improvements effort.
100GB Results are more interesting because this is database size for which you can find results published on TPC Web site. For example These Are results recently published for Microsoft SQL Server.
Of course this is much more powerful system but still you can get an idea – This system has geomean of 7.7 sec for Power test which is good order of magnitude better compared to MySQL results on 10G database.
TPC does discourage from comparing results against different database sizes so let’s see 100G data set results.
In this case we set cut-off time to 3 hours to give MySQL more time to complete the queries:
|Query||MySQL 5.0.23||MySQL 6.0.4||Ratio|
As you can see on 100G data set MySQL 5.1 could only complete 10 out of 22 queries within 3hours run time allowed for each query and MySQL 6.0 has similar number of queries it can execute in reasonable time frame. There 2 queries (Query8 and Query20) which MySQL 6.0 does better but there is also Query11 in which significant regression is observed. Vadim has already Wrote about it in his MySQL 5.1 vs 6.0 in TPC-H Queries post
As a Summary: We can see MySQL capabilities to run complex analytics queries, in particular those presented in TPC-H benchmark are still subpar even with changes which are currently seen in MySQL 6.0 tree. There is a long way till Release and may be MySQL 6.0 performance will improve. Though considering a lot of talks about optimizer improvements in MySQL 6.0 I expected drastically better results.
Though may we should not be as surprised by these results – MySQL 6.0 still does not have hash or merge join which are frequently used for these kind of queries, and there is also no query parallelization which would allow MySQL to use CPUs and hard drives this system has more efficiently. Parallel query will be growing issue for MySQL because CPUs just continue to add more cores. Having 2 CPUs and being able to use only half of system CPU resources for query processing was not too bad, however with 16,32,64 cores it is just too bad.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.