On MySQL and Intel Optane performance

On MySQL and Intel Optane performance


Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this blog post, I wanted to look at this in more detail and explore the performance of MySQL 8, MySQL 5.7 and Percona Server for MySQL using a similar set up. The Intel Optane is a very capable device, so I was puzzled that Dimitri chose MySQL options that are either not safe or not recommended for production workloads.

Since we have an Intel Optane in our labs, I wanted to run a similar benchmark, but using settings that we would recommend our customers to use, namely:

  • use innodb_checksum
  • use innodb_doublewrite
  • use binary logs with sync_binlog=1
  • enable (by default) Performance Schema

I still used charset=latin1  (even though the default is utf8mb4 in MySQL 8) and I set a total size of InnoDB log files to 30GB (as in Dimitri’s benchmark). This setting allocates big InnoDB log files to ensure there is no pressure from adaptive flushing. Though I have concerns about how it works in MySQL 8, this is a topic for another research.

So let’s see how MySQL 8.0 performed with these settings, and compare it with MySQL 5.7 and Percona Server for MySQL 5.7.

I used an Intel Optane SSD 905P 960GB device on the server with 2 socket Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz CPUs.

To highlight the performance difference I wanted to show, I used a single case: sysbench 8 tables 50M rows each (which is about ~120GB of data) and buffer pool 32GB. I ran sysbench oltp_read_write in 128 threads.

First, let’s review the results for MySQL 8 vs MySQL 5.7

After achieving a steady state – we can see that MySQL 8 does not have ANY performance improvements over MySQL 5.7.

Let’s compare this with Percona Server for MySQL 5.7

MySQL 8 versus Percona Server with heavy IO application on Intel Optane

Percona Server for MySQL 5.7 shows about 60% performance improvement over both MySQL 5.7 and MySQL 8.

How did we achieve this? All our improvements are described here: https://www.percona.com/doc/percona-server/LATEST/performance/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html. In short:

  1. Parallel doublewrite.  In both MySQL 5.7 and MySQL 8 writes are serialized by writing to doublewrite.
  2. Multi-threaded LRU flusher. We reported and proposed a solution here https://bugs.mysql.com/bug.php?id=70500. However, Oracle have not incorporated the solution upstream.
  3. Single page eviction. This is another problematic area in MySQL’s flushing algorithm. The bug https://bugs.mysql.com/bug.php?id=81376 was reported over 2 years ago, but unfortunately it’s still overlooked.

Summarizing performance findings:

  • For Percona Server for MySQL during this workload, I observed 1.4 GB/sec  reads and 815 MB/sec  writes
  • For MySQL 5.7 and MySQL 8 the numbers are 824 MB/sec reads and  530 MB/sec writes.

My opinion is that Oracle focused on addressing the wrong performance problems in MySQL 8 and did not address the real issues. In this benchmark, using real production settings, MySQL 8 does not show any significant performance benefits over MySQL 5.7 for workloads characterized by heavy IO writes.

With this, I should admit that Intel Optane is a very performant storage. By comparison, on Intel 3600 SSD under the same workload, for Percona Server I am able to achieve only 2000 tps, which is 2.5x times slower than with Intel Optane.

Drawing some conclusions

So there are a few outcomes I can highlight:

  • Intel Optane is a very capable drive, it is easily the fastest of those we’ve tested so far
  • MySQL 8 is not able to utilize all the power of Intel Optane, unless you use unsafe settings (which to me is the equivalent of driving 200 MPH on a highway without working brakes)
  • Oracle has focused on addressing the wrong IO bottlenecks and has overlooked the real ones
  • To get all the benefits of Intel Optane performance, use a proper server—Percona Server for MySQL—which is able to utilize more IOPS from the device.

Share this post

Comments (6)

  • Mark Callaghan Reply

    Thanks for a nice report.

    Were I still dependent on InnoDB I would be thrilled with the fixes you mention above that are in Percona Server. Are those improvements enabled by default in recent Percona Server 5.7 releases?

    30gb for InnoDB logs — seems expensive when that is on Optane or even on NAND flash.

    July 13, 2018 at 11:36 am
  • Gijs Reply

    I’d like to see the same benchmark performed on MariaDB 10.3.8.
    We’re currently using MariaDB as they release a Windows version, however if Percona performs much better than MariaDB we might consider switching the database to a Linux Hyper-V guest.

    July 14, 2018 at 5:34 am
    • Vadim Tkachenko Reply

      I did not test MariaDB 10.3, but as they switched to MySQL version of InnoDB, I anticipate the result for MariaDB will be closer to MySQL than to Percona Server.
      However I need to clarify that such difference you will see only in specific scenarios: you have very fast IO storage (like Intel Optane) and you have intensive read-write IO operations.
      For the slower storage and for less intense IO, the performance difference might be not so big.

      July 14, 2018 at 7:25 pm
  • Mark Callaghan Reply

    Great report, but I want to see results from a test that is more write-heavy. Like sysbench update-index, update-nonindex, insert-only or even my favorite, the insert benchmark. Those will give Percona Server a chance to shine.

    August 14, 2018 at 1:41 pm
  • German Estrada Pacheco Reply

    Hi. I wonder about performance on Percona XtraDB Cluster. Will it be comparable to Percona 5.7? Thanks

    November 22, 2018 at 9:55 am

Leave a Reply