Deleting data transaction is too large, size: 104857608

Translated
1

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

Original topic: 删除数据transaction is too large,size:104857608

| username: 点点-求助来了

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] /
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring] Deleting data transaction is too large, size: 104857608

2
| username: 有猫万事足 | Original post link
3
| username: TIDB-Learner | Original post link

Transactions cannot be too large; there are limitations. If the data to be deleted is too large, perform the operation in batches.

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

You can use a for loop to execute each delete limit 10000 in the background.

5
| username: TIDB-Learner | Original post link

The image you provided is not visible. Please provide the text you need translated.

6
| username: porpoiselxj | Original post link

BATCH | PingCAP 文档中心 Automatic batch processing

7
| username: 迪迦奥特曼 | Original post link

Solution:

  1. Adjust the txn-total-size-limit parameter
  2. Split into smaller transactions and process in batches
8
| username: zhanggame1 | Original post link

It’s best to add a WHERE clause to the DELETE statement and split it into multiple statements executed by primary key.

9
| username: dba远航 | Original post link

Splitting large transactions into smaller ones is part of database optimization. It also prevents large transactions from causing the database to hang and block other transactions.

10
| username: 随缘天空 | Original post link

The delete operation has exceeded the default maximum value for transactions.

11
| username: 小龙虾爱大龙虾 | Original post link

Deleting like this will get slower and slower, it’s better to do it in batches.

12
| username: yytest | Original post link
  1. Adjust Transaction Size Limit: You can increase the maximum transaction size allowed by adjusting the txn-total-size-limit configuration item. This can be set in the TiDB configuration file. However, before making such adjustments, ensure you understand the potential risks and conduct appropriate performance evaluations.
  2. Split Transactions: If possible, try to split large transactions into multiple smaller ones. This can help avoid triggering the size limit for a single transaction.
  3. Optimize Data Model: Consider whether you can optimize your data model to reduce the amount of data processed in a single operation. For example, by processing in batches or using batch operation interfaces.
  4. Check for Unnecessary Operations: Sometimes, transactions may include unnecessary operations, such as redundant writes or reads. Checking and optimizing the operations within the transaction may reduce its size.
  5. Follow TiDB Best Practices: Adhere to TiDB best practices during development, such as avoiding full table scans within transactions.
13
| username: 像风一样的男子 | Original post link

I also have a table with 4 billion records and need to delete 3 billion of them. The deletion process gets slower and slower with each iteration, which is not very feasible.

14
| username: zhang_2023 | Original post link

Splitting transactions

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

BATCH ON test.t2.id LIMIT 1 delete FROM t2;
Supported in version 7. Still using my version 5.

16
| username: Hacker007 | Original post link

If it’s not real-time data writing, create a new table to write the valid data, then delete the old table, rename the new one, and it will be faster.

17
| username: forever | Original post link

Break it into smaller transactions and delete in a loop.

18
| username: TiDBer_RjzUpGDL | Original post link

Batch deletion

19
| username: TiDBer_HUfcQIJx | Original post link

Process in batches.

20
| username: zhaokede | Original post link

Split the transactions, commit around 5000 each time.