Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 由于超出单个SQL查询所允许的内存限制，查询已被取消。请尝试缩小查询范围或增加tidb_mem_quota_query限制，然后再试一次。[conn=3607482257570389131]
Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again. [conn=3607482257570389131]
I set SET GLOBAL tidb_mem_quota_query = 14G but it still doesn’t work. How should I troubleshoot this specifically?
What are you troubleshooting? With such a large memory, it should first consider optimizing the SQL.
You can reduce the query scope.
Check the SQL usage during statement execution.
Are you trying to find out what this SQL is?
Look for expensive queries, and then see if conn=3607482257570389131 matches.
This number is the conn_id in the log.
This SQL was working fine before, but suddenly it stopped working. It doesn’t seem to be an issue with the SQL itself.
It might still be an SQL issue. Post the execution plan and let’s take a look.
You can find the execution plan of this failed SQL in the slow query log.
First, take a look at this SQL execution plan.
An increase in the data in the table or inaccurate statistics could both lead to this issue. Identify the SQL and then check the execution plan to understand it.
Even with such a large setting, is there no OOM (Out of Memory)?
Increasing memory can temporarily solve the problem. However, you may frequently encounter similar issues later on. It is recommended to analyze the SQL and see if it can be optimized.
There are slow queries in the console, right? If it doesn’t work, get a TiDB with large memory specifically to execute this statement.
It is also possible that the data has changed. Previously, it only returned 10 rows, but now it returns 1 billion rows.
Just post the SQL execution plan… 14GB of memory is not enough.
If the SQL hasn’t changed, then it must be the data itself that has changed, and the data has increased.
Let the business modify the query range. 14G already causes OOM, increasing it further might affect the stable operation of the database.
This should optimize the SQL statement, you can’t use memory without limits.
|
username: TiDBer_hUfk8uMB
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: