Performance issues after upgrading to MySQL 8

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.

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!

internal_tmp_mem_storage_engine=MEMORY

Shane said this:


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).

StackExchange Database Administrators Q#267143, answer score: 8