Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 使用tidb_mem_quota_query参数控制SQL内存使用不生效
[TiDB Usage Environment] Production Environment
[TiDB Version] 6.1
[Reproduction Path] Execute SQL directly from the TiDB terminal
[Encountered Problem: Phenomenon and Impact] The mem_quota_query parameter is not effective, causing SQL statements to use up memory and trigger OOM, leading to frequent killing of the TiDB service
[Resource Configuration] 32C64G
[Attachments: Screenshots/Logs/Monitoring]
You set the wrong value, 6442450944 is 6G, increase it a bit more.
Yes, my goal is to limit the maximum memory for a single SQL to 6GB.
My goal is to kill these larger statements when they exceed 6G to prevent using up the server’s memory and causing the entire service to be killed.
Did you configure tidb_mem_oom_action as CANCEL?
Yes,
tidb_mem_oom_action is set to CANCEL
The parameter oom-use-tmp-storage is also set to false, disabling temporary storage.
It seems that limiting the memory for a single SQL in version 6.1 is not very effective. Starting from version 6.4, the tidb_server_memory_limit which limits the entire TiDB memory handles the OOM issue better.
Our version is too old. I saw in the official documentation that this parameter can be used for control.
Let’s first look at the slow queries and see if we can optimize those SQL statements that consume a lot of memory.
In version 6.1, the explain command also does not show memory consumption
Doesn’t it crash after running for 10 minutes? Before it crashes, use EXPLAIN FOR CONNECTION to check, and send the result as text.
Well, I’ll try to see if I can capture it. When manually reproducing, it will kill the entire instance.
I have the same problem, setting that parameter doesn’t work.
My version is lower and doesn’t have this parameter.
Before version 6.1, this parameter was at the session level and needed to be manually set before writing SQL.
Take a look at this to see if it gives you any ideas.
I found that version 5.4 also has invalidation issues.
Monitor the statements yourself and terminate them when they reach the limit. If the memory is exhausted, just restart directly.
Mine also has a failure situation.
Check if there are issues with multi-statement concurrency; it may not necessarily be parameter failure.
|
username: TiDBer_E1B2Y6aN
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: