@Rocky - starrocks 3.5.16 shared-data mode. Despite setting spill_mem_limit_threshold to 0.5 I am still running out of memory on my CNs with peaks hitting the max memory allocated for a few really heavy materialized views. I know that spill works and I don't have ability to add more memory right now (although hard drives for spilling are huge). What options do I have?

In StarRocks 3.5.x shared-data mode, if your CNs are still hitting OOM despite having spilling enabled, it is usually because the automatic spill trigger (

auto

mode) is not aggressive enough for your specific workload or because the peaks consist of non-spillable memory. Since you have plenty of disk space, you should shift from "reactive" spilling (waiting for a memory threshold) to "proactive" spilling. Here are the options to stabilize your CNs: 1. Force Aggressive Spilling for Specific MVs Instead of relying on the global memory threshold, you can force the heavy Materialized Views to spill intermediate results immediately. This is the most effective way to leverage your "huge" hard drives. Update your MV properties to use

force

mode:

sql
ALTER MATERIALIZED VIEW <mv_name> SET (
    'session.enable_spill' = 'true',
    'session.spill_mode' = 'force'
);

In

force

mode, StarRocks spills intermediate results for all supported operators (Aggregate, Sort, Join, CTE) regardless of the current memory usage. 2. Lower the Operator-Level Trigger If

force

mode is too slow, you can use the experimental variable

spill_revocable_max_bytes

. This tells StarRocks to start spilling as soon as a single operator (like a large Join or Hash Aggregate) reaches a certain size in memory.

sql
ALTER MATERIALIZED VIEW <mv_name> SET (
    'session.spill_revocable_max_bytes' = '536870912' -- 512MB
);

3. Check Global Trigger Configuration The parameter you mentioned,

spill_mem_limit_threshold

, is often a Resource Group property. If you are using the default resource group for MVs (

default_mv_wg

), ensure the BE/CN configuration matches your intent: *

query_pool_spill_mem_limit_threshold

: (CN/BE config) This triggers spilling when the total query pool exceeds this ratio. If this is set to

1.0

(default), it might trigger too late to prevent a spike. Try setting this to

0.5

in your

cn.conf

. *

default_mv_resource_group_memory_limit

: This limits the total memory the MV resource group can use. If this is too high (default

0.8

), it might not leave enough headroom for system processes and OS overhead. 4. Isolate MVs via Resource Groups If you haven't already, move these "heavy" MVs into a dedicated resource group with a strict memory limit. This won't stop them from using memory, but it will force them to trigger spilling much earlier without affecting the rest of the CN's stability.

sql
CREATE RESOURCE GROUP heavy_mv_group
TO (user='mv_user') -- or bind by specific MV names
WITH (
    'mem_limit' = '30%',
    'spill_mem_limit_threshold' = '0.5'
);

5. Account for Non-Spillable Memory Be aware that StarRocks cannot spill all types of memory. If your MV contains the following, spilling might not help the peaks: * Expression Evaluation: Memory used for calculating complex functions or large string manipulations. * Scan Buffers: Memory used while reading data from S3/Object storage. * Metadata and Chunk Overhead: The fixed cost of managing data structures. Recommendation: Start by setting

spill_mode = force

on your top 2-3 heaviest MVs. This will drastically reduce their peak memory footprint at the cost of some refresh latency, which sounds like an acceptable trade-off for your current situation. References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md