2

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.

1
6

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

2
  • just a note. Oracle have been saying that it's fixed in the next release for few releases now. as of 8.0.25 it's still a problem.
    – Mordechai
    Jul 19 at 7:48
  • @Mordechai - not for me. I was able to upgrade my package a month or so after this was posted and the issue disappeared.
    – billynoah
    Jul 19 at 17:11
2

Suggestions to consider for your my.cnf [mysqld] section

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_buffer_pool_instances=8  # from 16 to reduce overhead until more RAM is avail
innodb_flush_neighbors=2  # from 0 to clear innodb_buffer_pool_pages_dirty quicker
thread_cache_size=100  # from 13 to reduce threads_created of 252 in 29 hours

Looking forward to your contact next weekend.

1

product_to_vehicle sounds like a many-to-many mapping table with an extra column. You can probably remove the AUTO_INCREMENT column and promote one of the indexes to be the PK. After that, query 1 may be faster.

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

Query 2 does not need DISTINCT if you reformulate it thus:

SELECT  vehicle_type_id, vehicle_type_name
    FROM   vehicle_type vt
    WHERE EXISTS( SELECT 1 FROM base_vehicle
        WHERE vt.vehicle_type_id = vehicle_type_id );

Please provide EXPLAINs after such conversions.

Note: These suggestions may help whether or not the MEMORY is fixed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.