MySQL 5.7 Upgrade Caused Metadata Lock and Derived‑Table Performance Issues – Diagnosis and Fix

Background: A MySQL database was upgraded from version 5.6.22 to 5.7.32. The upgrade itself succeeded, but two days later the service reported that the database was unresponsive.

Symptoms: Queries showed many sessions stuck in Waiting for table metadata lock . The show processlist output displayed numerous long‑running INSERT statements (ids 6891 and 11528) that held the locks.

Initial Investigation: The performance_schema metadata_locks table returned empty, indicating that the MDL instrument was not enabled. Enabling it temporarily via UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl'; or permanently in my.cnf with performance-schema-instrument='wait/lock/metadata/sql/mdl=ON' made the lock information visible.

Transaction Check: Queries on INNODB_TRX revealed two large transactions (trx_id 328588354171408 and 328588354177792) corresponding to the problematic INSERTs, each holding thousands of row locks and consuming large memory.

Root Cause Analysis: The INSERT statements contain complex multi‑table sub‑queries that reference schemas db02 and db04. In MySQL 5.7 the optimizer option derived_merge is ON by default, causing derived tables to be merged before filtering, which dramatically increases the data volume processed and leads to severe slowdown and MDL lock buildup.

Resolution: The immediate fix was to kill the two INSERT sessions, which released the MDL locks and restored service. To prevent recurrence, the derived_merge option was disabled (either globally in my.cnf or per‑session) or the SQL was rewritten to avoid the costly derived‑table merge.

Solution Summary:

Disable the derived_merge optimizer setting (e.g., SET SESSION optimizer_switch='derived_merge=off'; or add derived_merge=off to my.cnf ).

Consider refactoring the INSERT‑SELECT statements to reduce derived‑table complexity.

After disabling derived_merge , the batch jobs ran normally and the database performance returned to expected levels.