MariaDB 11.8 Fix Makes In-Memory Workloads Up to 3x Faster

If your MariaDB data fits in RAM and the server became slower after an upgrade from 10.4, you were probably not imagining it. MariaDB developers have found the cause: a performance regression in the InnoDB buffer pool that dates back to 10.5. The fix has been merged into the 11.8 and 12.3 branches. In one benchmark, throughput rose from 20,326 to 57,052 queries per second, which is close to three times faster. This post explains what went wrong, who benefits, and how to check and prepare your own servers.

What went wrong in 10.5

InnoDB keeps the pages it has cached in memory on an LRU (least recently used) list. When a page is read often, InnoDB moves it, or promotes it, to the young end of the list so it is not evicted. In 10.5 the buffer pool went from several instances to one, with a single LRU list protected by one mutex. Each promotion has to take that mutex.

Older versions avoided most of that work. While an internal counter called freed_page_clock stayed at zero, meaning nothing had been evicted yet (the normal state when all data fits in memory), InnoDB skipped promotion entirely. Other changes in 10.5 made purged undo pages and freed B-tree pages advance that counter. Once it moved away from zero it never returned, so from then on almost every page access could try to take the buffer pool mutex. A later change turned that mutex into a blocking one, and on one test the number of context switches per query rose from 2.8 to 29.6.

The regression shows up clearly in the test results from the MariaDB issue tracker (MDEV-37992):

What the fix changes

The fix removes the mutex from the page-access path. When a page is read, InnoDB now just sets a promote flag on it without taking any lock. Flagged pages are moved to the young end later, by the page cleaner and eviction routines, which already hold the mutex. The old clock is gone. A simple counter of seconds, updated by a background timer, replaces it.

The results are strong. On Mark Callaghan’s benchmark, 11.8 went from 20,326 to 57,052 QPS, about 9 percent faster than 10.4.34. In an independent run on a 48-core AMD EPYC host, 11.8 went from 17,925 to 66,653 transactions per second. Cache hit ratios stayed the same or improved slightly in eviction-heavy tests, and an update-heavy test showed no regression.

A few details change for existing setups:

• innodb_old_blocks_time now has a resolution of one second. Values are rounded up, and the internal cap is about 18.2 hours. Before the fix, the value was in milliseconds.
• FREE_PAGE_CLOCK in INFORMATION_SCHEMA now always shows 0.
• Innodb_buffer_pool_pages_made_young is now counted at sweep time, so you cannot compare it directly with older versions in your dashboards.
• No new configuration options were added.

Are you affected?

The workloads that gain the most run on 10.5 or later, have a buffer pool large enough to hold the working set, and handle many concurrent reads. Typical examples are busy WordPress, WooCommerce and Laravel sites on a well-sized database server. Start by comparing logical reads with reads that had to go to disk:

SELECT VERSION();
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

If Innodb_buffer_pool_reads is tiny compared with Innodb_buffer_pool_read_requests, your data is effectively in memory. If the server also shows high context-switch rates under load (the cs column in vmstat 1), this regression is a likely cause.

Upgrading, and a workaround until then

The MariaDB post suggests testing the upcoming 11.8.10, 12.3.4 and 13.1.2 releases. It does not mention backports to 10.6, 10.11 or 11.4. If you run one of those series, moving to 11.8 LTS is a major upgrade and needs testing first.

Always take a backup before upgrading:

sudo mariadb-dump --all-databases --single-transaction --routines --events > all-$(date +%F).sql

If you use the MariaDB repository on Debian or Ubuntu:

sudo apt update && sudo apt install --only-upgrade mariadb-server

On RHEL, AlmaLinux or Rocky Linux with the MariaDB repository:

sudo dnf upgrade MariaDB-server

Afterwards, run sudo mariadb-upgrade if your packages do not do it automatically. On cPanel servers, use the MySQL/MariaDB Upgrade tool in WHM instead of the package manager.

On releases without the fix, the article describes a workaround. Setting innodb_old_blocks_time to a very large value almost stops promotion and restored most of the lost throughput in testing:

SET GLOBAL innodb_old_blocks_time = 2147483647;

Use this only when your data truly fits in the buffer pool. Once old pages are almost never promoted, hot pages can be evicted as easily as pages from a one-off table scan. Remove the setting when you upgrade, because the variable’s units and limits change with the fix.

How TechProvidence can help

TechProvidence handles MariaDB and MySQL tuning, version upgrades and migrations for hosting and application servers, including cPanel environments. We can benchmark your workload, plan a tested upgrade path and roll it out with minimal downtime. To get started, contact us.

Source: MariaDB.org