MySQL / MariaDB Post-Upgrade Performance Anomalies

/by

Upgrading your MySQL or MariaDB database to a new major version (e.g. from MySQL 5.7 to 8.0 or from MariaDB 10.4 to 10.5) brings new features, but it is not uncommon to encounter performance anomalies after such upgrades. These anomalies typically manifest as abrupt changes in query latencies at the point of upgrade, often leading to performance degradation on highly optimized databases. In some cases, however, latencies of some queries can improve.

Understanding Performance Anomalies

Performance anomalies usually occur due to changes in the query optimizer and its interaction with index statistics. Both MySQL and MariaDB usually contain changes to the query optimizer in major version upgrades, but because the entire field of query optimisation is based on heuristic guesses based on index statistics, the results vary between different data sets, indexes and queries.

Root Causes

Heuristic-based Index Statistics: Index statistics are gathered heuristically based on data sampling. That means that even with extensive testing in pre-production environments that might have the same data as production could end up with subtly different index statistics, and the heuristic for gathering index statistics can change during database upgrades. That means that even with rigorous testing, there can be discrepancies between expected and actual performance post-upgrade.

Query Optimizer Evolution: Query optimization is based on heuristics. Those heuristics can change between versions and changes that lead to improvements in some queries can lead to a deterioration in other queries.

What to Look For

Here is what post-upgrade query discrepancies look like in SSM – you can find this in the Query Analytics section:

Post-Upgrade Query Latency Anomalies

As you can see, some queries got worse (latency steps up at the point of upgrade), some queries got better (latency steps down at the point of upgrade), and most stayed the same with no obvious discrepancy between pre and post upgrade periods.

Common Scenarios Post-Upgrade

On highly optimized systems, most performance anomalies tend to skew towards worse performance. However, it is not unusual to observe some improvements as well. This unpredictability means that some amount of overwatch is usually required after upgrades to identify any significant anomalies and address them, typically by modifying queries or index or join order hints to constrain the optimizer into the previous, better execution plan.

It is also not unheard of that some of the anomalously behaving queries can go back to their old, better execution plans spontaneously, as the index statistics get gradually updated due to ongoing data changes. Caveat being that this also means they can spontaneously get worse over time – and even without version upgrades, which is why it is important to regularly review query performance to spot such events early.

Mitigating Risks of Performance Anomalies

To minimize performance anomalies upon upgrades:

  • Thorough Testing: Test your critical queries and applications with the new version before migrating to ensure they behave as expected.
  • Monitor Post-Upgrade: Keep an eye on query latencies post-upgrade to identify any significant changes early.
  • Analyze Slow Queries: Use tools like SSM (Shattered Silicon Monitoring) to analyze query performance and execution plans before and after the upgrade.
  • Adjust Indexes and Statistics: If necessary, adjust your indexes and queries to constrain the optimizer into the old, better execution plan.
  • Update index statistics using ANALYZE TABLE. This isn’t normally required, but it is worth a try just in case the index statistics are misleading compared to the actual distribution of data.
  • Review Optimizer Settings: Check if any changes in optimizer settings can help improve performance post-upgrade. Sometimes new optimizations are added or enable by default. Disabling them using the optimizer_switch setting can lead to the original execution plan being restored.

Conclusion

While major upgrades in MySQL and MariaDB often bring useful new features and ensure that you will keep getting bug fixes for another support cycle, they also often introduce performance anomalies due to changes in query optimization logic and index statistics calculation methods. Being prepared for these anomalies and taking proactive measures can help ensure a smoother upgrade process with minimal impact on your database’s performance.

If you are facing a database upgrade in the near future in order to remain compliant and continue to receive updates, and you have concerns about performance and functionality anomalies, give our MySQL consulting team a call.