At Percona, we have always prioritized database performance as a critical factor in selecting database technologies. Recently, we have observed a concerning trend in the community edition of MySQL, where performance appears to be declining across major releases, specifically MySQL versions 5.7, 8.0, and 8.4. If you’re interested in learning more about these observations, we recommend checking out Marco Tusa’s recent blog post, “Sakila, Where Are You Going?”.

In light of these concerns, our team decided to delve deeper into the matter. I am pleased to announce that we have promising updates to share. The upcoming release of Percona Server for MySQL 8.4.0 will include enhancements that will improve performance in all write operations.

Write performance is up to 17% better in Percona Server 8.4 compared to MySQL 8.4. We have observed an average performance increase of 10.89% for oltp_inlist_update workload in Percona Server for MySQL compared to the MySQL Community Edition 8.4.0. 

A little bit of theory first.

In the replication chain, the source server produces the stream of transaction events to be applied by the replica. The stream is serialized, so the replica should apply events one by one in the simplest scenario. Of course, that would not be efficient. That’s why we use multithreaded replicas and take advantage of the fact that transactions in the stream do not necessarily depend on each other. If the replica had the chance to detect non-dependent events, it could apply them in parallel. Providing such dependency information is the job of the source server.

In 8.0.x the way the source traces transaction dependencies is controlled by ‘binlog_transaction_dependency_tracking’ parameter, with COMMIT_ORDER being the default value. What the source does in this mode is concluding that transactions were not dependent if they were committed at the same time.

Another possible value of ‘binlog_transaction_dependency_tracking’ is WRITESET. Official documentation clearly describes what happens in this mode:

“Each row in the transaction adds a set of one or more hashes to the transaction’s write set, one of each unique key in the row. (If there are no unique, non nullable keys, a hash of the row is used.) This includes both deleted and inserted rows; for updated rows, both the old and the new row are also included.

Two transactions are considered conflicting if their write sets overlap—that is, if there is some number (hash) that occurs in the write sets of both transactions.”

‘binlog_transaction_dependency_tracking’ was deprecated in 8.0.35 and removed in 8.4. In 8.4 the server defaults to WRITESET mode. Meaning the server is now always in WRITESET mode with no option to change.

Here, we get to the root cause of the detected performance problem. The server keeps track of previous writesets. History is a map of pairs: row hash and the last transaction sequence number that changed this row. Every time a new transaction is committed, we check if any row modified by it has a dependency in the history map. If we find the transaction that modified this row, we know its sequence number and that we depend on it.

The history map is std::map, which means that random item access (find) has logarithmic complexity. With the default size of the history map being 25,000 and frequent ‘finds,’ it starts to be meaningful.

Our team investigated the possibility of using std::unordered_map instead of std::map. It has constant on average (worst case linear) random item access complexity. Benchmark results confirmed this improvement, leading to a performance boost.

Now let’s look at some of the technical details. A short description of the setup:

Benchmark procedure:

  • We created a template database with 16 tables x 10 millions rows each (53 GB space)
  • We used 6 write-only workloads tested with 8, 16, 32, 64 clients; spent 300 seconds for each test
  • After each workload database was restored from a template to make results more stable
  • Full tests with 6 write-only workloads were repeated 3 times for both MySQL 8.4.0 and Percona Server for MySQL 8.4.0 with interleaved runs. 

Please see Figure 1 for the summary of the results.

Figure 1: The results for Percona Server (PS) 8.4.0 vs. MySQL 8.4.0 in queries per second (QPS) for different workloads.

Here are the averaged results from the tests:

Table 3 presents a comparison of the performance results between Percona Server 8.4.0 and MySQL Community Edition 8.4.0. The data indicates an average improvement of 10.89% for the oltp_inlist_update workload. More notably, there is an approximate 2% average improvement across all write workloads. It is important to note that these gains are most noticeable under conditions involving heavy write workloads and require fast storage solutions. Feel free to use PMM to get information about your databases’ performance https://www.percona.com/software/pmm/quickstart.

During our research, we identified additional areas where performance improvements can be made. These enhancements will be addressed during the implementation phase of future MySQL releases, both in the 8.4.x and 8.0.x series.  We thought this improvement was substantial enough to get it incorporated immediately and write about it.  If you’re seeing performance degradation on high write workloads (or concerned you could soon) we encourage you to upgrade or install Percona Server to take advantage of the speed boost because after all, time is money so don’t leave any time on the table! 

Learn more about Percona Server for MySQL

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Michal

Good job. I think there were no performance improvements for a long time. It’s nice to finally get something done.

2
Reply