I have a fairly simple query that has become very slow after upgrading MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS).
SELECT DIFFERENT
Name, d.id, deviceType, issuedBy, description, avNum, CompanyName,
Brand name, dwNumber, quant, discDate, Type
FROM table_one d
JOIN table_two i ON d.id = i.id;
In 5.6, this query is completed in 90 seconds. After updating to 5.7, it is taking more than 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 = in
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = en
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
materialization = in
semijoin = en
loosescan = on
firstmatch = on
subquery_materialization_cost_based = on
use_index_extensions = on
# 5.7
Optimizer settings in 5.7:
index_merge = in
index_merge_union = on
index_merge_sort_union = on
index_merge_intersection = on
engine_condition_pushdown = on
index_condition_pushdown = on
mrr = en
mrr_cost_based = on
block_nested_loop = on
batched_key_access = off
materialization = in
semijoin = en
loosescan = on
firstmatch = on
subquery_materialization_cost_based = on
use_index_extensions = on
condition_fanout_filter = on
derivative_merge = in
duplicateweedout = on
The only changes I see are the last three options in 5.7. I turned them off as follows:
SET optimizer_switch = & # 39; condition_fanout_filter = off & # 39 ;;
SET optimizer_switch = & # 39; Derivado_merge = off & # 39 ;;
SET optimizer_switch = & # 39; duplicateweedout = off & # 39 ;;
This had no effect. Next, I observed the differences in my innodb configuration (my instance of 5.7 has more memory, hence the differences in the size of the cluster).
# my innodb changes 5.6 -> 5.7
innodb_adaptive_hash_index_parts added in 5.7, set to & # 39; 8 & # 39;
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_buffer_pool_load_at_startup changed from 'OFF' to 'ON'
innodb_buffer_pool_size changed from 2,804,940,800 to 11,811,160,064
innodb_checksum_algorithm changed from "innodb" to "crc32"
innodb_deadlock_detect added in 5.7, set to & # 39; ON & # 39;
innodb_default_row_format added in 5.7, configured as 'dynamic'
innodb_file_format changed from "Antelope" to "Barracuda"
innodb_file_format_max changed from "Antelope" to "Barracuda"
innodb_fill_factor added in 5.7 set to & # 39; 100 & # 39;
innodb_flush_sync added in 5.7 set to & # 39; ON & # 39;
innodb_log_checksums added in 5.7 set to & # 39; ON & # 39;
innodb_log_write_ahead_size added in 5.7 set to 8192
innodb_max_undo_log_size added in 5.7 set to 1,073,741,824
innodb_mirrored_log_groups deleted in 5.7, set to & # 39; 1 & # 39; in 5.6
innodb_numa_interleave added in 5.7 set to "DISABLED"
innodb_page_cleaners added in 5.7 set to '4'
innodb_purge_rseg_truncate_frequency added in 5.7 set to & # 39; 128 & # 39;
innodb_strict_mode changed from 'OFF' to 'ON'
innodb_temp_data_file_path added in 5.7, set to & # 39; ibtmp1: 12M: autoextend & # 39; ibtmp1: 12M:
innodb_undo_log_truncate added in 5.7, set to & # 39; OFF & # 39;
I have reviewed the reference manual.
and tried to turn off the following:
innodb_strict_mode = 'OFF'
innodb_deadlock_detect = 'OFF'
innodb_flush_sync = & # 39; OFF & # 39;
innodb_log_checksums = 'OFF'
Also, it has no effect.
I hope someone can tell me what is happening here. The query is part of a larger workflow, which has become unusable as a result. I would like to recover the behavior of 5.6 if possible, but with my limited knowledge of DBA, I am running out of things to try. I hope someone can point me in the right direction or give me additional avenues to investigate.
Below are my tables. Many of the columns have large widths, and are mostly defined as VARCHAR (X) DEFAULT NULL because the data source is not clean (I have no control over the source). Note that I need utf8 data, but I am using latin1 while troubleshooting slow query problems, to eliminate that as a possible cause of slowness. (The reason we need to migrate from 5.6 -> 5.7 is for the larger index sizes in 5.7, which can accommodate our larger UTF8 column sizes).
(In addition, I must point out that the I.D the field comes from the source data and is a long string of alphanumeric characters of variable width (up to ~ 30 characters), so I need it to be a VARCHAR).
CREATE TABLE `table_one` (
`id` varchar (200) NOT NULL DEFAULT & # 39; & # 39 ;,
`RecordKey` varchar (50) DEFAULT NULL,
`VersionStat` varchar (200) DEFAULT NULL,
`Status` varchar (200) DEFAULT NULL,
`VersionNumber` varchar (50) DEFAULT NULL,
`VersionDate` varchar (20) DEFAULT NULL,
`PublishDate` varchar (20) DEFAULT NULL,
`DistStart` varchar (20) DEFAULT NULL,
`DistCommStat` varchar (2000) DEFAULT NULL,
`BrandName` varchar (100) DEFAULT NULL,
`VersionModelNumber` varchar (100) DEFAULT NULL,
`Catalog` varchar (100) DEFAULT NULL,
`dwNumber` varchar (100) DEFAULT NULL,
`CompanyName` varchar (500) DEFAULT NULL,
`DeviceCount` varchar (200) DEFAULT NULL,
`description` varchar (3000) DEFAULT NULL,
`Exemption` varchar (1100) DEFAULT NULL,
`PreMarket` varchar (1500) DEFAULT NULL,
`DevDRMT` varchar (1000) DEFAULT NULL,
`DTKit` varchar (200) DEFAULT NULL,
`Combination` varchar (250) DEFAULT NULL,
`Usage` varchar (500) DEFAULT NULL,
`SingleBatch` varchar (50) DEFAULT NULL,
`SerialNumber` varchar (250) DEFAULT NULL,
`ManuDate` varchar (20) DEFAULT NULL,
`ExpDate` varchar (20) DEFAULT NULL,
`Donation` varchar (50) DEFAULT NULL,
`LabeldWithMLO` varchar (50) DEFAULT NULL,
`NLabledMLO` varchar (50) DEFAULT NULL,
`MLOStatus` varchar (1000) DEFAULT NULL,
`BTT` varchar (50) DEFAULT NULL,
`OPP` varchar (50) DEFAULT NULL,
`BRC` varchar (50) DEFAULT NULL,
`PriorUse` varchar (100) DEFAULT NULL,
PRIMARY KEY (`id`)
) MOTOR = InnoDB DEFAULT CHARSET = latin1
CREATE TABLE `table_two` (
`id` varchar (200) DEFAULT NULL,
`Name` varchar (200) DEFAULT NULL,
`deviceType` varchar (50) DEFAULT NULL,
`issueBy` varchar (200) DEFAULT NULL,
`avNum` varchar (50) DEFAULT NULL,
`quant` varchar (50) DEFAULT NULL,
`discDate` varchar (50) DEFAULT NULL,
`PkgStatus` varchar (50) DEFAULT NULL,
`Type` varchar (50) DEFAULT NULL,
KEY `idx_table_two_id` (` id`)
) MOTOR = InnoDB DEFAULT CHARSET = latin1
