Resolving query plan regressions after a MySQL engine upgrade

The useful part

Resolving query plan regressions after a MySQL engine upgrade | AWS Database Blog Skip to Main Content AWS Database Blog Resolving query plan regressions after a MySQL engine upgrade. When upgrading an Amazon Aurora MySQL or Amazon RDS for MySQL major version, some queries might experience performance regressions. Queries that previously ran in milliseconds may slow down to several seconds, even when the application code, schema, and traffic patterns remain unchanged.

How it works

• In this post, we walk you through the diagnostic workflow we apply when a query regresses after a major version upgrade.
• Each MySQL 8.0.x release revises how the optimizer estimates the cost of candidate query execution plans, and in some releases also flips new optimizer_switch flags to on by default.
• The first hours after an upgrade can show elevated ReadIOPS and latency not because the query plan changed, but because the buffer pool is cold.
• Update table statistics An engine upgrade may change how InnoDB samples data pages to estimate index cardinality.
• Column Value type ref key idx_cust_id rows 12 filtered 100.00 Extra Using where After the upgrade (plan regression):

What to take from it

Because the underlying cost model changed, the optimizer now picks different execution plans by default, sometimes considering strategies your previous version never evaluated at all. These regressions might appear only under production load, hours after the upgrade completes. A major version jump (for example, 8.0 to 8.4) accumulates years of optimizer changes in a single step: revised cost models, new default parameter values, expanded execution strategies, and updated character set defaults.

Example or evidence

• The innodb_stats_persistent_sample_pages parameter is global-only and cannot be set at the session level.
• On Aurora MySQL, the buffer pool uses a survivable page cache that persists across normal restarts, but a major version upgrade still clears it (Aurora performs a clean shutdown and rebuilds the engine), so...
• Parameter group change (affects all tables) aws rds modify-db-parameter-group \ --db-parameter-group-name your-parameter-group-name \ --parameters...
• Each section is tied to a specific change introduced between MySQL versions, so you can identify the exact mechanism responsible and apply the right fix.

Details worth keeping

We see this pattern most often with major version upgrades. Why performance changes after an upgrade Before you diagnose a specific query, it helps to understand what actually changed in the engine. MySQL 8.0 and 8.4 both default to utf8mb4 with collation_server = utf8mb4_0900_ai_ci, so a straight 8.0-to-8.4 upgrade doesn't change this default.