|
Planet MySQL
|
Planet MySQL - https://planet.mysql.com
|
-
Performance improvements in Percona Server 8.4.11-11
Focusing on Percona Server 8.4.11-11
My previous post (Performance Progression of Percona Server for MySQL 8.4) did a brief review of the performance changes in Percona Server for MySQL 8.4 released in 2026. I recommend reading it first to better understand the material in this post.
Version 8.4.11-11 includes patches that deliver significant improvements in performance and scalability.
One of the most impressive illustrations of the performance bump is reflected in the following graph:
Graph 1 – Percona-Server 8.4.X with 8G buffer [ INTERACTIVE GRAPH ][ TABLE ]
There are two separate improvements in the version 8.4.11-11:
Faster speed for the number of threads smaller than the number of CPU cores. On a host with 40 CPU cores + hyperthreading, which enables simultaneous execution of 80 threads, all versions of the server keep increasing TPS up to 64 threads. However, the version 8.4.11-11 does a noticeably better job in this section.
Prevent abrupt performance degradation for high thread numbers, which is an issue in the versions 8.4.8-8 and 8.4.10-10. Also, a similar degradation is observed in Upstream MySQL 8.4.11.
All code changes improved the performance of the InnoDB buffer pool and the way it handled pages flushing.
Why is this important?
InnoDB is the default storage engine in Percona Server for MySQL. Therefore, InnoDB performance influences the overall performance of the database server.
The InnoDB engine has a buffer pool, which is basically a data cache in RAM. Instead of reading from disk for every query, the server keeps the most-used data pages in memory.
Its performance matters because:
RAM is many times faster than disk – the more queries get results from the buffer pool, the faster the database works.
Every query uses the buffer pool. If its internal locks are slow, all queries queue up behind each other.
The buffer pool must constantly free space for new data. So, the queries do not stall because they need to do extra cleanup work themselves.
InnoDB stores data in fixed-size pages (16 KB by default). Tables and indexes are split into pages, and a page is the unit that moves between disk and the buffer pool – it never reads a single row from disk, always the whole page containing it.
To better understand the mechanism responsible for the InnoDB buffer pool handling of pages we need to explain the Least Recently Used (LRU) concept.
LRU is the strategy used to decide which pages should be removed from the buffer pool when space runs low. All pages in memory reside in a list ordered by usage: recently accessed pages stay near the “hot” end, untouched ones drift to the “cold” end, and get evicted first. This keeps frequently used data in fast memory, while rarely used data is written back to disk.
The implementation.
The code is available in the GitHub repository for anyone to see:
https://github.com/percona/percona-server/
PS-11444: Narrow the LRU mutex scope in buf_page_init_for_read()
(GitHub: 52d87557)
Significant improvement for read-heavy (IO-bound) workloads. Previously, every physical page read held the pool-wide LRU list mutex while doing extra work, including zeroing a 16 KB page frame – so all reads on a buffer pool instance were serialized on one lock. Now the mutex only covers the actual LRU list insertion, and the rest runs under a much finer-grained page-hash latch. As a result, pages could temporarily become visible in the page hash table prior to their insertion into the LRU list, which requires careful handling in the codebase. Physical reads can now proceed in parallel rather than queueing behind one another. It also fixes a related race (PS-9837) and removes LRU-mutex contention from the purge thread.
NOTE: This improvement was possible by analyzing the lock waits in the profiling data, which pointed to the most contended mutex, which was in LRU list.
PS-11445: LRU optimization: LRU manager threads are restored
In this multi-patch ticket the LRU threads were restored to their original state and a few other performance improvements were done.
Restore per-buffer-pool LRU manager thread
(GitHub: 53bb2f0f)
With this patch each buffer pool instance gets its own background thread that evicts and flushes cold pages to keep free pages available. When this is not done, user queries needing a free page might start doing cleanup work inline during the query execution, which increases the execution time.
Close a shutdown/invalidation race
(GitHub: 37cdd537)
This commit has no direct performance impact.
Add innodb_lru_threads sysvar
(GitHub: 1a1185b4)
Adds the ON/OFF switching for the LRU manager through innodb_lru_threads system variable. Also, the patch restructures flush statistics, so LRU threads don’t need to synchronize with each other when updating counters – stats are aggregated by a single coordinator thread.
Count evictions as adaptive-sleep progress
(GitHub: c80f16557)
The LRU thread adapts how long it sleeps based on whether it is “making progress”. In the previous implementation only flushing pages counted as progress, so freeing memory by evicting clean pages looked like failure and made the thread back off wrongly. Now evictions count as progress too, so the LRU thread avoids backing off under clean-page workloads.
Single-page-flush concurrency cap
(GitHub: 2624d116)
Fixes a low-concurrency throughput drop: when a background LRU batch was already running, a user thread needing a free page always sat waiting for the whole batch to finish. Now the thread first scans for a page it can free immediately, only waits when it truly has to, and never waits twice in one call (avoiding starvation). Adds two monitoring counters for observability.
PS-11446: Fix inverted old/young check in LRU scan iterator
(GitHub: dc344fba)
This improvement fixes a bug when the background flusher wasted CPU time re-scanning the same pages over and over because it started from LRU tail each time. After the fix, the LRU scan iterator remembers where it left off and continues from there.
NOTE: This bug existed in MySQL and Percona Server for MySQL for a long time unnoticed. It was discovered only because the LRU code was re-assessed for possible performance improvements.
Summary
The overall performance improvements were delivered by: faster physical reads through much less lock contention (PS-11444), background threads keeping free pages ready so queries don’t stall on evictions (PS-11445) and no wasted rescanning in the flusher (PS-11446).
The post Performance improvements in Percona Server 8.4.11-11 appeared first on Percona.
-
Node.js MySQL Insert Record
I pointed a Node script at a MySQL table and the connection succeeded. The INSERT failed on a name containing an apostrophe. Pasting values into the SQL string holds until actual data arrives, then a name like O’Brien ends the run with a syntax error. You will insert one row and many rows through placeholders, […]
-
MySQL INSTANT DDL breaks EXCHANGE PARTITION
MySQL ERROR 1731 'Non matching attribute INSTANT COLUMN(s)' breaks EXCHANGE PARTITION after ALGORITHM=INSTANT. This blog offers reasoning and fixtures.
The post MySQL INSTANT DDL breaks EXCHANGE PARTITION first appeared on Change Is Inevitable.
-
NodeJS MySQL Drop Table
DROP TABLE crashed my Node process when I re-ran it without a guard, and the fix turns that crash into a warning. I built every code path below on Node 26.7.0 with mysql 2.18.1 and mysql2 3.24.4 against MariaDB 10.11.14. I captured the terminal output as images so what you run matches what I saw. […]
-
NodeJS MySQL Create Table
I ran node app.js once and the users table appeared, then I ran it again and MySQL returned Table users already exists. If you copy the Node.js MySQL create table snippet from an older tutorial you expect it to be safe to rerun because the code looks like any other setup script, so the duplicate […]
|
