@Rocky Running StarRocks 4.1.1 in

shared_data

mode (S3 + CN). Hit an OOM on a large Iceberg scan (119M rows, 224 columns, SELECT * ORDER BY) that I'm trying to understand the best practices around. Setup: 3 CNs, 30 vCPU / 56Gi each (32 cores visible to process). enable_spill=true. With pipeline_dop=0 (auto), DOP resolves to 15 per CN. The TopN sort buffers across 15 drivers exhaust the query pool (~40 GB limit) and OOM, even though spill kicked in (13.7 GB flushed). Setting pipeline_dop=8 manually fixes it -- query finishes in 13s, 19 GB peak, zero spill needed. 1. What's the recommended pipeline_dop setting for ETL/heavy scan workloads? Is capping it (e.g. 8) the right approach, or is there a better way to handle this per-query vs globally? 2. query_pool_spill_mem_limit_threshold defaults to 1.0 -- meaning spill only triggers when the pool is already 100% full. In my case spill couldn't flush fast enough before the workgroup hard limit killed the query. Is lowering this to 0.5-0.7 a common practice? Any downsides?

For your StarRocks 4.1.1 environment on CN nodes, you've encountered a classic "race condition" between fast memory allocation during a wide-table scan and the relatively slower process of spilling to disk. 1. Recommended

pipeline_dop

for ETL/Heavy Scans When

pipeline_dop

is set to

0

(auto), StarRocks typically targets half the available cores (16 in your case, resolving to 15). While this is great for low-latency analytical queries, it is often too aggressive for "heavy" queries like a

SELECT *

on 224 columns with a full sort. * Recommendation: For heavy ETL or large-scale exports, manually capping

pipeline_dop

(e.g., to 1/4 or 1/8 of total cores) is the standard practice. In your case,

8

is a solid balance. * Per-Query vs. Global: It is highly recommended to handle this per-query or per-workload rather than globally. * Hint:

SELECT /_+ SET_VAR(pipeline_dop = 8) _/ ...

* Resource Groups: The most robust way in v4.x is to use Resource Groups. You can create a "heavy_load" group with a hard

concurrency_limit

and a lower

mem_limit

. StarRocks 4.0+ also allows you to bind specific

pipeline_dop

defaults to a resource group in some configurations (though session variables are more common). * Why it helps: Reducing DOP reduces the number of concurrent "drivers" or pipeline instances. For a

TopN

(Sort) operator, each driver maintains its own sort buffer. Lowering DOP linearly reduces the base memory footprint of the sort before it even considers spilling. 2.

query_pool_spill_mem_limit_threshold

Tuning The default of

1.0

means the system only looks to spill when the query pool is effectively saturated. In a high-throughput scan (like S3/Iceberg), your memory usage can jump from 80% to 110% (OOM) in milliseconds—faster than the spill thread can write to the

spill_local_storage_dir

. * Is lowering to 0.5–0.7 common? Yes, it is a recommended "safety buffer" for environments with high ingestion/scan speeds or when using slower local disks for spilling. Setting it to

0.7

(70%) gives the spill engine a 30% "runway" to begin flushing data before the Process/OS OOM killer intervenes. * Downsides: * Performance "False Positives": You might trigger disk I/O (which is ~10x slower) for queries that actually could have fit in memory if left alone. * Disk I/O Contention: If many queries trigger spill early, your

spill_local_storage_dir

disks can become a bottleneck, potentially slowing down the entire CN node. * Better Alternative (

spill_revocable_max_bytes

): Instead of a global pool threshold, you can use the session variable

spill_revocable_max_bytes

(introduced in v3.2+). This forces a specific operator (like your Sort) to start spilling once it hits a specific size (e.g., 5GB), regardless of the total pool state. This is often more "surgical" than lowering the global threshold. Summary Checklist for your 4.1.1 Setup: 1. Reduce DOP: Stick with

pipeline_dop = 8

(or even

4

) for these massive wide-table sorts. 2. Adjust Threshold: If you keep hitting OOMs before spilling starts, set

query_pool_spill_mem_limit_threshold = 0.8

as a starting point. 3. Check I/O: Ensure

spill_local_storage_dir

points to a fast NVMe/SSD. If you are on CN nodes with limited local storage, consider

enable_spill_to_remote_storage

(spilling to S3), though this is significantly slower than local disk. 4. Resource Groups: Ensure your heavy queries are assigned to a group with a

mem_limit

and

spill_mem_limit_threshold

specifically tuned for background ETL vs. Dashboard traffic. References • administration/management/BE_configuration.md • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md