How enable disk spill?

TiDB
, ,
1

Application environment:

Production, Self Deployed

TiDB version:

7.5.0

Problem:

Hi,

I have a big query which returns about 96000 records. But I get OOM error. I want to enable disk spill to increase memory but can not enable it . Let me know please how enable it ?

This is query explain . As you can see Disk Spill is not enabled but I already enabled it with running:

SET tidb_executor_concurrency = 1;

explain
explain1920×1708 299 KB

but disk still in NA. Let me know how I can enable disk spill for this query?

Thanks

2

Hi,

To enable disk spill in TiDB, you need to configure the system variable tidb_executor_concurrency to a value of 1. However, please note that disk spill for the HashAgg operator is not triggered by default. You can refer to the official documentation for more details on this .

In your case, it seems that you have already set tidb_executor_concurrency = 1, but the disk spill is still not enabled for your query. There could be a few reasons for this:

  1. The query may not be using the operators that support disk spill, such as Sort, MergeJoin, HashJoin, or HashAgg. You can check the execution plan of your query to see if any of these operators are present.

  2. The memory usage of your query may not exceed the memory quota. Disk spill is triggered when the memory usage exceeds the memory quota. You can check the memory quota configuration (tidb_mem_quota_query) and adjust it if necessary.

  3. There may be other factors affecting the disk spill behavior, such as the tidb_enable_tmp_storage_on_oom, tmp-storage-path, and tmp-storage-quota parameters. Make sure these parameters are properly configured.

If you have already checked these factors and the disk spill is still not enabled for your query, please provide more details about your query and the configuration settings you have applied. This will help me further investigate the issue and provide you with a more accurate solution.

Best regards.