Multi-threaded Data Deletion or Update in TiDB

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

Original topic: tidb多线程删除或者更新数据

| username: TiDBer_微风轻吟

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] v7.5.1
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
Deployed using tiup on three 32G machines


The situation is roughly as shown above. Now, if multiple threads simultaneously perform updates or deletions (approximately 2 million records), it leads to the situation shown above. How can this issue be resolved?

| username: onlyacat | Original post link

This is useless.
Check the dashboard/Grafana to see if it is OOM.

| username: zhaokede | Original post link

It must be an OOM (Out of Memory) issue;
For this multi-threaded data deletion, you need to look at your business code’s delete SQL. Are there any large transactions? Are there any deadlocks? How many connections are there?

| username: TiDBer_QYr0vohO | Original post link

It is estimated to be OOM. Check the system logs and monitoring.

| username: 濱崎悟空 | Original post link

Check the situation in the monitoring.

| username: FutureDB | Original post link

It is estimated to be an OOM issue. You can check the memory usage in the actual execution plan of the SQL. Additionally, it is not recommended to deploy TiDB, TiKV, and TiFlash together. It is best to deploy storage and compute nodes separately.

| username: zhanggame1 | Original post link

Encountered OOM. Set memory limit parameters for TiDB and TiKV in a mixed deployment configuration, otherwise OOM is inevitable.

| username: TiDBer_微风轻吟 | Original post link

Yes, it indeed ran out of memory (OOM). Could you please tell me where to configure that parameter? I’ve been looking through the documentation for a long time. :sob:

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

Here you go: 混合部署拓扑 | PingCAP 文档中心

Here.

| username: zhanggame1 | Original post link

The first memory limit for the TiDB component: SET GLOBAL tidb_server_memory_limit = “20GB”;
The second memory limit for the TiKV component: SET config tikv storage.block-cache.capacity='GiB’. You can also modify the configuration file. Note that this value multiplied by 1.5 is approximately the actual memory usage.

| username: TiDBer_微风轻吟 | Original post link

Thank you.

| username: TiDBer_微风轻吟 | Original post link

Thank you.

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

  1. Use dmesg | tail -10 to check for OOM (Out of Memory) issues.
  2. Check if the update or delete SQL statements can be optimized to use indexes or primary keys.
| username: 友利奈绪 | Original post link

Is the OOM caused by a lack of configuration?

| username: TiDBer_RjzUpGDL | Original post link

Out of Memory (OOM)

| username: 鱼跃龙门 | Original post link

The system ran out of memory (OOM). Try setting the parameters tidb_server_memory_limit and storage.block-cache.capacity.