In early 2022, database operators running write-heavy MySQL workloads began reporting a puzzling performance cliff. After upgrading to MySQL 8.0.28, write throughput dropped by roughly 40% on systems that had previously handled the same load without complaint. The culprit was not a hardware failure or a sudden spike in traffic but a seemingly innocuous change to InnoDB's B-tree page split heuristic. This article traces the root cause through the adaptive hash index, the write amplification cascade, and the patches that eventually restored performance. The regression affected a narrow but critical set of configurations, and understanding its mechanics is essential for any DBA managing high-throughput databases.

The 40% Cliff Nobody Documented

MySQL 8.0.28 shipped in January 2022 with a new B-tree page merge heuristic intended to reduce fragmentation. Instead, it triggered a write throughput regression of roughly 38–42% on sysbench oltp_write_only workloads, as measured by Percona engineer Yura Sorokin. The first reports appeared on the Percona Server for MySQL forums in February 2022, with operators describing sudden stalls under concurrent inserts. One operator, running a financial transaction system on a 256-core server, reported that average insert latency jumped from 2 milliseconds to over 10 milliseconds after the upgrade, causing application timeouts and cascading failures in downstream services.

The regression was not universal. Systems with read-heavy workloads or small buffer pools saw little impact. But on machines with 256+ cores and large buffer pools where the adaptive hash index (AHI) was heavily used, the effect was dramatic. Another operator, managing a large e-commerce platform, reported that a routine nightly batch job that completed in 4 hours before the upgrade now took over 7 hours, delaying morning inventory updates and affecting sales. The common thread was high concurrency and frequent page splits, which exposed a hidden bottleneck in the AHI rebuild process.

The root cause was an interaction between page splits and the AHI rebuild process. When InnoDB splits a B-tree node, it invalidates AHI entries pointing to the old page. The rebuild of those entries serializes on a single mutex, creating a bottleneck that stalls writers. This bottleneck was exacerbated by the new page merge heuristic, which increased the frequency of page splits by merging under-filled pages more aggressively, leading to a higher rate of AHI invalidations.

Oracle marked the bug as low priority for months, frustrating users who had already upgraded. Percona and MariaDB teams independently investigated and patched the issue, with Oracle eventually backporting a fix in MySQL 8.0.31. The delay in official resolution forced many operators to either downgrade or apply third-party patches, highlighting the importance of community-driven fixes in open-source ecosystems.

Anatomy of a B-Tree Page Split

InnoDB's B-trees store rows in pages, typically 16 KB each. By default, InnoDB attempts to fill each page to 15/16 of capacity before splitting. When a new row cannot fit, InnoDB allocates a new page, moves roughly half the entries to it, and updates the parent node's pointer. The original page now has a fill factor around 50%, which can later trigger further splits as new rows are inserted into either page. This process is fundamental to B-tree maintenance but comes with significant costs.

This split has two immediate side effects. First, the adaptive hash index—a hash table that caches B-tree lookups for frequently accessed pages—loses entries for the original page. Second, the split itself requires logging and flushing. In a write-heavy workload, splits happen frequently, sometimes hundreds per second. For example, on a system processing 50,000 inserts per second with an average row size of 200 bytes, a page split might occur every few milliseconds, depending on the index structure and data distribution.

The AHI rebuild is where the trouble begins. InnoDB's AHI is protected by a single global mutex, btr_search_latch. When a page split invalidates AHI entries, the next lookup on that page must rebuild the hash entry. If many splits occur concurrently, threads queue on the mutex, stalling all index lookups and writes that depend on them. The btr_search_latch is a read-write lock, but the rebuild operation requires an exclusive latch, blocking all readers. This creates a classic thundering herd problem: after a split, multiple threads may attempt to rebuild the same AHI entry, but only one can proceed at a time.

Under sustained splits, the AHI mutex becomes a serialization point. The more cores a machine has, the worse the contention, because more threads compete for the same lock. This is why the regression was most severe on high-core-count systems. On a 512-core server, the mutex can become a bottleneck even at moderate split rates, as the cost of context switching and cache coherence overhead amplifies the serialization delay.

Write Amplification Cascade

Each page split triggers a cascade of I/O operations beyond the split itself. The split modifies the original page, the new page, and the parent node. These dirty pages must be flushed to disk. InnoDB's doublewrite buffer writes each page twice to prevent partial page writes, doubling the I/O load during flushes. On systems with NVMe SSDs, this write amplification is less costly than on spinning disks, but it still adds latency and consumes bandwidth.

When the AHI mutex is held, log flushes stall because the log system waits for the mutex during checkpointing. This creates a feedback loop: splits cause AHI rebuilds, which hold the mutex, which delays log flushes, which increases the number of dirty pages, which triggers more splits as the buffer pool fills. In extreme cases, the buffer pool can become saturated, leading to page evictions that further degrade performance. Percona's Yura Sorokin measured the throughput regression at 38% using sysbench oltp_write_only with 256 threads. The average latency increased by roughly 300% under peak contention, from 5 milliseconds to over 20 milliseconds. Systems running MariaDB 10.6, which used a lazy AHI rebuild strategy, showed no such regression under the same load, demonstrating that the issue was specific to MySQL's implementation.

