主页 / dba / 问题 / 135180
Accepted
Ross
Asked: 2016-04-14 07:46:19 +0800 CST

Percona 5.7 tokudb 查询性能不佳 - 选择了错误的（非聚集）索引

  • 772

我有一张桌子，里面有大约 850 万行。该表是 tokudb，它具有下面描述的索引。尝试运行如下更新语句时，我遇到了糟糕的性能：

 update retail.lw_item_discovery 
 set price = 'X', 
     prev_price = 'Y', 
     last_updated = '2016-04-13', 
     last_price_change = '2016-04-13' 
 where market = 'XX' 
   and sku = '123456'

执行此更新需要 40 秒以上。像这样的其他更新也经常发生，但是这台机器的 I/O 子系统丝毫没有受到压力（raided SSD），而且还有足够的 RAM 可用。

EXPLAIN产量：

+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table             | partitions | type  | possible_keys                                              | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
|  1 | UPDATE      | lw_item_discovery | NULL       | index | cl_unique_idx,cl_mkt_sku_upd_avail_idx,market_sku_item_idx | PRIMARY | 4       | NULL |  100 |   100.00 | Using where; Using temporary |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
1 row in set (0.00 sec)

基于此 - 它选择PRIMARY索引而不是其他索引之一，例如cl_unique_idx在前两个位置的 where 语句中有两列。所以我很困惑为什么规划者选择了PRIMARY而不是导致性能如此糟糕。以下是索引列表：

+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                 | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lw_item_discovery |          0 | PRIMARY                  |            1 | itd_id          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          0 | cl_unique_idx            |            1 | sku             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          0 | cl_unique_idx            |            2 | market          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          0 | cl_unique_idx            |            3 | upc             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          0 | cl_unique_idx            |            4 | model_num       | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          0 | cl_unique_idx            |            5 | item_id         | A         |          82 |     NULL | NULL   | YES  | BTREE      |         |               |
| lw_item_discovery |          1 | update_idx               |            1 | last_updated    | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | update_idx               |            2 | market          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | update_idx               |            3 | sku             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | description_idc          |            1 | web_description | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | category_idx             |            1 | web_category    | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | category_idx             |            2 | upc             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | category_idx             |            3 | sku             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | upc_idx                  |            1 | upc             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | item_id_idx              |            1 | item_id         | A         |          82 |     NULL | NULL   | YES  | BTREE      |         |               |
| lw_item_discovery |          1 | item_id_idx              |            2 | market          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | item_id_idx              |            3 | available       | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | cl_mkt_sku_upd_avail_idx |            1 | sku             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | cl_mkt_sku_upd_avail_idx |            2 | market          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | cl_mkt_sku_upd_avail_idx |            3 | last_updated    | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | cl_mkt_sku_upd_avail_idx |            4 | available       | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | market_sku_item_idx      |            1 | market          | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | market_sku_item_idx      |            2 | sku             | A         |          82 |     NULL | NULL   |      | BTREE      |         |               |
| lw_item_discovery |          1 | market_sku_item_idx      |            3 | item_id         | A         |          82 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24 rows in set (0.00 sec)

我不得不将tokudb_lock_timeout时间从 4 秒增加到 40 秒，以免出现大量的锁等待争用。我在这里错过了什么吗？

表定义

