MySQL 8 is not always faster than MySQL 5.7

PREVIOUS POST
NEXT POST

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

 

PREVIOUS POST
NEXT POST

Share this post

Comments (15)

  • Andy Reply

    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 Reply

    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 Reply

      Vadim,

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

      February 21, 2019 at 3:58 pm
      • vadimtk Reply

        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) Reply

          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 Reply

        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 Reply

          There is no free lunch as it says 🙂

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

      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
  • Peter Zaitsev Reply

    Vadim,

    Morgan brings an important question. I do not see character set configured in your my.cnf file. Are the servers using same character set for communication and table data ?

    February 23, 2019 at 7:35 am
    • Vadim Tkachenko Reply

      Peter,

      I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0

      February 25, 2019 at 1:55 pm
      • Geir Høydalsvik Reply

        Vadim, Do you have thoughts on “what is a realistic workload?”. For example, is SysBench considered a realistic workload, DBT3, …? And is latin1 more realistic than utf8mb4, or is it the other way around ?

        February 28, 2019 at 3:15 pm
        • vadimtk Reply

          Geir,

          I do not believe such thing as “realistic workload” exists. Different users and customers need different things.
          The best we can do is to stress different subsystems to understand how database performs under give conditions.

          February 28, 2019 at 3:25 pm
          • Michael D.

            my “realistic” rule of thumb is for app in dev. process use/switch to latest version.
            Supporting factors:
            1. Better for long run
            2. as @vadimtk said “I do not believe such thing as “realistic workload” exists…”

            March 1, 2019 at 12:47 pm
        • Peter Zaitsev Reply

          Geir, In my opinion for modern applications it is utf8 what matters so MySQL 8 being faster with this charset is great! However if we only get utf8 handling in MySQL 8 much faster but everything else got slower it is not a great trend.

          March 1, 2019 at 1:13 pm
  • Fixer Reply

    There’s a lot to digest here without a whole lot of information; however, I really think you need to modify most of your configuration options.

    Remove the deprecated entry

    Fix your log size (run the test to see what it should be)
    https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/

    Increase join_buffer_size, it’s set to the minimum.

    Fix innodb_file_per_table=1

    Fix the spacings with = and value (they should be set without the weird arrangement of spaces)

    etc… once fixed and optimized for your actual session needs, memory & cache, it should really chug along well.

    May 7, 2019 at 9:41 pm

Leave a Reply