MySQL 8 is not always faster than MySQL 5.7

MySQL 8.0.15 performs worse in sysbench oltp_read_write than MySQL 5.7.25

Initially I was testing group replication performance and was puzzled why MySQL 8.0.15 performs consistently worse than MySQL 5.7.25.

It appears that a single server instance is affected by a performance degradation.

My testing setup

mysql 8 slower than mysql 5.7 sysbenchHardware details:
Bare metal server provided by packet.net, instance size: c2.medium.x86
24 Physical Cores @ 2.2 GHz
(1 X AMD EPYC 7401P)
Memory: 64 GB of ECC RAM

Storage : INTEL® SSD DC S4500, 480GB

This is a server grade SATA SSD.

Benchmark

In the following summary I used these combinations:

  • innodb_flush_log_at_trx_commit=0 or 1
  • Binlog: off or on
  • sync_binlog=1000 or sync_binlog=1

The summary table, the number are transactions per second (tps – the more the better)

Summary: MySQL 8.0.15 is persistently worse than MySQL 5.7.25.

In the worst case with trx_commit=0  and sync_binlog=1000 , it is worse by 22%, which is huge.

I was looking to use these settings for group replication testing, but these settings, when used with MySQL 8.0.15, provide much worse results than I had with MySQL 5.7.25

(*)  in the case of trx_commit=0, binlog=off, MySQL 5.7.25 performance is very stable, and practically stays at the 11400 tps level. MySQL 8.0.15 varies a lot from 8758 tps to 10299 tps in 1 second resolution measurements

Update:

To clarify some comments, I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0

Appendix:


Photo by Suzy Hazelwood from Pexels

 

Share this post

Comments (17)

  • Andy

    I’ve seen many benchmarks that show MySQL 8.0 faster than 5.7. For example this: https://severalnines.com/blog/mysql-performance-benchmarking-mysql-57-vs-mysql-80

    And this: http://dimitrik.free.fr/blog/posts/mysql-performance-80-iobound-oltprw-vs-percona57.html

    Are there any rules of thumb about the conditions under which MySQL 8.0 is generally faster? Something like MySQL 8.0 is faster for read only workload but 5.7 is faster for read-write?

    February 21, 2019 at 3:15 pm
  • vadimtk

    Andy,

    In my experience I have yet to see the case when MySQL 8.0 is faster. Soon I will publish my read-only benchmarks where MySQL 8.0 is also slower.
    In my opinion Dimitri in his benchmarks is focusing on non-realistic configurations that are tuned and favorable for MySQL 8.0

    February 21, 2019 at 3:24 pm
    • Andy

      Vadim,

      That’s interesting. Do you recommend upgrading to MySQL 8.0 then?

      February 21, 2019 at 3:58 pm
      • vadimtk

        Andy,

        That’s tricky question. While the performance is important, it is not the only factor for the decision.
        MySQL 8.0 brings a lot of new features:
        https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
        So if you want to use some of those features, it might be worth to upgrade.
        Otherwise I would wait a little longer, till 8.0 becomes more polished.

        February 21, 2019 at 4:10 pm
        • Morgan Tocker (@morgo)

          I agree sysbench is often not realistic, but I think there is a case where MySQL 8.0 is a lot faster and matches real-world use cases: utf8mb4.

          It was not really a priority in earlier releases, but most users are using utf8mb4.

          February 21, 2019 at 5:17 pm
      • Pavel Katiushyn

        For mid and low loaded databases 5.5 is faster than 5.6, 5.6 better than 5.7. So no surprise here as well. I suppose this is the price for reacher functionality, that we get with every new version.

        February 21, 2019 at 4:16 pm
        • james wang

          There is no free lunch as it says 🙂

          February 22, 2019 at 5:24 am
    • Jason Perrone

      I noticed almost instantly that 8 is slower than 5.7. Disturbingly so. I was wondering if maybe I needed some new settings in my.cnf that I didn’t know about.

      April 10, 2019 at 7:33 am