Ensuring continuity and open collaboration

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

9 Comments

  1. Nirbhay Choubey Nirbhay Choubey
    2015-10-22    

    Axel, was 10.1 built with wsrep-patch?

  2. Anurag Anurag
    2015-10-23    

    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?

    • Axel Schwenke Axel Schwenke
      2015-10-23    

      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.

  3. 2015-10-23    

    Hi
    Oracle announced that the optimizer is rewirten. So is that the root cause for performance downgrade ?

    • Axel Schwenke Axel Schwenke
      2015-10-23    

      Unlikely. This simple benchmark doesn’t stress the optimizer at all.

      • Mike Mike
        2016-05-11    

        How useful is a benchmark that doesn’t stress the optimizer?

1 Trackback

  1. WebStorm 11 Beta, PhpStorm 10 Public Preview & Joomla! on 2015-10-23 at 14:02
  2. Confluence: Riskdata Cloud Solutions on 2016-03-11 at 15:27

Leave a Reply

Your email address will not be published. Required fields are marked *

Sponsors

MariaDB Foundation sponsors

Tweets by @mariadbfdn

Code statistics