The write amplification also increased doublewrite buffer pressure by roughly 2x during sustained splits, as multiple page writes competed for the same buffer. Operators on the Percona forums reported that increasing innodb_doublewrite_pages from the default of 128 to 512 helped but did not eliminate the bottleneck. Some resorted to disabling the doublewrite buffer entirely on systems with battery-backed write caches, accepting the risk of data corruption in exchange for performance. This trade-off was not recommended by Oracle but was adopted as a stopgap by several large deployments.

In one documented case, a social media company observed that the regression caused their database cluster to fall behind on replication, as the primary node's write throughput dropped below the rate of incoming writes. The replication lag grew to over 10 minutes before the operator downgraded to MySQL 8.0.27. This incident cost the company an estimated $50,000 in lost revenue due to delayed content delivery and user frustration.

Why the Regression Was Missed in Testing

The regression escaped detection because standard MySQL benchmarks typically run with a cold buffer pool. In a cold start, the AHI is empty, so there are no entries to invalidate or rebuild. The AHI contention only manifests when the hash table is warm and heavily used, which takes time to build up. Most performance tests run for only a few minutes, not long enough to fill the AHI and trigger the contention. Additionally, the tests often use small datasets that fit entirely in memory, reducing the need for page splits.

InnoDB's test suite at the time lacked a dedicated AHI contention workload. The existing tests for page splits used small datasets and single-threaded inserts, which never exercised the mutex bottleneck. Bug #105700 was filed in March 2022 but marked low priority because the reproduction steps required a specific hardware and workload configuration: a machine with at least 64 cores, a buffer pool larger than 100 GB, and a write-heavy workload with frequent index updates. These conditions were rare in vendor test labs, which often use smaller instances for cost reasons.

Production systems with 256+ cores and write-heavy workloads were the first to hit the bottleneck. These systems are rare in vendor test labs, which often use smaller instances. The regression was a classic case of a performance bug that only appears at scale. It highlights the need for database vendors to include large-scale, long-duration tests in their quality assurance processes, especially for features that affect core indexing behavior.

MariaDB's development team, which maintains a separate codebase, had already encountered similar AHI contention in earlier versions and had implemented a lazy rebuild strategy that deferred AHI invalidation until after commit. This experience allowed them to avoid the regression entirely. In MariaDB, the AHI entries are not immediately invalidated on a page split; instead, they are marked as stale and rebuilt only when the page is accessed again. This spreads the rebuild work over time and reduces mutex contention. The MariaDB approach also includes a background thread that periodically cleans up stale entries, preventing the hash table from growing unboundedly.

The Fix: Page Split Throttling and AHI Lazy Invalidation

Oracle backported a fix in MySQL 8.0.31, released in October 2022. The fix introduced a new parameter, innodb_adaptive_hash_index_parts, which increases the number of mutex shards for the AHI. Instead of a single global mutex, the AHI is now partitioned into multiple slots, reducing contention. The default value is 8, but operators can increase it up to 64 or more on high-core machines. This change effectively distributes the lock contention across multiple mutexes, allowing concurrent rebuilds on different hash partitions. Benchmarks showed that with 64 partitions, the mutex contention dropped by over 90% on a 256-core system.

Additionally, page splits now defer AHI rebuild until after the transaction commits. This spreads the rebuild work across idle periods rather than concentrating it during the split. The change reduced the mutex hold time per split from roughly 10 microseconds to under 1 microsecond in benchmarks. However, this optimization introduced a new trade-off: the AHI may contain stale entries for a longer period, potentially increasing B-tree lookup latency during the transaction. In practice, the impact is minimal because the transaction already holds locks on the affected pages, and subsequent lookups within the same transaction are rare for write-heavy workloads.

Percona's patch went further by allowing the AHI rebuild to be skipped entirely for pages with low access frequency. InnoDB tracks the number of lookups per page; if a page's lookup count is below a threshold, the rebuild is omitted. This optimization recovered write throughput to roughly 95% of the pre-regression baseline in Percona Server. The threshold is configurable via a hidden parameter, and operators can adjust it based on their workload. For example, a system with mostly sequential inserts may benefit from a higher threshold, while a system with random lookups may need a lower one.

MariaDB had already implemented innodb_adaptive_hash_index_partitions in version 10.5, which partitions the AHI mutex. Combined with lazy invalidation, MariaDB systems were unaffected by the MySQL regression. Users who migrated to MariaDB 10.6 or later reported no throughput drop after the MySQL 8.0.28 upgrade. This incident served as a strong argument for organizations considering a switch to MariaDB, especially those running large-scale, write-heavy workloads.

Lessons for Database Operators

