DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. On MySQL and Intel Optane Performance

On MySQL and Intel Optane Performance

Let's take a look at and explore the performance of MySQL 8, MySQL 5.7, and Percona server for MySQL.

Vadim Tkachenko user avatar by
Vadim Tkachenko
·
Jul. 17, 18 · Opinion
Like (1)
Save
Tweet
Share
5.18K Views

Join the DZone community and get the full member experience.

Join For Free

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:

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

Published at DZone with permission of Vadim Tkachenko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Efficiently Computing Permissions at Scale: Our Engineering Approach
  • 3 Examples of Address Autofill Forms to Collect Shipping and Payment Addresses
  • Taming Cloud Costs With Infracost
  • Express Hibernate Queries as Type-Safe Java Streams

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: