How to Limit the Maximum Memory Usage of a Single SQL Query

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

Original topic: 如何限制单条sql使用的最大内存

| username: SummerGu

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.5.0

[Encountered Problem: Problem Phenomenon and Impact] Hello, I would like to ask, currently when we run SQL, it causes memory OOM. How can we limit the memory usage of a single SQL or limit the number of concurrent queries for SQLs that exceed a certain amount of memory?

| username: xfworld | Original post link

It may be more reasonable to limit resource usage by user.

Refer to:


There are also dedicated practice posts in the community, you can search for them yourself.

| username: Jolyne | Original post link

You can check this document for tidb_mem_quota_query:

| username: Kongdom | Original post link

Is this what you’re looking for?

| username: WinterLiu | Original post link

The person above is correct.

| username: 小龙虾爱大龙虾 | Original post link

Limiting SQL memory usage is one aspect, but the main issue is that problematic SQL queries can crash the TiDB Server process. To truly solve the problem, SQL optimization is necessary.

| username: 小毛毛虫 | Original post link

The tidb_mem_quota_query controls the total memory size for the entire session (including the total memory usage of all SQL statements in the session). If it exceeds the limit, it will trigger the action specified by the tidb_mem_oom_action parameter.

| username: 随缘天空 | Original post link

The above sets the memory for a single SQL to 4G. You can adjust the value according to your own situation.

| username: FutureDB | Original post link

To set the maximum memory usage for a single SQL query to 1GB, you can use the following two methods:

Method 1: set session tidb_mem_quota_query=1 << 30; (session level, can also be set globally by changing session to global)

Method 2: select /*+ MEMORY_QUOTA(1024) */ from test;

| username: 哈喽沃德 | Original post link

SET SESSION tidb_mem_quota_query = <memory_limit>;

| username: YuchongXU | Original post link

tidb_mem_quota_query

| username: andone | Original post link

tidb_mem_quota_query

| username: 这里介绍不了我 | Original post link

tidb_mem_quota_query

| username: Jellybean | Original post link

The original poster asked about limiting the memory of a single SQL query. If this is the only issue, then the parameter tidb_mem_quota_query mentioned by other experts can limit the maximum memory usage of a single SQL query. When the limit is exceeded, the SQL query will be terminated directly and an error message will be returned.

However, based on the original poster’s description, the actual need is to solve the OOM (Out of Memory) problem. Therefore, the goal is to solve or alleviate the OOM situation. The original poster can take the following measures:

  1. On the database side:
  • Configure oom-action as cancel on the database server.
  • Limit the memory usage of the entire tidb-server.
  • Enable the feature to spill to disk when large SQL queries exceed memory usage and set the relevant disk directory.
  1. On the application side:
  • Work with developers to optimize SQL queries, and try to avoid reading large amounts of data that occupy too much memory.
  • Control concurrency.

By following these approaches and implementing them, the original poster’s problem should be greatly alleviated.

| username: dba远航 | Original post link

tidb_mem_quota_query

| username: 哈喽沃德 | Original post link

If it’s resolved, remember to mark the correct answer.

| username: 不想干活 | Original post link

Use the system variable tidb_mem_quota_query to configure the memory usage threshold during the execution of an SQL query, with the unit being bytes.

| username: zhang_2023 | Original post link

tidb_mem_quota_query

| username: oceanzhang | Original post link

Is the entire database killed or just this connection killed when an OOM occurs?

| username: 随便改个用户名 | Original post link

Study check-in :melting_face: