Skip to main content
Background Image
  1. Database Guru/

MySQL Performance Getting Worse, Where Is Sakila Going?

·1382 words·3 mins· ·
Table of Contents
MySQL走好 - This article is part of a series.
Part 2: This Article

Author: Marco Tusa | Translator: Vonng (@Vonng) | WeChat Original | Percona’s Blog

At Percona, we constantly monitor our users’ needs and strive to meet them. We particularly tracked MySQL version distribution and usage patterns, discovering a striking trend: the migration pace from version 5.7 to 8.x is noticeably slow. More precisely, many users still need to stick with version 5.7.

mysql_versions_adoption_trend.png

Based on this finding, we took several measures. First, we talked with some users still using MySQL 5.7 to explore why they don’t want to migrate to 8.x. To this end, we developed an EOL plan providing extended lifecycle support for version 5.7, ensuring users who need to rely on old versions, binaries, and code fixes can get professional support.

Meanwhile, we conducted extensive testing on different MySQL versions to assess any performance degradation. While testing isn’t complete, we’ve collected enough data to start drawing relevant charts. This article is a preliminary interpretation of our test results.

Spoiler alert: For those who love Sakila like me, these findings might not be pleasant.

Translator’s note: Sakila is MySQL’s dolphin mascot

Author: Percona Blog, Marco Tusa, important contributor to the MySQL ecosystem, developer of famous PT series tools, MySQL backup tools, monitoring tools, and distributions.

Translator: Vonng, author of Pigsty, PostgreSQL expert and evangelist. Cloud exit advocate and database cloud exit practitioner.


Testing
#

Assumptions
#

Testing methods vary widely, and we certainly understand that test results may vary due to various factors (e.g., runtime environment, MySQL server configuration). But if we compare multiple versions of the same product on the same platform, we can reasonably assume that without changing MySQL server configuration, variables affecting results can be controlled to the maximum extent.

Therefore, I first ran performance tests based on MySQL default configuration. The working assumption here is clear: the default values you use when releasing a product are usually the safest configuration and have been thoroughly tested.

Of course, I also made some configuration optimizations and evaluated how optimized parameter configurations would affect performance.

What Tests Did We Run?
#

We ran both sysbench and TPC-C Like benchmarks. Complete test methodology and details can be found here, with actual executed commands available here:


Results
#

We ran the complete test suite, and all results can be found here.

But to keep the article concise and high-quality, I’ll only analyze and present sysbench read-write tests and TPC-C results here. I chose these two tests because they directly and comprehensively reflect MySQL server performance, and they’re also the most common application scenarios. Other tests are more suitable for in-depth analysis of specific issues.

In this report’s sysbench read-write tests, write operations account for about 36%, read operations about 64%, with reads consisting of point queries and range queries. In TPC-C tests, read-write operation ratios are both 50/50%.

Sysbench Read-Write Tests
#

First, we tested different MySQL versions with default configuration.

Small dataset, default configuration:

mysql_default_small.png

Small dataset, optimized results:

mysql_optimized_small.png

Large dataset, default configuration:

mysql_default_large.png

Large dataset, optimized configuration:

mysql_optimized_large.png

The first two charts are interesting but clearly show one point: we can’t use default configuration for performance testing. We can use them as a baseline to find better default values.

Oracle’s recent decision to modify many parameter defaults in 8.4 confirms this point (see article).

Given this, I’ll focus on performance evaluation results after optimizing parameter configuration.

Looking at the charts above, we can see:

  1. MySQL 5.7 with default values performs better in both scenarios (small and large datasets).
  2. MySQL 8.0.36’s poor default configuration parameters make it perform poorly in the first scenario (small dataset). But with some optimization adjustments, its performance can surpass 8.4 and get closer to 5.7.

TPC-C Tests
#

As mentioned above, TPC-C tests should be write-intensive, using transactions and executing complex queries with JOINs, GROUPs, and sorting.

We ran TPC-C tests using the two most commonly used isolation levels: Repeatable Read and Read Committed.

Although we encountered some issues during repeated testing, they were all random problems caused by lock timeouts. Therefore, despite some gaps in the charts, these don’t affect the overall trend - just maxed-out stress performance.

TPC-C, optimized configuration, RR isolation level:

tpcc-rr-optimized.png

TPC-C, optimized configuration, RC isolation level:

tpcc-rc-optimized.png

In these tests, we can observe that MySQL 5.7 performs better than other MySQL versions.

How About Percona’s MySQL and MariaDB?
#

For brevity, I’ll only present optimized parameter configuration tests here, as mentioned above - default parameters are useless.

Sysbench read-write, small dataset test results:

vs-percona-small.png

Sysbench read-write, large dataset test results:

vs-percona-large.png

When comparing MySQL versions with Percona Server MySQL 8.0.36 and MariaDB 11.3, we can see MySQL 8.4 only performs better when compared with MariaDB, but still lags behind MySQL 8.0.36.

TPC-C
#

TPC-C, RR isolation level test results:

tpcc-rr-optimized-all.png

TPC-C, RC isolation level test results:

tpcc-rc-optimized-all.png

As expected, MySQL 8.4 doesn’t perform well here either, with only MariaDB performing worse as a bottom reference. Incidentally, Percona Server for MySQL 8.0.36 is the only MySQL that handles increased concurrent contention well.


What Do These Tests Tell Us?
#

Frankly, the results we’ve tested here are also what most of our users experience firsthand — MySQL performance degrades with version increases.

Of course, MySQL 8.x has some interesting new features, but if you consider performance as the primary and most important factor, MySQL 8.x isn’t better.

That said, we must admit — most people still using MySQL 5.7 might be right (there are thousands of them). Why risk a major migration only to discover you’ve lost a considerable portion of performance?

This can be illustrated with TPC-C test results. We can convert the data to transactions per second throughput and compare how much performance was lost:

TPC-C, RR isolation level, MySQL 8.4 performance loss:

tpcc_trx_lost_rr.jpg

TPC-C, RC isolation level, MySQL 8.4 performance loss:

tpcc_trx_lost_rc.jpg

We can see that in both tests, MySQL 8.x performance degradation is very obvious, while the benefits it brings (if any) are not significant.

Using absolute values:

TPC-C, RR isolation level, MySQL 8.4 performance loss:

tpcc_trx_lost_rr-1.jpg

TPC-C, RC isolation level, MySQL 8.4 performance loss:

tpcc_trx_lost_rc-1.jpg

In this situation, we need to ask ourselves: can my business handle such performance degradation?


Some Thoughts
#

When MySQL was sold to SUN Microsystems, I was working at MySQL AB, and I was very unhappy with this acquisition. When Oracle took over SUN, I was very worried that Oracle might decide to kill MySQL, so I decided to join another company to continue working on it.

In the following years, I changed my mind and began supporting and promoting Oracle’s work on MySQL. In many ways, I’m still supporting and promoting it.

They did well in standardizing development processes, and code cleanup work was also effective. However, there wasn’t much progress in other code areas, and the performance degradation we see is the price of this lack of progress; see Peter’s article “Will Oracle Finally Kill MySQL?”.

On the other hand, we must acknowledge that Oracle has indeed invested heavily in performance and features for OCI/MySQL/Heatwave products — it’s just that these improvements aren’t reflected in MySQL code, whether community or enterprise edition.

Again, I think this is very sad, but I can understand why.

When cloud vendors like AWS and Google use MySQL code, optimize it for their own use, make billions of dollars, and aren’t even willing to contribute code back, why should Oracle continue to optimize MySQL code for free?

We know this situation has been going on for many years, and we know it has caused extremely negative impacts on the open source ecosystem.

MySQL is just one Lego brick in a larger scenario where cloud computing companies are devouring other companies’ work and getting rich from it.

What can we do? I can only hope we’ll soon see something different: open code, invest in projects, help communities like MySQL regain lost ground.

Meanwhile, we must acknowledge that many customers and users have very good reasons to use MySQL 5.7. Until we can solve this problem, they might never decide to migrate, or if they must migrate, migrate to other alternatives like PostgreSQL.

Then, Sakila will slowly and painfully die from human greed as usual, which isn’t new in some sense, but it’s bad.

fin.jpg

Wish everyone happy MySQL usage.


References
#

Sakila, Where Are You Going?

Perf regressions in MySQL from 5.6.21 to 8.0.36 using sysbench and a small server

MySQL走好 - This article is part of a series.
Part 2: This Article

Related

Can Chinese Domestic Databases Really Compete?
·1963 words·10 mins
Friends often ask me, can Chinese domestic databases really compete? To be honest, it’s a question that offends people. So let’s try speaking with data - I hope the charts provided in this article can help readers understand the database ecosystem landscape and establish more accurate proportional awareness.
The $20 Brother PolarDB: What Should Databases Actually Cost?
·2597 words·13 mins
Today we discuss the fair pricing of commercial databases, open-source databases, cloud databases, and domestic Chinese databases.
Redis Going Non-Open Source is a Disgrace to "Open Source" and Public Cloud
·2091 words·5 mins
Redis “going non-open source” is not a disgrace to Redis, but a disgrace to “open source/OSI” and even more so to public cloud. What truly matters has always been software freedom, while open source is just one means to achieve software freedom.
How Can MySQL's Correctness Be This Garbage?
·3110 words·15 mins
MySQL’s transaction ACID has flaws and doesn’t match documentation promises. This may lead to serious correctness issues - use with caution.
Which EL-Series OS Distribution Is Best?
·1594 words·4 mins
RHEL-series OS distribution compatibility level: RHEL = Rocky ≈ Anolis > Alma > Oracle » Euler. Recommend using RockyLinux 8.8, or Anolis 8.8 for domestic requirements.
Are Microservices a Stupid Idea?
·624 words·3 mins
Even Amazon, the SOA exemplar, thinks microservices and serverless suck. Are microservice architectures still a good idea? Amazon’s Prime Video team published a very compelling case study explaining why they abandoned microservices and serverless architecture.