mysql8和5.7区别_MySQL 8、MySQL 5.7和Percona server for MySQL性能比较

本文主要比较一下MySQL 8, MySQL 5.7, and Percona server for MySQL之间性能。MySQL8.0版本性能和MySQL5.6版本相比没有任何改进和提升，Percona server for MySQL5.6总体性能比MySQL5.6提升60%。

Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this article, 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 setup. 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.

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, however, Oracle has not incorporated the solution upstream.

Single page eviction. This is another problematic area in MySQL’s flushing algorithm. The bug 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

There are a few outcomes I can highlight:Intel Optane is a very capable drive, and 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.