At Percona, we’ve always prioritized performance, and recent trends in MySQL’s development have been a point of concern for us. In particular, the performance deterioration in the MySQL 8.4.x and 9.y versions caught our attention, as highlighted in Marco Tusa’s insightful blog post, Sakila, Where Are You Going?
We’re pleased to report that the latest releases 8.4.3 and 9.1.0 (released in October 2024) demonstrate noteworthy performance improvements, as shown in table one below.
Table 1: Improvements of queries per second (QPS) for MySQL 8.4.3 vs. MySQL 8.4.2 for different workloads.
For detailed information about our testing methodology, the sysbench workloads used, and the hardware configuration, please see our previous blog post: Percona Server for MySQL Performance Improvements – August 2024.
The same results, as shown in Table one, are presented below in a stacked graph to demonstrate the improvement visually.
Intrigued, we delved deeper into the source of these optimizations and identified three key changes responsible for these enhancements.
1. Switching to ankerl::unordered_dense::map for Binlog Transaction Dependencies
Bug Reference: BUG#37008442
The change from a std::map to an ankerl::unordered_dense::map in the binlog_transaction_dependency data structure has yielded substantial performance gains. This improvement boosts the efficiency of workloads like INLIST_UPDATE and UPDATE_INDEX_LIMIT, delivering an impressive 19.4% average performance improvement.
This change builds upon work Percona initially introduced in Percona Server 8.4.0 (PS-9302), as detailed in our August 2024 blog post.
2. Optimized query execution with JOINS
Bug Reference: BUG#35531293
A recurring issue in previous MySQL versions was the performance degradation observed in MySQL 8.0.33 compared to 8.0.28 for queries using JOINS. This optimization addresses that inefficiency, delivering a 2.17% average performance improvement for workloads like POINTS_COVERED_SI and POINTS_NOTCOVERED_SI.
3. Improved index range scans
Bug Reference: BUG#36775910
In prior versions, a missing record buffer in index range scans led to unnecessary slowdowns. The latest update resolves this issue, leading to a 2.12% average performance improvement for a range of workloads, including ORDER_RANGES, RANGE_NOTCOVERED, RANGE_COVERED, and SUM_RANGES.
Overall performance gains
Our testing highlights the cumulative impact of these changes:
- Write workloads: Approximately 7.25% average performance improvement for 9 tested workloads.
- Read workloads: Approximately 1.39% average performance improvement for 16 tested workloads.
When comparing newer versions:
- MySQL 8.4.3 is now only 1.47% slower than MySQL 8.0.40 for reads and writes.
- MySQL 9.1.0 is only 0.68% slower than MySQL 8.4.3 for reads and writes.
Final thoughts
The performance improvements in MySQL 8.4.3 and 9.1.0 represent a significant step forward. These releases demonstrate the active engagement of the MySQL development team in addressing community concerns, including valuable bug reports and fixes originally contributed by Percona. While there’s still room for further optimization to reach the peak performance of earlier versions, we are optimistic about future releases and look forward to continued advancements driven by the collaborative efforts of the MySQL community.
For more details on these changes and their impact, explore the corresponding commit logs linked above. As always, Percona remains committed to helping the MySQL community achieve optimal performance and reliability.
