UPDATE (tl;dr;):
I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details.
Certain queries seem to be struggling under MySQL 8.0.20 and I'm wondering if anyone can point to some possible resolution. At the moment I have the old server up and running, still on 5.7.30 so it's easy to A/B the performance results. Both servers have 32GB of RAM, nearly identical configuration and all tables are InnoDB. Here are some of the (relevant) settings:
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_instances = 12
innodb_buffer_pool_size = 16G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1536M
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_thread_concurrency = 0
Example 1:
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 query yields the following explain:
MySQL 8.0.20 (query takes 24 seconds):
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| 1 | SIMPLE | pts | NULL | ref | PRIMARY,product_id,store_id,store_product | store_id | 4 | const | 813308 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | ptv | NULL | ref | product_vehicle_submodel,vehicle_product | product_vehicle_submodel | 4 | pts.product_id | 53 | 50.00 | Using where; Using index |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
MySQL 5.7.30 (query takes 12 seconds):
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| 1 | SIMPLE | pts | NULL | ref | PRIMARY,product_id,store_id,store_product | store_product | 4 | const | 547242 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | ptv | NULL | ref | product_vehicle_submodel,vehicle_product | product_vehicle_submodel | 4 | pts.product_id | 22 | 50.00 | Using where; Using index |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
The two tables in question are identical on both servers. In this case looks like the plan is a bit different but I have others like this:
Example 2:
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!
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
| 1 | SIMPLE | vt | NULL | index | PRIMARY | vehicle_type_name | 194 | NULL | 11 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | bv | NULL | ref | vehicle_type_id | vehicle_type_id | 2 | vt.vehicle_type_id | 6428 | 100.00 | Using index |
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
I'm at a complete loss at this point and hoping someone can help illuminate what might be leading to such poor performance after this upgrade.
UPDATE: As requested, here are the table schema for the tables involved in those queries mentioned above:
CREATE TABLE `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 `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 `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 `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;
TABLE STATUS: It's interesting to note that the TABLE_ROWS are wrong for both. select count(1) from product_to_vehicle; gets me 18330148 in both cases and the 8.0 tables are result of dump and import to 8.0 so there's no reason these should be different.
Table Status on 8.0.20
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+
| TABLE_NAME | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+
| base_vehicle | InnoDB | 10 | Dynamic | 72210 | 36 | 2637824 | 0 | 12681216 | 4194304 | 150814 | 2020-05-14 04:16:34 | NULL | NULL | utf8_general_ci | NULL | |
| product_to_store | InnoDB | 10 | Dynamic | 2636946 | 32 | 86622208 | 0 | 124452864 | 5242880 | NULL | 2020-05-14 04:24:26 | 2020-05-14 04:31:18 | NULL | utf8_general_ci | NULL | |
| product_to_vehicle | InnoDB | 10 | Dynamic | 22502991 | 50 | 1147092992 | 0 | 1274970112 | 7340032 | 23457421 | 2020-05-14 05:15:41 | 2020-05-14 05:24:36 | NULL | utf8_general_ci | NULL | |
| vehicle_type | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 16384 | 0 | 2190 | 2020-05-14 04:29:15 | NULL | NULL | utf8_general_ci | NULL | |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+
Table Status on 5.7.30
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+
| TABLE_NAME | Engine | Version | Row_format | table_rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | table_collation | Checksum | Create_options |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+
| base_vehicle | InnoDB | 10 | Dynamic | 70716 | 52 | 3686400 | 0 | 11124736 | 4194304 | 150814 | 2020-05-14 01:04:16 | NULL | NULL | utf8_general_ci | NULL | |
| product_to_store | InnoDB | 10 | Dynamic | 2517116 | 39 | 99270656 | 0 | 144637952 | 7340032 | NULL | 2020-05-08 22:36:31 | NULL | NULL | utf8_general_ci | NULL | |
| product_to_vehicle | InnoDB | 10 | Dynamic | 15627279 | 37 | 584024064 | 0 | 1739882496 | 685768704 | 23457421 | 2020-05-14 01:03:35 | NULL | NULL | utf8_general_ci | NULL | |
| vehicle_type | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 16384 | 0 | 2190 | 2020-05-08 22:36:31 | NULL | NULL | utf8_general_ci | NULL | |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+
More analytics:
8.0.20
EXPLAIN ANALYZE 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;
| -> Table scan on <temporary> (actual time=0.001..3.453 rows=60193 loops=1)
-> Temporary table with deduplication (actual time=27786.823..27795.343 rows=60193 loops=1)
-> Nested loop inner join (cost=3222988.86 rows=14633875) (actual time=0.064..6910.370 rows=8610547 loops=1)
-> Index lookup on pts using store_id (store_id=21) (cost=81628.75 rows=813308) (actual time=0.041..176.566 rows=420673 loops=1)
-> Filter: (ptv.vehicle_id <> 0) (cost=0.26 rows=18) (actual time=0.006..0.014 rows=20 loops=420673)
-> Index lookup on ptv using product_vehicle_submodel (product_id=pts.product_id) (cost=0.26 rows=36) (actual time=0.006..0.011 rows=20 loops=420673)
5.7.30
EXPLAIN format = JSON 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;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2711880.30"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "pts",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"product_id",
"store_id",
"store_product"
],
"key": "store_product",
"used_key_parts": [
"store_id"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 547242,
"rows_produced_per_join": 547242,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1067.75",
"eval_cost": "109448.40",
"prefix_cost": "110516.15",
"data_read_per_join": "8M"
},
"used_columns": [
"product_id",
"store_id"
]
}
},
{
"table": {
"table_name": "ptv",
"access_type": "ref",
"possible_keys": [
"product_vehicle_submodel",
"vehicle_product"
],
"key": "product_vehicle_submodel",
"used_key_parts": [
"product_id"
],
"key_length": "4",
"ref": [
"pts.product_id"
],
"rows_examined_per_scan": 18,
"rows_produced_per_join": 5097113,
"filtered": "50.00",
"using_index": true,
"cost_info": {
"read_cost": "562530.32",
"eval_cost": "1019422.75",
"prefix_cost": "2711880.30",
"data_read_per_join": "77M"
},
"used_columns": [
"product_to_vehicle_id",
"product_id",
"vehicle_id",
"submodel_id"
],
"attached_condition": "(`ptv`.`vehicle_id` <> 0)"
}
}
]
}
}
}
And here's a comparison of the second query:
8.0.20
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "7186.24"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "vt",
"access_type": "index",
"possible_keys": [
"PRIMARY"
],
"key": "vehicle_type_name",
"used_key_parts": [
"vehicle_type_name"
],
"key_length": "194",
"rows_examined_per_scan": 11,
"rows_produced_per_join": 11,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.25",
"eval_cost": "1.10",
"prefix_cost": "1.35",
"data_read_per_join": "2K"
},
"used_columns": [
"vehicle_type_id",
"vehicle_type_name"
]
}
},
{
"table": {
"table_name": "bv",
"access_type": "ref",
"possible_keys": [
"vehicle_type_id"
],
"key": "vehicle_type_id",
"used_key_parts": [
"vehicle_type_id"
],
"key_length": "2",
"ref": [
"vt.vehicle_type_id"
],
"rows_examined_per_scan": 6519,
"rows_produced_per_join": 71712,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "13.69",
"eval_cost": "7171.20",
"prefix_cost": "7186.24",
"data_read_per_join": "1M"
},
"used_columns": [
"vehicle_id",
"vehicle_type_id"
]
}
}
]
}
}
}
5.7.30
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "14684.01"
},
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "vt",
"access_type": "index",
"possible_keys": [
"PRIMARY"
],
"key": "vehicle_type_name",
"used_key_parts": [
"vehicle_type_name"
],
"key_length": "194",
"rows_examined_per_scan": 11,
"rows_produced_per_join": 11,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "2.20",
"prefix_cost": "3.20",
"data_read_per_join": "2K"
},
"used_columns": [
"vehicle_type_id",
"vehicle_type_name"
]
}
},
{
"table": {
"table_name": "bv",
"access_type": "ref",
"possible_keys": [
"vehicle_type_id"
],
"key": "vehicle_type_id",
"used_key_parts": [
"vehicle_type_id"
],
"key_length": "2",
"ref": [
"vt.vehicle_type_id"
],
"rows_examined_per_scan": 6647,
"rows_produced_per_join": 73126,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "55.61",
"eval_cost": "14625.20",
"prefix_cost": "14684.01",
"data_read_per_join": "1M"
},
"used_columns": [
"vehicle_id",
"vehicle_type_id"
]
}
}
]
}
}
}
Oddly, these numbers seem to indicate the MySQL 8 has lower overall cost but is still executing much slower.
Best Answer
Shane Bester made a suggestion as a comment on my bug report which explains that this is a known bug and will be resolved in next release. More importantly, there is a workaround which I've tried with great success. Thanks Shane wherever you are!
Shane said this: