mysql-community-server 和 mysql-community-server-minimal 有什么区别？

Question

Eaten by a Grue

Asked: 2020-05-15 06:18:23 +0800 CST2020-05-15 06:18:23 +0800 CST 2020-05-15 06:18:23 +0800 CST

升级到 MySQL 8 后的性能问题

772

更新（tl；博士；）：

我在这里提交了一个错误报告：https ://bugs.mysql.com/bug.php?id=99593 ，该报告已被确认并提供了解决方法。有关详细信息，请参阅下面的答案。

某些查询似乎在 MySQL 8.0.20 下苦苦挣扎，我想知道是否有人可以指出一些可能的解决方案。目前我的旧服务器启动并运行，仍然在 5.7.30 上，所以很容易 A/B 性能结果。两台服务器都有 32GB 的 RAM，几乎相同的配置，所有的表都是 InnoDB。以下是一些（相关）设置：

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

示例 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;

此查询产生以下解释：

MySQL 8.0.20（查询需要 24 秒）：

+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| 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（查询需要 12 秒）：

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

有问题的两个表在两台服务器上都是相同的。在这种情况下，计划看起来有点不同，但我还有其他这样的：

示例 2：

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

这在两台服务器上产生相同的解释，但在 MySQL 5.7 上平均为 0.07 秒，在 MySQL 8 上平均为 0.30 秒，大约是 4 倍！

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

在这一点上，我完全不知所措，希望有人能帮助说明升级后可能导致性能如此糟糕的原因。

更新：根据要求，以下是上述查询中涉及的表的表架构：

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_ROWS是，两者都是错误的。select count(1) from product_to_vehicle;在这两种情况下都给我 18330148 和 8.0 表是转储和导入到 8.0 的结果，所以没有理由这些应该不同。

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

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

更多分析：

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

这是第二个查询的比较：

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

奇怪的是，这些数字似乎表明 MySQL 8 的总体成本较低，但执行速度仍然慢得多。

3 个回答

Voted

Eaten by a Grue · Answer 1 · 2020-05-17T04:14:29+08:00

Best Answer

Eaten by a Grue

2020-05-17T04:14:29+08:002020-05-17T04:14:29+08:00

Shane Bester 提出了一个建议作为对我的错误报告的评论，它解释了这是一个已知错误，将在下一个版本中解决。更重要的是，我尝试了一种解决方法并取得了巨大成功。感谢 Shane，无论您身在何处！

internal_tmp_mem_storage_engine=MEMORY

谢恩是这么说的：

感谢提供测试数据。在 8.0.20 上找到了一种解决方法来提高速度：
SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;
看来这个错误是内部提交的副本：

错误 30562964：8.0.18：在 8.0.18 中引入并在 8.0.21 中修复的 SELECT DISTINCT 中的性能回归。

我已经在 8.0.17 和当前的 8.0 内部版本上确认了这一点。性能回归消失了。

So changing the internal_tmp_mem_storage_engine should help until 8.0.21 is released (no ETA).

7

Wilson Hauck · Answer 2 · 2020-05-17T17:22:42+08:00

Wilson Hauck

2020-05-17T17:22:42+08:002020-05-17T17:22:42+08:00

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.

2

Rick James · Answer 3 · 2020-09-01T14:23:54+08:00

Rick James

2020-09-01T14:23:54+08:002020-09-01T14:23:54+08:00

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

升级到 MySQL 8 后的性能问题

示例 1：

示例 2：

更多分析：

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

升级到 MySQL 8 后的性能问题

示例 1：

示例 2：

更多分析：

3 个回答

相关问题