The memory limit for a single SQL execution is set to 2G, but a colleague's SQL query without a limit is using 18G of memory. Why is SET tidb_mem_quota_query = 2 << 30; not taking effect?

Translated
1

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

Original topic: 单条SQL执行限制内存2G，同事写SQL查询没有limit 这条SQL执行占用18G的内存,为啥SET tidb_mem_quota_query = 2 << 30；没有生效

| username: xiaoxiaozuofang

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Problem Description and Impact】
【Resource Configuration】

Image
Image1380×130 13.4 KB

【Attachments: Screenshots/Logs/Monitoring】

2
| username: 裤衩儿飞上天 | Original post link

What is the value of tidb_mem_oom_action?

3
| username: xiaoxiaozuofang | Original post link

tidb_mem_oom_action

4
| username: zhanggame1 | Original post link

You are controlling it at the session level, right? You need to execute the set command and then execute the SQL in the same window using the command line.

5
| username: xiaoxiaozuofang | Original post link

The system variable tidb_mem_quota_query limits the memory usage of a single SQL query to 1GB.

6
| username: buddyyuan | Original post link

Turn off this parameter.

7
| username: TiDBer_2i1SqvUB | Original post link

Did it take effect in the end?

8
| username: 人如其名 | Original post link

Your scenario:

  1. With the default setting of tidb_distsql_scan_concurrency=15, up to 15 regions can be cached in extreme cases.
  2. Fields like decimal and tinyint have memory amplification issues, especially decimal (40 bytes). If there are many such fields, it may lead to excessive memory usage.
  3. With tidb_enable_rate_limit_action=ON, rate limiting is enabled by default in this version, which prioritizes rate limiting and causes TiDB to cache too much data without timely triggering cancel or other OOM actions.
  4. Assuming your pushsubscribe is a view containing union all, it will be amplified, with a default maximum of 5 times (affected by the tidb_executor_concurrency parameter), i.e., 5 * 15 regions.
  5. If the frontend has cursor fetch enabled, it may lead to slower reads, causing more data backlog on the TiDB side. If the frontend fetches data faster, the backend is less likely to have data backlog. Therefore, using the official recommended streaming method is better.

Optimization methods, set at the session level:

  1. set tidb_distsql_scan_concurrency=2; Cache fewer region data while maintaining performance.
  2. set tidb_enable_rate_limit_action=OFF; Ensure memory does not exceed tidb_mem_quota_query, reducing the risk of TiDB-server OOM.
  3. Check regions to see if there are large regions, and try to perform splitRegion operations.
  4. set tidb_enable_chunk_rpc=OFF; Disable chunk_rpc to reduce memory amplification issues.

It is more recommended to upgrade to version 6.5, which uses paging instead of returning the entire region’s data, reducing the risk of statement OOM.
In version 6.5, paging is enabled by default:

mysql> show variables like '%paging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| tidb_enable_paging   | ON    |
| tidb_max_paging_size | 50000 |
| tidb_min_paging_size | 128   |
+----------------------+-------+
3 rows in set (0.00 sec)
9
| username: ljluestc | Original post link

The statement SET tidb_mem_quota_query = 2 << 30; is used to set the memory quota for a single SQL query execution in TiDB. This statement specifies the maximum amount of memory that a query can use.

However, it is important to note that the tidb_mem_quota_query configuration only applies to subsequent SQL statements executed after setting this configuration. It does not retroactively limit the memory usage of previously executed SQL statements.

If your colleague has already executed SQL queries without memory limits, those queries may have consumed more memory than the specified quota. The memory usage of these queries cannot be retroactively limited.

To enforce memory limits on SQL query execution, you need to ensure that the tidb_mem_quota_query configuration is set before executing the queries. Additionally, it is important to consider the overall resource usage and allocation in the TiDB cluster to prevent excessive memory consumption and optimize performance.

If you want to ensure that all SQL queries are subject to memory limits, you can globally set the tidb_mem_quota_query configuration in the TiDB configuration file (tidb.toml). By doing so, the memory limit will apply to all SQL queries executed in the TiDB cluster.

Remember to restart the TiDB service after modifying the configuration file for the new settings to take effect.

If you encounter any issues or if the memory limits are still not enforced as expected, it is recommended to check the TiDB logs and consult the TiDB community or support channels for further assistance.

10
| username: redgame | Original post link

Why didn’t I see the final answer…

11
| username: cy6301567 | Original post link

This is complete