MariaDB 10.1 and MySQL 5.7 performance on commodity hardware
When you have read my previous blog post about MariaDB 10.1 GA performance, you have probably wondered why I didn’t include any numbers for MySQL 5.7. There are two reasons: first MySQL wasn’t GA at that time and secondly MySQL is not running stable on Power8.
Today I will come up with a comparison benchmark. I have chosen some more down-to-earth hardware for that because that is what the majority of our users will be running. Specifically it’s a SP-64 cloud machine from OVH. It has a 4-core Intel CPU and 64G of RAM. Disks aren’t fancy, but the benchmark is again a simplified read-only OLTP workload that runs from memory.
To make things more interesting I added latest MySQL 5.6. So we have now 4 contenders: brand new MySQL 5.7 GA and it’s predecessor and brand new MariaDB 10.1 GA and it’s predecessor. Now after that many words: results!
For those of you that like numbers better than charts:
| clients | MariaDB 10.0.21 | MariaDB 10.1.8 | MySQL 5.6.27 | MySQL 5.7.9 |
|---|---|---|---|---|
| 1 | 22166 | 22748 | 24456 | 21499 |
| 2 | 41220 | 42155 | 45314 | 40552 |
| 4 | 70850 | 72865 | 78024 | 69816 |
| 8 | 112328 | 118933 | 126892 | 112442 |
| 16 | 113645 | 119503 | 129029 | 112502 |
| 32 | 113572 | 119663 | 127780 | 112925 |
| 64 | 111003 | 117255 | 125526 | 110663 |
| 128 | 111015 | 116137 | 124158 | 108568 |
| 256 | 111232 | 115688 | 116337 | 102345 |
So the winner is: MySQL 5.6.27! But there is more to be learned from those numbers. Lets look how the contenders do relatively to each other:
| clients | MariaDB 10.1 vs. MariaDB 10.0 | MySQL 5.7 vs. MariaDB 10.1 | MySQL 5.7 vs. MySQL 5.6 |
|---|---|---|---|
| 1 | +2,6% | -5,5% | -12,1% |
| 2 | +2,3% | -3,8% | -10,5% |
| 4 | +2,8% | -4,2% | -10,5% |
| 8 | +5,9% | -5,5% | -11,4% |
| 16 | +5,2% | -5,9% | -12,8% |
| 32 | +5,4% | -5,6% | -11,6% |
| 64 | +5,6% | -5,6% | -11,8% |
| 128 | +4,6% | -6,5% | -12,6% |
| 256 | +4,0% | -11,5% | -12,0% |
The surprising result of this comparison is that MySQL 5.7.9 is not only slower than MariaDB 10.1.8 (I guess you expected to read that in the MariaDB blog) but it is also significantly slower than MySQL 5.6.27. This unfortunate trend is something that has also been observed by others. MariaDB on the other hand could improve from 10.0.21 to 10.1.8.
Benchmark Details
The benchmark in use was again sysbench. The benchmark is using 1 million rows in a single table. Using single or multiple tables does not make much difference for such a small machine. The my.cnf was as follows:
[mysqld] max_connections = 300 table_open_cache = 600 innodb_buffer_pool_size = 512M performance-schema = false secure_file_priv = /tmp
Notice: the last line is only necessary for MySQL 5.7. And the second last line (disabling performance schema) is only necessary for MySQL. And yes, make sure you disable performance schema or MySQL performance will drop by a few more percent. MariaDB disables performance schema by default.
The sysbench command line was this:
sysbench-0.5 --test=lua/oltp.lua --oltp-table-size=1000000 --oltp-read-only=on --oltp_point_selects=1000 --oltp_sum_ranges=0 --oltp_simple_ranges=0 --oltp_order_ranges=0 --oltp_distinct_ranges=0 --max-requests=0 --max-time=100 --num-threads=... run
Axel, was 10.1 built with wsrep-patch?
This is our 10.1.8 source code package that anybody can download from downloads.mariadb.org. I didn’t apply any patch but built it straight with my standard build script for benchmarks.
I thought oracle announced 3x pert improvement with 5.7? My own measurements looked a lot like yours, but I don’t know if I should be configuring things differently. Do you know the setup they used?
Oracle isn’t very talkative about their benchmark platform (someting with 40 cores and 80 threads, so probably Intel?) or what specific configuration options they used. But if you can do your own benchmarks, then do it! And if you have real world workload then this is even better than a synthetic benchmark.
looks like intel e7s with 72 cpu cores 144 cpu threads @2.5ghz http://dimitrik.free.fr/blog/archives/2015/10/mysql-performance-yes-we-can-do-more-than-16m-qps-sql-on-mysql-57-ga.html
it’s always interesting to read both parties benchmark results 🙂
i would also love to see MariaDB offer intel architecture optimised rpm binaries as to `generic` compiled too. Would be interested to see how that changes performance in this predominantly intel server dominated world 🙂
Hi
Oracle announced that the optimizer is rewirten. So is that the root cause for performance downgrade ?
Unlikely. This simple benchmark doesn’t stress the optimizer at all.
How useful is a benchmark that doesn’t stress the optimizer?