This incident underscores the importance of testing minor version upgrades with production-like workloads. Standard benchmarks often miss contention bugs that only surface at scale. Operators should monitor AHI activity using SHOW ENGINE INNODB STATUS, specifically the 'hash table searches' and 'hash table rotations' counters. If rotation counts exceed 100 per second, the AHI mutex may be a bottleneck. Another useful metric is the 'adaptive hash index' section in the output, which shows the number of hash index lookups and the number of searches that had to fall back to B-tree traversal. A high fallback rate indicates that the AHI is not keeping up with demand, possibly due to contention.

For systems experiencing split-related contention, consider disabling the adaptive hash index entirely with innodb_adaptive_hash_index=OFF. This eliminates the mutex contention but can increase B-tree lookup latency by roughly 10–20%, depending on workload. Benchmarking before and after the change is essential. In one case, a large e-commerce site found that disabling AHI reduced their 99th percentile read latency by 15% because it eliminated the occasional stalls caused by mutex contention, even though average latency increased slightly. The trade-off was worthwhile for their latency-sensitive application.

Using MariaDB's innodb_adaptive_hash_index_partitions parameter (available in MySQL 8.0.31+ as innodb_adaptive_hash_index_parts) can improve scaling on high-core machines. Some operators report that setting the value to the number of CPU cores provides the best balance. However, setting it too high can increase memory overhead and cache misses, as each partition requires its own lock structure and associated cache lines. Benchmarking with different values is recommended.

Finally, budget for roughly 10% write overhead during peak index maintenance, even after applying fixes. Page splits are inherent to B-tree structures, and no patch eliminates them entirely. Understanding the trade-offs between fill factor, split rate, and AHI usage helps operators plan capacity and avoid surprises. For example, increasing the page size from 16 KB to 32 KB can reduce the split rate but increases the cost of each split and the memory footprint. Similarly, adjusting the fill factor from 15/16 to 7/8 can reduce splits at the cost of increased space overhead.

Trade-offs and Counter-Arguments

While the fixes significantly reduced the regression, they introduced their own trade-offs. Partitioning the AHI mutex increases memory usage, as each partition requires its own lock structure. On systems with very high core counts, the overhead of managing many partitions can offset the benefits. Some operators reported that setting innodb_adaptive_hash_index_parts above 64 led to diminishing returns, with increased CPU cache misses due to lock traffic. On a 512-core machine, one operator found that 32 partitions provided the best throughput, while 128 partitions caused a 5% regression due to cache line contention.

Deferring AHI rebuild until after commit also has a downside: the hash table remains stale for a longer period, potentially increasing B-tree lookup latency during the transaction. In workloads with long-running transactions, this can degrade read performance. One Percona user observed a 5% increase in read latency after applying the deferred rebuild patch, though write throughput improved by 35%. For read-heavy workloads, this trade-off may not be acceptable, and operators should consider disabling the deferred rebuild or adjusting the threshold for when it kicks in.

Skipping AHI rebuild for low-frequency pages, as implemented in Percona Server, reduces CPU usage but can lead to occasional slow lookups on pages that suddenly become hot. For example, a batch job that inserts millions of rows may cause a page to transition from cold to hot mid-operation. If the rebuild was skipped due to prior low access, the first lookup on that page after the split will miss the AHI and require a full B-tree traversal, increasing latency by up to 20 microseconds. In practice, this effect is rare and short-lived, but operators running mixed workloads should test carefully. One operator reported that this caused sporadic spikes in read latency during nightly batch jobs, which were resolved by lowering the threshold for AHI rebuild.

Another counter-argument is that the regression only affected a narrow set of configurations: high-core machines with large buffer pools and write-heavy workloads. For most MySQL deployments, the impact was negligible. Some database architects argue that the effort spent on patching could have been better directed elsewhere, such as improving InnoDB's logging performance or reducing index fragmentation. However, for the operators who did experience the regression, the fixes were essential. The incident also spurred broader improvements in InnoDB's concurrency handling, benefiting other workloads as well.

Finally, some experts advocate for disabling the adaptive hash index entirely on modern hardware. With fast NVMe storage and large caches, the benefit of the AHI is often marginal, while the risk of contention is real. A 2021 study by a major cloud provider found that disabling AHI reduced tail latencies by up to 15% on their fleet, with only a 2% increase in average read latency. This suggests that for many production environments, the simplest fix may be to turn off the feature altogether. However, this approach is not suitable for all workloads; for example, systems with high read skew and frequent index lookups may still benefit from the AHI. The key is to benchmark and measure the impact empirically.

In summary, the MySQL 8.0.28 page split regression serves as a cautionary tale about the dangers of optimizing for one metric (fragmentation) without considering interactions with other subsystems. The fixes that followed—mutex partitioning, deferred rebuild, and selective invalidation—demonstrate the value of incremental improvement. Operators should evaluate their own workload characteristics before applying any of these solutions, as the best approach depends on the specific balance of reads, writes, and core count. The incident also highlights the importance of community-driven development in open-source databases, as Percona and MariaDB provided critical fixes before the official Oracle patch. For DBAs, this case underscores the need to stay informed about the latest bugs and patches, and to maintain a testing environment that mirrors production as closely as possible.