Início / dba / Perguntas / 267143
Accepted
Eaten by a Grue
Asked: 2020-05-15 06:18:23 +0800 CST

Problemas de desempenho após a atualização para o MySQL 8

  • 772

ATUALIZAÇÃO (tl;dr;) :

Eu arquivei um relatório de bug aqui: https://bugs.mysql.com/bug.php?id=99593 que já foi reconhecido e uma solução alternativa fornecida. Veja a resposta abaixo para detalhes.

Certas consultas parecem estar lutando no MySQL 8.0.20 e eu estou querendo saber se alguém pode apontar para alguma resolução possível. No momento eu tenho o servidor antigo funcionando, ainda na versão 5.7.30, então é fácil fazer A/B dos resultados de desempenho. Ambos os servidores possuem 32 GB de RAM, configuração quase idêntica e todas as tabelas são InnoDB. Aqui estão algumas das configurações (relevantes):

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

Exemplo 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;

Essa consulta produz a seguinte explicação:

MySQL 8.0.20 (a consulta leva 24 segundos):

+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| 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 (a consulta leva 12 segundos):

+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| 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     |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+

As duas tabelas em questão são idênticas em ambos os servidores. Neste caso parece que o plano é um pouco diferente mas tenho outros assim:

Exemplo 2:

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

Este produz explicações idênticas em ambos os servidores, mas em média 0,07 segundos no MySQL 5.7 e 0,30 segundos no MySQL 8, aproximadamente 4 vezes mais!

+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
| 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                  |
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+

Estou completamente perdido neste momento e espero que alguém possa ajudar a esclarecer o que pode estar levando a um desempenho tão ruim após essa atualização.

ATUALIZAÇÃO: Conforme solicitado, aqui estão os esquemas das tabelas envolvidas nas consultas mencionadas acima:

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;

STATUS DA TABELA: É interessante notar que TABLE_ROWSestão errados para ambos. select count(1) from product_to_vehicle;me dá 18330148 em ambos os casos e as tabelas 8.0 são resultado de dump e importação para 8.0, então não há razão para que sejam diferentes.

Status da Tabela em 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 |                |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+

Status da Tabela em 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 |                |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+

Mais análises:

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)"
          }
        }
      ]
    }
  }
}

E aqui está uma comparação da segunda consulta:

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"
            ]
          }
        }
      ]
    }
  }
}

Estranhamente, esses números parecem indicar que o MySQL 8 tem um custo geral menor, mas ainda está executando muito mais lentamente.

mysql-5.7 query-performance

3 respostas

  1. Best Answer

    Shane Bester fez uma sugestão como um comentário no meu relatório de bug que explica que este é um bug conhecido e será resolvido na próxima versão. Mais importante, há uma solução alternativa que tentei com grande sucesso. Obrigado Shane onde quer que você esteja!

    internal_tmp_mem_storage_engine=MEMORY

    Shane disse isso:

    Obrigado pelos dados do teste. Encontrou uma solução alternativa no 8.0.20 para melhorar a velocidade:

    SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

    Parece que este bug é uma duplicata do arquivado internamente:

    Bug 30562964 : 8.0.18: REGRESSÃO DE DESEMPENHO EM SELECT DISTINCT que foi introduzido em 8.0.18 e corrigido em 8.0.21.

    Confirmei isso em 8.0.17 e na versão interna atual de 8.0. a regressão de desempenho se foi.

    Portanto, alterar o internal_tmp_mem_storage_engine deve ajudar até que o 8.0.21 seja lançado (sem ETA).

    • 7

  2. Sugestões a serem consideradas para sua seção my.cnf [mysqld]

    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.

    • 2

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

    • 1

relate perguntas