Deleting Over 80,000 Records, Slower and Slower

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 删除8万多条数据,越删越慢

| username: 大飞飞呀

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed to cause the issue
Repeatedly execute the following statement:
delete from table where I_DATE = 20230601 limit 1000, a total of more than 80,000 rows need to be deleted, the total data volume of the table is about 70 million
【Encountered Issue: Problem Phenomenon and Impact】
Initially very fast, gradually getting slower, later each deletion takes more than 2 seconds
{total_process_keys: 1000, total_keys: 680001, rocksdb: {delete_skipped_count: 2000, key_skipped_count: 597000
Execution Plan

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

【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

| username: zhanggame1 | Original post link

Is there an index on the data column? Check the execution plan for the query SELECT * FROM table WHERE date=20230601 LIMIT 1000.

| username: 大飞飞呀 | Original post link

The execution plan is as follows:

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
| username: 大飞飞呀 | Original post link

With indexes.

| username: Billmay表妹 | Original post link

The issue arises because deleting a large amount of data at once slows down the delete operation. In TiDB, if a large amount of data is deleted at once, the delete operation slows down because each delete operation traverses from the front to the back. After the front part is deleted, a considerable number of delete markers remain for a short period (which will be GC’d later), affecting subsequent Delete statements. Therefore, it is recommended to break large chunks of data into smaller chunks for deletion to avoid the impact of the previous Delete statements on the subsequent ones.

To address this issue, you can try the following two methods:

  1. Reduce the amount of data deleted at once, for example, by setting the limit parameter to a smaller value, such as 100 or 50. This can reduce the amount of data deleted each time and avoid slowing down the delete operation.

  2. Break large chunks of data into smaller chunks for deletion, for example, by splitting the data to be deleted into multiple smaller chunks based on time range, and deleting one chunk at a time. You can use pseudo-code similar to the following:

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()

The above pseudo-code splits the data to be deleted into 24 smaller chunks based on the hour, deleting one hour’s worth of data at a time, with each deletion involving 1000 rows. This can avoid deleting too much data at once, thereby improving the efficiency of the delete operation.

Additionally, it is recommended to avoid using delete * when deleting data. Instead, use appropriate conditions based on business requirements to filter the data, thereby reducing the amount of data to be deleted.

| username: 考试没答案 | Original post link

Please share the table structure.

| username: 考试没答案 | Original post link

What is the type of the date column? Why use keywords as table fields?

| username: 有猫万事足 | Original post link

This is an old bug.
Upgrade the version.

| username: 大飞飞呀 | Original post link

The deletion was done with a limit of 1000, so the issue is not with that.

| username: 大飞飞呀 | Original post link

It shouldn’t be this issue because we have already fixed this bug by setting gc.enable-compaction-filter: false.

| username: 大飞飞呀 | Original post link

The date field is of int type, and it stores dates in int format, such as 20230617.

| username: 裤衩儿飞上天 | Original post link

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

    Perform a table analysis

The index does not include the date column, the index usage is inaccurate

| username: 大飞飞呀 | Original post link

The query is on the I_DATE field, which can be understood as I_DATE being the same as DATE.

During the deletion process, if you execute analyze, the analyze itself will also take more time.

| username: tidb菜鸟一只 | Original post link

It still feels like a GC issue, with too many keys that have already been deleted being scanned.

| username: 有猫万事足 | Original post link

In the changelog, this issue was indeed resolved in version 5.0.5.

And this issue, the first sentence describes it as a problem with the GC worker.

“The GC in Compaction Filter mechanism performs GC during the RocksDB compaction process based on the distributed GC mode (DISTRIBUTED GC mode), instead of using a separate GC worker thread.”

Setting this parameter to false, according to the documentation, disables this mechanism. That is, you have enabled a separate GC worker.

| username: 大飞飞呀 | Original post link

A total of only over 80,000 rows of data were deleted, total_keys: 680001. How can there be 680,000?

| username: redgame | Original post link

Inaccurate statistics

| username: 大飞飞呀 | Original post link

In theory, if it’s not accurate, it won’t use the index, but it seems like it’s still using the index.

| username: 大飞飞呀 | Original post link

The total data volume of the table is 70 million, and deleting only 80,000 rows should not cause the statistics to be inaccurate.

| username: tidb狂热爱好者 | Original post link

Such an old version