You are here

TPC-H Run on MySQL 5.1 and 6.0

MySQL Performance Blog - Thu, 10/04/2008 - 10:22pm

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 smaller than 1 slower:

Query MySQL 5.0.23 MySQL 6.0.4 Ratio Query1 370.50 372.16 1.00 Query2 724.63 623.68 0.86 Query3 328.25 354.62 1.08 Query4 7.95 8.08 1.02 Query5 154.36 161.65 1.05 Query6 29.35 38.51 1.31 Query7 322.04 331.05 1.03 Query8 469.31 457.18 0.97 Query9 123.75 121.33 0.98 Query10 343.37 341.23 0.99 Query11 2.32 2.34 1.01 Query12 28.68 38.61 1.35 Query13 46.00 47.84 1.04 Query14 8.83 8.23 0.93 Query15 21.50 23.61 1.10 Query16 55.48 61.42 1.11 Query17 9.00 9.33 1.04 Query18 N/A 1962.96 N/A Query19 5.03 5.30 1.05 Query20 2.06 0.26 0.13 Query21 33.16 32.98 0.99 Query22 7.84 8.06 1.03

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 Query1 3784.45 3737.54 0.99 Query2 NA NA NA Query3 NA NA NA Query4 NA NA NA Query5 NA NA NA Query6 NA NA NA Query7 4328.22 4533.62 1.05 Query8 8947.72 4122.62 0.46 Query9 NA NA NA Query10 NA NA NA Query11 2726.20 3395.02 1.25 Query12 NA NA NA Query13 NA NA NA Query14 2345.68 ? NA Query15 NA NA NA Query16 725.31 693.56 0.96 Query17 1895.55 NA NA Query18 NA NA NA Query19 4896.27 4682.19 0.96 Query20 3117.45 1450.42 0.47 Query21 NA NA NA Query22 108.56 100.72 0.93

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.

Entry posted by peter | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks