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!

simplified OLTP, QPS for MySQL and MAriaDBFor those of you that like numbers better than charts:

simplified OLTP – queries per second
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:

relative results
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