Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 删除大量数据时报错"ERROR 2013 (HY000): Lost connection to MySQL server during query"
[Test Environment] TiDB
[TiDB Version] 7.5
[Reproduction Path] After connecting to TiDB through the MySQL client, executing a delete to remove a large amount of data results in the error “ERROR 2013 (HY000): Lost connection to MySQL server during query” after some time. What could be the reason?
The official best practice for deletion is generally DELETE LIMIT 1000.
The amount of data to be deleted is relatively large and exceeds TiDB’s memory limit: tidb_mem_quota_query.
Is there a quick way to delete data? Deleting large amounts of data with limit is very slow.
Query:
show variables like ‘%quota%’;
tidb_mem_quota_query====> The size of the cache occupied by each SQL
show variables like ‘%oom%’;
tidb_mem_oom_action | CANCEL | or LOG ===> Directly recorded in the log file
tidb_mem_oom_action====> If the cache size occupied by each SQL exceeds the tidb_mem_quota_query value,
and temporary disk is enabled, and the temporary disk has no space, this parameter controls whether to cancel or continue using
Enable temporary disk:
show variables like ‘tidb_enable_tmp_storage_on_oom’;
| tidb_enable_tmp_storage_on_oom | ON |
show config where name like ‘%tmp-storage-path%’;
tmp-storage-path
show config where name like ‘%tmp-storage-quota%’;
tmp-storage-quota
First, try changing it to 8G:
set global tidb_mem_quota_query=8589934592;
No matter how large the memory is, there is always a risk of it crashing. It is recommended to control the data volume, delete in batches, control the transaction size, and reduce memory usage.
It is recommended to add a limit when performing update and delete operations on large amounts of data, and also to set the memory threshold for OOM.
You can batch delete excessive data by retaining the data and batching it to a new table -----> drop the old table ----------> then rename the new table. You can create a task through DataX to execute it periodically. We have a similar solution in production that you can refer to. Another method mentioned above is using the limit method. Also, consider the GC time and the tidb_mem_quota_query setting, depending on the actual hardware situation.
This is caused by the transaction being too large and taking too long. Change it to smaller transactions.
The service is down, right?
Try modifying the TiDB global parameter wait_timeout to increase the timeout duration.
Here is a script that can delete data on a large scale:
date1=$(date -d "1 day ago" +"%Y-%m-%d")
echo $date1
delete_db_sql="delete from xxxx a where a.xxxx < '2023-01-01 00:00:00' and a.xxxx = '2' limit 10000;"
echo $delete_db_sql
i=0
while ((++i)); do
a=$(/bin/mysql -uroot -pxxxx -A soa_vehicle -hx.x.x.x -P 4000 --comments -e "${delete_db_sql}" -vvv | grep "Query OK" | awk '{print $3}')
echo $a
if (($a < 1)); then
break 1
fi
sleep 1
printf "%-4d" $((i))
done
In addition to the professional suggestions mentioned above, if the amount of data to be deleted accounts for the majority of the table and the business can accept it, you can choose to create a new empty table, migrate the data that needs to be retained to the new table, then drop the old table, and finally rename the new table to the old table’s name.
No one deletes a large amount of data like this using delete; it’s usually done in batches with a script.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: terry0219
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: