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
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:
- Parallel doublewrite. In both MySQL 5.7 and MySQL 8 writes are serialized by writing to doublewrite.
- 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.
- 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.
You May Also Like
ZFS is debatably the most advanced file system compatible with Linux. ZFS offers pooled storage, copy-on-write, snapshots, data integrity verification, and more. Yves Trudeau, a Principal Architect at Percona, has written several blogs about ZFS. See: About ZFS Performance, Hands-On Look at ZFS with MySQL and ZFS from a MySQL perspective for notable insights into the file system.
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.
Mark,
These all are default in Percona Server 5.7
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.
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.
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.
Hi. I wonder about performance on Percona XtraDB Cluster. Will it be comparable to Percona 5.7? Thanks