Is the memory usage threshold setting for a single SQL query ineffective?

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

Original topic: 单条 SQL 的内存使用阈值设置无效?

| username: 海石花47

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1.1
[Encountered Issue]



  1. The memory setting for a single SQL is 1G (default setting), but the actual memory usage of the SQL found in information_schema.processlist is 3.3G, which is very confusing. No matter how I adjust the limit number, the memory usage is always 3.3G.
  2. Additionally, from the logs, it appears that a thread of an operator is triggered and stopped periodically. Whether the SQL can complete execution depends on the race between the operation time and the operator stopping pace.

Subsequently, I adjusted tidb_distsql_scan_concurrency to 12 (previously set to 24), and then when running the SQL again, the memory usage dropped by half to 1.6G.

The official documentation describes that the tidb_mem_quota_query parameter controls the memory usage of a single SQL, but it doesn’t mention its relation to tidb_distsql_scan_concurrency (scan concurrency). So how exactly does this tidb_mem_quota_query = 1G limit work?


| username: xfworld | Original post link

You are focusing on the wrong area. Take a good look at your own screenshot; I marked it for you.

The limitations are twofold: one is execution time, and the other is memory usage. Once the limit is reached, you can choose to cancel or continue.

| username: 海石花47 | Original post link

I understand the actions of cancel and log, and I also know that after the SQL memory exceeds this threshold, the scan threads will be gradually shut down until the last thread, and if it still exceeds the memory, the cancel/log actions will be performed. What I mean is, is this parameter merely a threshold setting and not an actual memory limit?

| username: xfworld | Original post link

As mentioned above, it’s just a threshold setting…

It’s not a hard limit…

| username: 海石花47 | Original post link

:joy: I see… That makes it clear.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.