v6.5.1 delete limit 删除慢

是我的海 · 2023 年4 月 12 日 03:50

【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.5.1
集群从5.4.0 升级到6.5.1 业务的删除语句全部变慢卡主无法执行。
执行sql 如下:

  `ocr_content_version` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'OCR数据版本0老版本 1三端统一版本',
`is_del` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除1表示已经删除',
`create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
`update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_owner_flag` (`owner_flag`),
KEY `idx_create_time` (`create_time`),
UNIQUE KEY `idx_uniq_sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=13803902764 */ COMMENT='xxx'
1 row in set (0.01 sec)

MySQL [up_ks_pigai]> 
MySQL [up_ks_pigai]> 
MySQL [up_ks_pigai]> explain DELETE FROM   `tblCorrectRecordScancode`   WHERE   create_time < 1668287229 limit 1000;
+----------------------------------+---------+-----------+--------------------------------------------------------------------+-------------------------------------------+
| id                               | estRows | task      | access object                                                      | operator info                             |
+----------------------------------+---------+-----------+--------------------------------------------------------------------+-------------------------------------------+
| Delete_5                         | N/A     | root      |                                                                    | N/A                                       |
| └─IndexLookUp_14                 | 1000.00 | root      |                                                                    | limit embedded(offset:0, count:1000)      |
|   ├─Limit_13(Build)              | 1000.00 | cop[tikv] |                                                                    | offset:0, count:1000                      |
|   │ └─IndexRangeScan_11          | 1250.00 | cop[tikv] | table:tblCorrectRecordScancode, index:idx_create_time(create_time) | range:[-inf,1668287229), keep order:false |
|   └─TableRowIDScan_12(Probe)     | 1000.00 | cop[tikv] | table:tblCorrectRecordScancode                                     | keep order:false                          |
+----------------------------------+---------+-----------+--------------------------------------------------------------------+-------------------------------------------+

删除任务是后半夜开始执行，监控上看到tidb-server 内存升高并且 tikv cpu 几乎打满。这个任务已经运行两年了，之前从未出现这个问题。

还有一个问题，我设置了max_execution_time = 5000，但是所有的delete sql 超时后并没有主动断开连接

xfworld · 2023 年4 月 12 日 06:10

有两个问题，你可以看看：

limit 1000 是硬性要求么？执行计划上一共就 1250 条记录，是不是不用 limit 也可以？
删除的步骤，是分为两步的，第一部是查，第二步才是删，那么慢到底是查慢，还是删慢？是不是能在甄别一下？

max_execution_time 的超时回滚，和session 链接断开没啥必然关系…

因为超时回滚，这个会话就没了，这种现象不是更奇怪么？

dbaspace · 2023 年4 月 12 日 06:18

强总，继续升级吧

dbaspace · 2023 年4 月 12 日 06:23

删除加上limit 应该是合理的，线上基本都是这么操作，看它这个有2个问题：
1、扫的KEY 有点多
2、并行度只有1

是我的海 · 2023 年4 月 12 日 06:24

1、这个表有几十亿数据，脚本是分批删除的，实际上是limit 5000;

统计信息早上刚搜集过了

2、删除的慢具体慢在哪里从上图的执行计划信息能分辨出来吗？这个sql 执行计划是走了正确的索引的
重新跑了一个delete 信息如下：麻烦看下能否大概定位到问题，为何indexlookup 这里会耗时这么久？

MySQL [up_ks_pigai]> explain analyze DELETE FROM   `tblCorrectRecordScancode`  force index(idx_create_time) WHERE   create_time < 1668287229 limit 5000;



