Following an upgrade from MySQL 5.6 to 5.7, the following query went from running in <2 minutes to taking over 30 minutes.
SELECT DISTINCT Name,d.id,deviceType,issuedBy, description,avNum,CompanyName, BrandName,dwNumber,quant,discDate,Type FROM table_one d JOIN table_two i ON d.id = i.id;
I have tried a variety of things to diagnose the issue.
-
I compared optimizer_switch settings between 5.6 and 5.7, and switched “off” the new settings:
SET optimizer_switch=‘condition_fanout_filter=off’; SET optimizer_switch=‘derived_merge=off’; SET optimizer_switch=‘duplicateweedout=off’; -
I compared configuration/variable settings between the 5.6 and 5.7 servers. Where applicable, I made the 5.7 settings match 5.6. I made quite a few changes to variables (more than I can list here) and none seemed to have much of an impact, better or worse.
-
I enabled the performance_schema tables and compared results.
The 5.7 instance shows many calls/time spent in something called “wait/synch/sxlock/innodb/hash_table_locks”. This led me to look more closely at InnoDB and storage.
- The two servers are in AWS RDS. As a comparison, I started two identical EC2 instances. One with MariaDB 10.2.21 (InnoDB version 5.7.24), and one with MySQL 5.7.25 (InnoDB version 5.7.25) using out-of-the-box config.
The 5.7 server still takes 30 minutes in this configuration, but like our 5.6 server, MariaDB took only 60 seconds! This makes me think 1) it’s not a storage contention issue, and 2) InnoDB doesn’t appear to be a limiting factor, because it works fine within MariaDB.
My thinking is it must be something different in the query execution plan chosen by the 5.7 server. I’m having trouble verifying this though.
The EXPLAINs for both are pretty similar. MariaDB has a “using where” and reverses the order of the tables. However, when I force the same table order in the 5.7 query (using STRAIGHT_JOIN), it doesn’t get any faster.
MariaDB: [URL]https://pastebin.com/Ma8tLQrm[/URL]
MySQL 5.7 (and 5.6): [URL]https://pastebin.com/S4QELM3c[/URL]
The 5.6 EXPLAIN is identical to the 5.7 EXPLAIN, even though they don’t behave in the same way.
Apologies for posting an external link, but I’ve gathered some more data, including my table definitions, here: [URL]MySQL join has drastically worse performance after upgrading from 5.6 to 5.7 - Database Administrators Stack Exchange
Does anyone know what might be going on? I’ve been stuck on this for nearly a week, and I don’t think diving into the source is going to be a productive exercise for me, so I’m blocked. I’m really interested to understand what is going on here. The performance hit is quite significant for such a straightforward query.