MySQL join has drastically worse performance after upgrading from 5.6 to 5.7

I have a fairly straightforward query that has become very slow after upgrading from MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS).

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;


In 5.6, this query completes in 90 seconds. After upgrading to 5.7, it’s taking over 30 minutes. Each table has approximately 2 million rows.

I started by comparing the optimizer settings between 5.6 and 5.7:

# 5.6
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
subquery_materialization_cost_based=on
use_index_extensions=on

# 5.7
Optimizer settings in 5.7:
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on
duplicateweedout=on

The only changes I see were the last three options in 5.7. I switched them off as follows:

SET optimizer_switch='condition_fanout_filter=off';
SET optimizer_switch='derived_merge=off';
SET optimizer_switch='duplicateweedout=off';


This had no effect. Next, I looked at the differences in my innodb settings (my 5.7 instance has more memory, hence the pool size differences).

```
# my innodb changes 5.6 -> 5.7
innodb_adaptive_hash_index_parts added in 5.7, set to ‘8’
innodb_additional_mem_pool_size set to 8388608 in 5.6, removed in 5.7
innodb_buffer_pool_dump_at_shutdown changed from ‘OFF’ to ‘ON’
innodb_buffer_pool_dump_pct added in 5.7, set to ’25’
innodb_buffe

Analysis of VARIABLES and GLOBAL STATUS:

(Alas, nothing here jumps out as an explanation of the Question in hand)

I dislike doing FORCE INDEX, etc, but STRAIGHT_JOIN may be the simplest and safest way to force the ordering of the two tables.

Observations:

• Version: 5.7.25-log

• 15.25 GB of RAM

• Uptime = 14:05:48; some GLOBAL STATUS values may not be meaningful yet.

• You are not running on Windows.

• Running 64-bit version

• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

With SSD, change these:

innodb_flush_neighbors = 0 -- from 1
innodb_io_capacity = 800 -- from 200

It sounds like your dataset size is considerably less than innodb_buffer_pool_size. So, perhaps you are paying for more RAM than you need? If you are preparing for growth, then ignore this comment.

With rare exceptions, I don't think innodb_deadlock_detect should be OFF.

A lot of poorly performang queries. Crank down long_query_time and make use of the slowlog: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Since only 11 connections seem to be needed, lower max_connections to, say 40. This will avoid certain pressures on RAM.

Are you sure you want READ-UNCOMMITTED?

Details and other observations:

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 272 / (6879 + 272) = 3.8% -- Effectiveness of table_open_cache.
-- Increase table_open_cache and check table_open_cache_instances.

( innodb_lru_scan_depth innodb_page_cleaners ) = 1,024 4 = 4,096 -- Amount of work for page cleaners every second.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners to innodb_buffer_pool_instances

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 494,639 / 720896 = 68.6% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,590,832,128 / 11264M = 30.4% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.

( Uptime / 60 innodb_log_file_size / Innodb_os_log_written ) = 50,748 / 60 128M / 2513408 = 45,166 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( innodb_deadlock_detect ) = innodb_deadlock_detect = OFF -- 5.7.15 provides way to speed up heavy inserting by turning off deadlock detection
-- Do you really want to turn it OFF?

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Created_tmp_disk_tables / Questions ) = 37,591 / 73621 = 51.1% -- Pct of queries that needed on-disk tmp table.
-- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / Created_tmp_tables ) = 37,591 / 46560 = 80.7% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (168 + 0 + 2 + 0) / 170 = 1 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 51,492 / 50748 = 1 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 51,492 / 71543 = 72.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 2 - 0 ) / ( 2 + 0 ) = 100.0% -- Are you ( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after

StackExchange Database Administrators Q#235351, answer score: 2