+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+------+
| id                               | estRows | actRows | task      | access object                                                      | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | operator info                             | memory  | disk |
+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+------+
| Delete_5                         | N/A     | 0       | root      |                                                                    | time:2m16.9s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A                                       | 4.72 MB | N/A  |
| └─IndexLookUp_14                 | 5000.00 | 5000    | root      |                                                                    | time:2m16.8s, loops:6, index_task: {total_time: 2m16.7s, fetch_handle: 2m16.7s, build: 2.29µs, wait: 7.8µs}, table_task: {total_time: 220.2ms, num: 3, concurrency: 5}, next: {wait_index: 2m16.7s, wait_table_lookup_build: 568.5µs, wait_table_lookup_resp: 28.6ms}                                                                                                                                                                                                                                                                                                                                                                                          | limit embedded(offset:0, count:5000)      | 23.5 MB | N/A  |
|   ├─Limit_13(Build)              | 5000.00 | 6752    | cop[tikv] |                                                                    | time:2m16.7s, loops:6, cop_task: {num: 343, max: 1.94s, min: 276.5µs, avg: 398.4ms, p95: 793.4ms, max_proc_keys: 3040, p95_proc_keys: 0, tot_proc: 2m16.1s, tot_wait: 8ms, rpc_num: 343, rpc_time: 2m16.6s, copr_cache_hit_ratio: 0.14, distsql_concurrency: 1}, tikv_task:{proc max:1.94s, min:0s, avg: 456.5ms, p80:618ms, p95:793ms, iters:363, tasks:343}, scan_detail: {total_process_keys: 6752, total_process_keys_size: 310592, total_keys: 441734885, get_snapshot_time: 16.4ms, rocksdb: {delete_skipped_count: 57658013, key_skipped_count: 547864360, block: {cache_hit_count: 333684, read_count: 29, read_byte: 1.34 MB, read_time: 822.3µs}}}   | offset:0, count:5000                      | N/A     | N/A  |
|   │ └─IndexRangeScan_11          | 6250.00 | 6752    | cop[tikv] | table:tblCorrectRecordScancode, index:idx_create_time(create_time) | tikv_task:{proc max:1.94s, min:0s, avg: 456.4ms, p80:618ms, p95:793ms, iters:363, tasks:343}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | range:[-inf,1668287229), keep order:false | N/A     | N/A  |
|   └─TableRowIDScan_12(Probe)     | 5000.00 | 5000    | cop[tikv] | table:tblCorrectRecordScancode                                     | time:217.2ms, loops:9, cop_task: {num: 95, max: 61.1ms, min: 1.02ms, avg: 4.96ms, p95: 11ms, max_proc_keys: 127, p95_proc_keys: 92, tot_proc: 145ms, rpc_num: 95, rpc_time: 469.7ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:4ms, min:0s, avg: 1.53ms, p80:2ms, p95:3ms, iters:179, tasks:95}, scan_detail: {total_process_keys: 5000, total_process_keys_size: 22088846, total_keys: 5538, get_snapshot_time: 1.86ms, rocksdb: {delete_skipped_count: 297, key_skipped_count: 1160, block: {cache_hit_count: 54287, read_count: 13, read_byte: 526.3 KB, read_time: 405.3µs}}}                                              | keep order:false                          | N/A     | N/A  |
+----------------------------------+---------+---------+-----------+--------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+------+

3、max_execution_time 这个参数我问的表述有误，我的理解是sql 执行超过5s 会被终止执行，返回报错 interrupted ？这个delete 没有被终止是因为前面indexLookUP 这里的等待不算在执行时间里？

从执行计划看出，应该慢在了这个cop_task 上了，然后再怎么优化？遇到技术瓶颈了😂
cop_task: {num: 343, max: 1.95s, min: 712.6µs, avg: 460.7ms, p95: 821.8ms, max_proc_keys: 3040, p95_proc_keys: 0, tot_proc: 2m37.2s, tot_wait: 24ms, rpc_num: 343, rpc_time: 2m38s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}

是我的海 · 2023 年4 月 12 日 06:25

你在怀疑tidb 的实力么

dbaspace · 2023 年4 月 12 日 06:29

记住鲁迅语录中说：疑并不是缺点。总是疑，而并不下断语，这才是缺点

xfworld · 2023 年4 月 12 日 07:16

通过时间来做分区，是不是更合适你现在的场景？

删除的时候，只用考虑在某个分区内的数据，不用delete ，直接 truncate 就够了

@dbaspace 已经指出了你的问题， key太多拉

是我的海 · 2023 年4 月 12 日 07:18

tidb 分区表限制太多了，再说业务都跑了这么久你不能让人家改造表啊。
还有就是为什么之前好好的，一升级就这样了。除了那个keys 多的问题，还有其他的优化空间吗？

xfworld · 2023 年4 月 12 日 07:25

适当的优化 GC，或者增加手动的 compaction 可以减少版本留存的问题

自动的参考：
https://docs.pingcap.com/zh/tidb/stable/garbage-collection-configuration#gc-in-compaction-filter-机制

手动的参考：
https://docs.pingcap.com/zh/tidb/stable/tikv-control#手动-compact-单个-tikv-的数据

看看哪种更适合你的场景吧，数据的版本减少后，key 也会下降，扫描速度上升自然问题就缓解了

system · 2023 年6 月 11 日 07:25

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。