Bug #99593 Performance issues in 8.0.20
Submitted: 15 May 2020 16:05 Modified: 25 May 2020 11:17
Reporter: Billy Sullivan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.20 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:Any

[15 May 2020 16:05] Billy Sullivan
Description:
Having some major performance issues with queries that were fine in 5.7.30 before migrating to Ubuntu 20.04 / MySQL 8.0.20.  Execution is taking roughly 3x as long to complete.

How to repeat:
I've been able to reproduce issue simply by spinning up to clean instances of Ubuntu 16.04 and Ubuntu 20.04, installing mysql server, importing test data and executing a couple of queries.

Test data: https://zuma-design.com/shared/test.sql

TABLEs (included in test data):

CREATE TABLE `oc_product_to_store` (
  `product_id` int NOT NULL,
  `store_id` int NOT NULL,
  PRIMARY KEY (`product_id`,`store_id`),
  KEY `product_id` (`product_id`),
  KEY `store_id` (`store_id`),
  KEY `store_product` (`store_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `oc_product_to_vehicle` (
  `product_to_vehicle_id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `vehicle_id` mediumint NOT NULL DEFAULT '0',
  `submodel_id` smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_to_vehicle_id`),
  KEY `submodel_id` (`submodel_id`),
  KEY `product_vehicle_submodel` (`product_id`,`vehicle_id`,`submodel_id`),
  KEY `vehicle_product` (`vehicle_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `oc_vehicle_type` (
  `vehicle_type_id` smallint NOT NULL AUTO_INCREMENT,
  `vehicle_type_name` varchar(64) NOT NULL,
  PRIMARY KEY (`vehicle_type_id`),
  KEY `vehicle_type_name` (`vehicle_type_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `oc_base_vehicle` (
  `vehicle_id` mediumint NOT NULL AUTO_INCREMENT,
  `year` smallint NOT NULL DEFAULT '0',
  `make_id` smallint NOT NULL DEFAULT '0',
  `model_id` mediumint NOT NULL DEFAULT '0',
  `vehicle_type_id` smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (`vehicle_id`),
  KEY `make_id` (`make_id`),
  KEY `model_id` (`model_id`),
  KEY `year_make` (`year`,`make_id`),
  KEY `year_model` (`year`,`model_id`),
  KEY `vehicle_type_id` (`vehicle_type_id`),
  KEY `ymm` (`year`,`make_id`,`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Queries to compare between versions:

SELECT DISTINCT vehicle_id, submodel_id, store_id
FROM product_to_store pts
    JOIN product_to_vehicle ptv USING (product_id)
WHERE vehicle_id != 0 AND pts.store_id = 21;

This one takes me roughly 8 seconds on 5.7.30 and over 30 seconds on 8.0.20.

SELECT DISTINCT vehicle_type_id, vehicle_type_name
FROM base_vehicle bv
    INNER JOIN vehicle_type vt USING (vehicle_type_id);

This one yields identical explain on both servers but averages 0.07 sec on MySQL 5.7 and 0.30 Sec on MySQL 8, roughly 4 times as long;
[16 May 2020 9:10] MySQL Verification Team
Thanks for the test data.  Found a workaround on 8.0.20 to improve the speed:

    SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

It seems this bug is a duplicate of internally filed:

Bug 30562964 : 8.0.18: PERFORMANCE REGRESSION IN SELECT DISTINCT
which was introduced in 8.0.18 and fixed in 8.0.21.

I've confirmed that on 8.0.17 and current internal build of 8.0. the performance regression is gone.

So changing the internal_tmp_mem_storage_engine should help until 8.0.21 is released (no ETA).
[16 May 2020 12:16] Billy Sullivan
Thanks Shane, that worked!
[25 May 2020 11:17] Erlend Dahl
Already fixed in the upcoming 8.0.21 release under the heading of

Bug#30562964 8.0.18: PERFORMANCE REGRESSION IN SELECT DISTINCT
[1 Oct 2021 13:05] Derli Dias Campos Junior
By running:

    SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

The variable value won't be changed to MEMORY, it will stay as TempTable

Any suggestion?