`lw_item_discovery` (
  `item_id` bigint(20) unsigned DEFAULT '0',
  `chain` varchar(12) NOT NULL DEFAULT 'lowes',
  `market` varchar(4) NOT NULL DEFAULT '',
  `available` varchar(1) NOT NULL DEFAULT 'y',
  `last_updated` date NOT NULL DEFAULT '0000-00-00',
  `itd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `web_description` varchar(255) NOT NULL DEFAULT '',
  `model_num` varchar(100) NOT NULL DEFAULT '' COMMENT 'its only 1char cause its not currently used. Its here for consistency',
  `price` decimal(6,2) NOT NULL DEFAULT '0.00',
  `item_link_url` text NOT NULL,
  `item_img_url` text NOT NULL,
  `store_shopped` smallint(5) unsigned NOT NULL DEFAULT '0',
  `sku` varchar(32) NOT NULL DEFAULT '0',
  `upc` varchar(12) NOT NULL DEFAULT '',
  `web_category` varchar(255) NOT NULL DEFAULT '',
  `mfr` varchar(100) NOT NULL DEFAULT '',
  `class` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `subclass` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `first_found` date NOT NULL DEFAULT '0000-00-00' COMMENT 'first time it was seen in market',
  `last_price_change` date NOT NULL DEFAULT '0000-00-00' COMMENT 'the date of the last price change observed',
  `discontinued` varchar(1) NOT NULL DEFAULT 'n',
  `discontinued_date` date NOT NULL DEFAULT '0000-00-00',
  `prev_price` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
  `rating` decimal(4,2) NOT NULL DEFAULT '-1.00',
  `review_count` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`itd_id`),
  UNIQUE KEY `cl_unique_idx` (`sku`,`market`,`upc`,`model_num`,`item_id`),
  KEY `update_idx` (`last_updated`,`market`,`sku`),
  KEY `description_idc` (`web_description`),
  KEY `category_idx` (`web_category`,`upc`,`sku`),
  KEY `upc_idx` (`upc`),
  KEY `item_id_idx` (`item_id`,`market`,`available`) USING BTREE,
  KEY `cl_mkt_sku_upd_avail_idx` (`sku`,`market`,`last_updated`,`available`),
  CLUSTERING KEY `market_sku_item_idx` (`market`,`sku`,`item_id`)
) ENGINE=TokuDB AUTO_INCREMENT=8858224 DEFAULT CHARSET=latin1

每次更新的行数最多应为 1-3。更新的速度大约为每秒 1 次到每秒 3-4 次，通常高达每秒几十次。

这是在 Percona Server 5.7 上。

mysql update

2 个回答

  1. 与 InnoDB 不同，TokuDB 历史上不会自动计算基数统计信息。作为用户，您需要手动运行ANALYZE TABLE才能计算这些值。

    在 5.6.27-76.0 之前创建的所有表和索引也不会保持准确的行数。在 5.6.27-76.0 之后，新的表和索引，以及有RECOUNT ROWS分析的表，都会准确地跟踪行数。这对于基数指标非常重要，尤其是对于分区表的基数。

    请参阅以下描述分析更改的文档：

    在 5.7.11-4 之前，默认禁用自动背景分析。从 5.7.11-4 开始，当大约 30% 的表被更改（插入/更新/删除）时，默认启用自动后台分析。您可以通过操作上面链接中记录的各种系统变量来更改此阈值和分析的其他几个方面。

    将您的数据重新加载到比 5.6.27-76.0 更新的服务器中会更正不准确的行计数，并且迁移到 5.7.11-4 会启用自动后台分析。

    如果你打算使用 TokuDB，你应该确定你的理由，TokuDB 不仅仅是“在所有负载方面都比 InnoDB 好”。它具有特定的优势和权衡，以及在性能上不如 InnoDB 的用例，并且通常不如 InnoDB 成熟。

    如果您需要压缩、插入负载很重、存储速度很慢，或者如果您的数据集大大超过可用内存，TokuDB 可能是一个不错的选择。如果您需要原始随机点查询性能，有大量顺序删除，然后是覆盖查询，有大的 char/varchar/blobs (> 32K)，有足够的快速存储（尽管 TokuDB 可以减少闪存磨损），或者有一个小数据集这是物理内存大小的一个小倍数，TokuDB 可能不适合你。

    我现在还注意到您说您只有 100GB 的数据但有 500GB 的内存（带有 100GB innodb 缓冲池）。在这种情况下，您的大部分/所有数据都将适合内存。InnoDB 应该是这里明显的性能赢家。TokuDB（还）没有针对内存中的工作负载进行优化，在这种情况下，InnoDB 将在几乎 100% 的时间内击败它。现在，如果您有 100GB 的内存和 1TB 的数据和索引，那么 TokuDB 将值得考虑。

    （我是 Percona 的软件工程师。）

    • 6
  2. Best Answer

    最终 - 转储表并重新加载解决了奇怪的基数和行数行为。我们尝试使用分析表，但这并没有解决问题。 乔治的回答非常好，但不幸的是它不能解决我的问题。

    • 1

相关问题