删除8万多条数据，越删越慢

大飞飞呀 · 2023 年7 月 31 日 07:58

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
循环执行下面语句
delete from table where I_DATE =20230601 limit 1000，总共需要删除8万多条，表的总数据量在7000万
【遇到的问题：问题现象及影响】
一开始非常快，慢慢越来越慢，后面一次删除需要2秒多
{total_process_keys: 1000, total_keys: 680001, rocksdb: {delete_skipped_count: 2000, key_skipped_count: 597000
执行计划

2023-07-31, 2.31, 0,  id                      task      estRows operator info                                                                                                                                                                       actRows execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    memory  disk
  Delete_5                root      0       N/A                                                                                                                                                                                 0       time:2.28s, loops:1, commit_txn: {prewrite:30.9ms, get_commit_ts:135.2µs, commit:6.97ms, region_num:16, write_keys:5000, write_byte:208404}                                                                                                                                                                                                                                                                                                                                                       1.19 MB N/A
  └─IndexLookUp_19        root      4.00    limit embedded(offset:0, count:1000)                                                                                                                                                1000    time:2.26s, loops:2, index_task: {total_time: 2.21s, fetch_handle: 2.21s, build: 855ns, wait: 2.25µs}, table_task: {total_time: 17.7s, num: 1, concurrency: 8}                                                                                                                                                                                                                                                                                                                                    1.10 MB N/A
    ├─Limit_18            cop[tikv] 4.00    offset:0, count:1000                                                                                                                                                                1000    time:2.21s, loops:1, cop_task: {num: 1, max: 1.14s, proc_keys: 1000, tot_proc: 1.11s, tot_wait: 22ms, rpc_num: 2, rpc_time: 2.21s, copr_cache_hit_ratio: 0.00}, ResolveLock:{num_rpc:1, total_time:1.12ms}, tikv_task:{time:1.08s, loops:6}, scan_detail: {total_process_keys: 1000, total_keys: 680001, rocksdb: {delete_skipped_count: 2000, key_skipped_count: 597000, block: {cache_hit_count: 722, read_count: 7, read_byte: 84.1 KB}}}                                                      N/A     N/A
    │ └─IndexRangeScan_16 cop[tikv] 4.00    table:table, index:UNIQ_DT(I_DATE, a_ID, I_MON_TYPE, b_ID), range:[20230622,20230622], keep order:false  1000    tikv_task:{time:1.08s, loops:6}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                                    N/A     N/A
    └─TableRowIDScan_17   cop[tikv] 4.00    table:table, keep order:false, stats:pseudo                                                                                                          1000    time:46.5ms, loops:2, cop_task: {num: 3, max: 45.3ms, min: 2.21ms, avg: 16.8ms, p95: 45.3ms, max_proc_keys: 902, p95_proc_keys: 902, tot_proc: 42ms, tot_wait: 1ms, rpc_num: 3, rpc_time: 50.1ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:36ms, min:2ms, p80:36ms, p95:36ms, iters:9, tasks:3}, scan_detail: {total_process_keys: 1000, total_keys: 1010, rocksdb: {delete_skipped_count: 0, key_skipped_count: 30, block: {cache_hit_count: 12146, read_count: 3, read_byte: 39.9 KB}}} N/A     N/A, DELETE FROM table            WHERE I_DATE = ? LIMIT 1000  [arguments: 20230622];, 3a0a4c969b2b6d559ca72148d0432c8e486e9f805f41fc012c6e23b98369965e

【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

zhanggame1 · 2023 年7 月 31 日 08:03

data列上有索引吗，查询下selete * from table where date=20230601 limit 1000 看看执行计划

大飞飞呀 · 2023 年7 月 31 日 08:09

执行计划如下：

2023-07-31, 2.31, 0,  id                      task      estRows operator info                                                                                                                                                                       actRows execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    memory  disk
  Delete_5                root      0       N/A                                                                                                                                                                                 0       time:2.28s, loops:1, commit_txn: {prewrite:30.9ms, get_commit_ts:135.2µs, commit:6.97ms, region_num:16, write_keys:5000, write_byte:208404}                                                                                                                                                                                                                                                                                                                                                       1.19 MB N/A
  └─IndexLookUp_19        root      4.00    limit embedded(offset:0, count:1000)                                                                                                                                                1000    time:2.26s, loops:2, index_task: {total_time: 2.21s, fetch_handle: 2.21s, build: 855ns, wait: 2.25µs}, table_task: {total_time: 17.7s, num: 1, concurrency: 8}                                                                                                                                                                                                                                                                                                                                    1.10 MB N/A
    ├─Limit_18            cop[tikv] 4.00    offset:0, count:1000                                                                                                                                                                1000    time:2.21s, loops:1, cop_task: {num: 1, max: 1.14s, proc_keys: 1000, tot_proc: 1.11s, tot_wait: 22ms, rpc_num: 2, rpc_time: 2.21s, copr_cache_hit_ratio: 0.00}, ResolveLock:{num_rpc:1, total_time:1.12ms}, tikv_task:{time:1.08s, loops:6}, scan_detail: {total_process_keys: 1000, total_keys: 680001, rocksdb: {delete_skipped_count: 2000, key_skipped_count: 597000, block: {cache_hit_count: 722, read_count: 7, read_byte: 84.1 KB}}}                                                      N/A     N/A
    │ └─IndexRangeScan_16 cop[tikv] 4.00    table:table, index:UNIQ_DT_DID_MONTH_TP_BKR(I_DATE, a_ID, I_MONTH_ON_MONTH_TYPE, b_ID), range:[20230622,20230622], keep order:false  1000    tikv_task:{time:1.08s, loops:6}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                                    N/A     N/A
    └─TableRowIDScan_17   cop[tikv] 4.00    table:table, keep order:false, stats:pseudo                                                                                                          1000    time:46.5ms, loops:2, cop_task: {num: 3, max: 45.3ms, min: 2.21ms, avg: 16.8ms, p95: 45.3ms, max_proc_keys: 902, p95_proc_keys: 902, tot_proc: 42ms, tot_wait: 1ms, rpc_num: 3, rpc_time: 50.1ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:36ms, min:2ms, p80:36ms, p95:36ms, iters:9, tasks:3}, scan_detail: {total_process_keys: 1000, total_keys: 1010, rocksdb: {delete_skipped_count: 0, key_skipped_count: 30, block: {cache_hit_count: 12146, read_count: 3, read_byte: 39.9 KB}}} N/A     N/A, DELETE FROM table            WHERE I_DATE = ? LIMIT 1000  [arguments: 20230622];, 3a0a4c969b2b6d559ca72148d0432c8e486e9f805f41fc012c6e23b98369965e

大飞飞呀 · 2023 年7 月 31 日 08:11

有索引的

Billmay表妹 · 2023 年7 月 31 日 08:12

这个问题是由于删除数据时，每次删除的数据量过大，导致删除操作变慢。在 TiDB 中，删除数据时，如果一次删除的数据量非常大，会导致删除操作变慢，因为每次删除都是从前向后遍历，前面的删除之后，短时间内会残留不少删除标记(后续会被 GC 掉)，影响后面的 Delete 语句。因此，建议把大块的数据拆成小块删除，以避免删除过程中前面的 Delete 语句影响后面的 Delete 语句。

针对这个问题，可以尝试以下两种方法：

将一次删除的数据量减小，例如将 limit 参数设置为更小的值，例如 100 或者 50。这样可以减小每次删除的数据量，避免删除操作变慢。
将大块的数据拆成小块删除，例如将需要删除的数据按照时间范围拆分成多个小块，每次删除一个小块。可以使用类似下面的伪代码：

for i from 0 to 23:
    while affected_rows > 0:
        delete * from t where date = 20230601 and hour = i limit 1000;
        affected_rows = select affected_rows()

上面的伪代码将需要删除的数据按照小时拆分成 24 个小块，每次删除一个小时的数据，每次删除的数据量为 1000 条。这样可以避免一次删除的数据量过大，从而提高删除操作的效率。

另外，建议在删除数据时，尽量避免使用 delete * 的方式，而是应该根据业务需求，使用合适的条件进行过滤，以减小删除的数据量。

考试没答案 · 2023 年7 月 31 日 08:12

表结构发下吧。

考试没答案 · 2023 年7 月 31 日 08:14

date 列的字段时什么类型的。为什么使用关键字当表的字段。

有猫万事足 · 2023 年7 月 31 日 08:14

历史久远的老bug。
升级版本吧。

大飞飞呀 · 2023 年7 月 31 日 08:15

本身就是limit 1000 来删除的，问题不在于此

大飞飞呀 · 2023 年7 月 31 日 08:17

应该也不是这个问题，因为我们已经修复了这个bug，采用的是设置 gc.enable-compaction-filter: false

大飞飞呀 · 2023 年7 月 31 日 08:18

date字段是int类型，里面存储的是int型的日期，例如20230617

裤衩儿飞上天 · 2023 年7 月 31 日 08:18

[quote=“大飞飞呀, post:4, topic:1010569”]
stats:pseudo
[/quote]

做个表分析

索引里并没有date列，索引用的不准确

大飞飞呀 · 2023 年7 月 31 日 08:20

查询的是I_DATE字段，可以理解为I_DATE 跟DATE是同一个，

正在删除过程中，如果执行analyze ，analuze本身也会花费更多的时间

tidb菜鸟一只 · 2023 年7 月 31 日 08:24

感觉还是gc的问题，扫到的已经删除掉的key太多了

有猫万事足 · 2023 年7 月 31 日 08:28

https://github.com/tikv/tikv/blob/42882f8bcf4ce52ea93f93d325c000147ac2e137/CHANGELOG.md?plain=1#L136

在changelog上这个确实是5.0.5才解决的。

而这个issue,第一句就描述了是GC worker的问题。

https://github.com/tikv/tikv/issues/11217

GC in Compaction Filter 机制是在分布式 GC 模式 (DISTRIBUTED GC mode) 的基础上，由 RocksDB 的 Compaction 过程来进行 GC，而不再使用一个单独的 GC worker 线程。

https://docs.pingcap.com/zh/tidb/v5.0/garbage-collection-configuration#gc-in-compaction-filter-机制

而把这个参数设置为false，从文档的描述来看是关闭了这个机制。即，你启用了一个单独的GC worker。

大飞飞呀 · 2023 年7 月 31 日 08:32

总共删除才8万多条数据， total_keys: 680001 怎么会有68万呢

redgame · 2023 年7 月 31 日 08:35

统计信息不准

大飞飞呀 · 2023 年7 月 31 日 08:37

理论上不准就是不走索引了，但是看着还是走了索引的

大飞飞呀 · 2023 年7 月 31 日 08:56

表的总数据量是7000万，只删除8万条数据，不应该导致统计信息不准确的

tidb狂热爱好者 · 2023 年7 月 31 日 09:12

这么老的版本