Resolving query plan regressions after a MySQL engine upgrade

AI summary

This article outlines the technical causes behind query performance regressions following MySQL major version upgrades, such as optimizer cost model changes and new default flags. It provides a systematic diagnostic workflow using tools like EXPLAIN ANALYZE and ANALYZE TABLE to isolate and resolve execution plan shifts. The guide emphasizes the importance of baseline performance snapshots and proper buffer pool management to ensure post-upgrade stability.

Key points

• Major version upgrades introduce engine-level changes to optimizer cost models, flag defaults, and execution strategies that can force different query plans.
• Perform ANALYZE TABLE immediately after an upgrade to ensure the optimizer has accurate cardinality statistics.
• Use EXPLAIN ANALYZE to identify specific plan deviations caused by features like prefer_ordering_index or expanded hash join eligibility.
• Maintain a baseline of EXPLAIN output for top queries to significantly reduce mean time to resolution during future version transitions.
• Verify that buffer pool warm-up strategies are enabled in RDS for MySQL to prevent initial cold-start performance degradation.