MariaDB 5.3 has reached the release candidate milestone, and the 5.3 version promises a lot of new features and optimization (i.e in optimizer http://kb.askmonty.org/en/what-is-mariadb-53#query-optimizer). No surprise I wanted to check how all improvements affect general performance.
So I why don’t we run old good sysbench benchmark.
For the benchmark I took:
- HP ProLiant DL380 G6 box
- sysbench multitables oltp rw workload, 16 tables, 500mil rows each, total datasize about 30GB
- working threads from 1 to 256
- Versions: MariaDB 5.3.4, MySQL 5.5.20
- Data is stored on RAID10 HDD partition
- Like in all my recent benchmarks, I make throughput measurements each 10 sec, so we can see the stability of the throughout
The raw results, configuration and scripts are available on our Benchmarks Launchpad
The graphical results:
Throughput (more is better)
1 2 3 4 5 6 7 8 9 10 | Threads MariaDB 5.3.4 MySQL 5.5.20 Ratio 1 252 271 0.9298893 2 412 588 0.7006803 4 801 1097 0.7301732 8 1709 2205 0.7750567 16 3197 4076 0.7843474 32 3303 4166 0.7928469 64 3336 4150 0.8038554 128 3800 4170 0.9112710 256 3710 4131 0.8980876 |
I was surprised to see that MariaDB shows 20-30% worse throughput.
It seems many changes resulted to performance hit in general. I wonder whether MariaDB team runs performance regression benchmarks, and if they do, why do we see such performance decline.
> I was surprised to see that MariaDB shows 20-30% worse throughput.
Might it be that this actually compares the scalability of 5.1 versus 5.5? I think it would be more fair to compare MariaDB 5.3 against MySQL 5.1 (which 5.3 is based from). Perhaps, also use a workload that has queries where the new optimizations will provide a benefit.
Are you going to tell us why it might be slower?
Mark,
That might be the task for my next weekend project
Vadim,
Can you also post comparison numbers for MariaDB 5.2 and Percona Server?
Vadim,
I think because of Multi-Range-Read optimization in MariaDB
And in sysbench, there is a range benchmark which may be optimized by this.
To Davi Arnaut.
MariaDB 5.3 uses XtraDB engine which scales pretty well in both 5.1 and 5.5.
Now about fairness:
MariaDB 5.3 is RC for the next production ready MariaDB’s flagship database.
For MySQL the current pre-production product is 5.6.
So there we compare the future MariaDB with the current MySQL 5.5, and probably more fair would be to compare
MariaDB 5.3 vs MySQL 5.6.
Whilst I understand where you’re coming from, and agree to some extent, it doesn’t stop this feeling a bit like an Apples to Pears comparison. We know there were a whole heap of speed improvements that went in between MySQL 5.1 and 5.5, so to test against MariaDB based on 5.1 code-base pretty much leaves it a foregone conclusion.
Just double-checking for sanity as it’s not clear from the article, was this with the new optimizer or without?
If it’s possible to request, would it be feasible to get the results both with and without? Unfortunately I don’t have access to any suitable hardware to benchmark it myself 🙁
How do we know that it is not a regression in XtraDB? Too bad the latest Percona Server is not included in the benchmark.
Actually MariaDB team ran sysbench several times before releasing 5.3.4 and we did not have such a degradation as Vadim had, far from it. Our configuration was different though. Axel will probably write about his results.
Vadim – the launchpad repo has my.cnf and my.55.cnf. These are very different — innodb_buffer_pool_size, innodb_flush_log_at_trx_commit. What was used during the test?
2 alex.
Indeed, I never published benchmarks Percona Server vs MySQL 5.5, like this
http://www.mysqlperformanceblog.com/2012/01/17/benchmarks-of-new-innodb_flush_neighbor_pages/
from which you can’t get conclusion that Percona Server performs better than MySQL 5.5.
Mark,
It is my.55.cnf in this benchmark.
Interesting benchmarks. I think it is fair to compare MySQL 5.5.20 to MariaDB 5.3.4 as these are real choices people care about performance have now. The comparison against MySQL would be mainly of academic interest. These results highlight the real challenge MariaDB poses for users right now – do you want improved concurrency MySQL 5.5 provides or do you want Optimizer Changes MariaDB team developed ?
If your workload is simple (sysbench like) MySQL 5.5 would be natural choice, however if you have need for high performance with complicated queries, MariaDB has additional optimizations for, it can be good choice for Performance standpoint.
I hope MariaDB merge with MySQL 5.5 changes will be completed soon and we would be able to get advantage from advantages in both code line. Also getting ready for MySQL 5.6 already might be a good idea – over 1 year wait for MariaDB based on MySQL 5.5 code base is way too long.
> [..] MariaDB with the current MySQL 5.5, and probably more fair would be to compare MariaDB 5.3 vs MySQL 5.6.
If your goal was to compare prerelease products, sure, but that wasn’t the goal that you established in the first paragraph — “check how all improvements affect general performance”.
Vadim’s benchmarks for High Performance MySQL 3rd Edition in a simple readonly workload showed that MySQL 5.1 is actually a hair faster than 5.5, so a comparison against 5.1 might show even more of a gap, not less. See http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/
I think it is also a reasonable point of view that Vadim isn’t the one who’s deciding what to compare to what. A lot of performance claims have been made about MariaDB versus MySQL, but this blog post is one of the few credible benchmarks. I think the lack of published benchmarks from the MariaDB team leaves too much room for people to doubt, and is a conspicuous omission, so it would probably be helpful for the MariaDB team to publish their benchmarks. Here is one place where some performance claims are made, and there are hints at extensive benchmarks: http://www.odbms.org/blog/2011/09/mariadb-the-new-mysql-interview-with-michael-monty-widenius/ The obvious question is if so much benchmarking is being done, why isn’t it being made public? I would guess that most people aren’t going to think very far beyond the first explanation that comes to their minds.
I agree with Peter, right now you can’t have the best of all worlds. I speculate that when MySQL 5.6 is GA, the official MySQL from Oracle will have a clear advantage over GA versions of MariaDB in several common types of workloads. When will MariaDB based on MySQL 5.6 be released? I suppose that when MariaDB based on 5.5 is finished, we will have a rule of thumb that might be useful to estimate the lag. I’d expect (but I could be wrong) that it will take somewhat longer to port to 5.6, because unlike the 5.5 codebase where Oracle, Percona, and Monty Program made a lot of changes in somewhat disparate parts of the server, in MySQL 5.6 there will be a lot of changes that will potentially conflict — in MySQL 5.6 there are extensive changes to the query optimizer and the replication codebase, which have been changed a lot in MariaDB as well. These changes might be difficult to merge.
Before start arguing on fairness of the comparison we should get a confirmation that they are really bad for some typical configurations. As I said earlier we did not see such bad regression with our own benchmarking. Let’s first compare the numbers Vadim got and our numbers and let us figure out why they are so different.
I think that Vadim published the results too soon and now we are talking about this too soon. Vadim is a world-class expert on InnoDB performance. He should have spent more time trying to understand why it is slower before publishing the result. He didn’t and now everyone is spending/wasting time on this.
Note, Vadim is an expert at this. So if I say this enough times that might be more motivation for him to explain the results. Therefore I think we should ignore this result until that is done.
MariaDB 5.3.4 runs ontop XtraDB 1.0.17-13.0 which in turn is based on InnoDB 1.0.17 from MySQL 5.1. So indeed MySQL 5.1 or Percona Server 5.1.59-13.0 would be proper targets for a comparison with MariaDB 5.3.4.
As pointed out by Peter, sysbench runs simple queries only where MariaDB cannot demonstrate any benefits from its optimizer improvements.
Thanks Vadim for the benchmarks, definitely love to see more of these types of benchmarks 🙂
What about re-running benchmarks with 5.3.4 new optimizers disabled ? Would be interesting to see benchmarks for other ‘common type’ of work loads and more complex query work loads as well.
But I agree with Peter, I hope MariaDB on 5.5. code base comes sooner rather than later!
I agree with comparison of MariaDB 5.3 vs MySQL 5.5. Comparing it to MySQL 5.1 is really useless (at last to me). I hope MariaDB 5.5 get ready before MySQL 5.6.
Cache 🙂
Try joining a table with a nested query, include a where clause that is random for each iteration like the current stamp and insert a new record in one of the tables on each iteration.
Mysql is slow there as it does not support hash join. But it compensates by caching the results and the joined temp table. However if you insert a record you will force mysql to expire its cache related to that join.
Mariadb is faster with those joins but seems like its cache system could be improved:-)
That would explain the bue dots in the bottom of the chart. Is there cache hits ratio statistics?
I guess its a good thing for Percona if your benchmarks show MariaDB as slower. :/
Not able to edit post. So the benchmark on this page is accurate as per this post on MariaDB blog:
http://blog.mariadb.org/benchmarking-mariadb-5-3-4/
Read full response it contains some pretty good news about faster tps with InnoDB. Thanks for this article, seems it helped wake up a few thoughts on that end.