@Rocky - 3.5.16 - shared data. 4 FE nodes, 3 CN nodes. 57GB free and usable on each CN - typical load around 16GB, but peak consumption burns all the memory. Getting Mem usage has exceed when the limit is reached from materialized views even though they have force spill on. Increasing memory allocation is restricted by underlying hardware. What else can I do?

When using StarRocks 3.5.16 (Shared Data) with Materialized Views (MVs), getting "Mem usage has exceed" errors despite having

force spill

enabled usually indicates that memory is being consumed by components that cannot be spilled, or that the spilling is not being triggered early enough to prevent the hard memory limit. Here are the specific actions you can take beyond increasing hardware RAM: 1. Optimize the Materialized View Strategy (High Impact) * Implement Partitioning: If your MV is not partitioned, a refresh will attempt to process the entire dataset at once. Partition the MV by a time column (e.g.,

PARTITION BY dt

). This allows StarRocks to refresh only specific partitions, significantly reducing the peak memory footprint per task. * Incremental Refresh: Ensure you are using

REFRESH ASYNC

with a partition strategy so that the system only computes the delta of the data rather than a full rebuild. 2. Configure Resource Groups for MVs Standard "force spill" settings might trigger too late. By creating a dedicated Resource Group for your MVs, you can control their memory behavior more strictly: * Lower the Spill Threshold: Set

spill_mem_limit_threshold

to a lower value (e.g.,

0.5

). This forces the MV refresh to start spilling when it hits 50% of its allocated memory, leaving more headroom for non-spillable operators. * Limit Concurrency: Set

concurrency_limit = 1

for the MV resource group to prevent multiple refresh tasks from competing for the same 57GB on a CN. * Set Memory Caps: Use

mem_limit

(e.g.,

0.4

) to ensure the MV group never consumes more than 40% of the CN's total memory, protecting the rest of the system.

sql
CREATE RESOURCE GROUP mv_wg
  TO (user='your_mv_user') 
  WITH (
    "mem_limit" = "40%",
    "spill_mem_limit_threshold" = "0.5",
    "concurrency_limit" = "1"
);

3. Check for Non-Spillable Operators Memory spilling in StarRocks currently supports Aggregate, Sort, Hash Join, and CTE operators. If your MV query includes the following, they will still consume raw RAM: * Complex Expressions: Large string manipulations or complex mathematical functions in the

SELECT

or

WHERE

clause. * Window Functions: Some window functions do not support spilling as efficiently as basic aggregations. * Expression Evaluation: Memory used for evaluating expressions and scalar functions is currently not released by the spilling mechanism. 4. Adjust CN Configuration (

cn.conf

) * `query_max_memory_limit`: Check if this is set. If an individual MV query hits this limit, it will fail even if the CN has free memory. * `default_mv_resource_group_spill_mem_limit_threshold`: If you are not using custom resource groups, you can set this parameter (default is

0.8

) in your

cn.conf

to a lower value (like

0.5

) to trigger spilling earlier for all MVs. * `storage_root_path`: Ensure the path used for

spill_local_storage_dir

is on a high-performance disk. If spilling is too slow, the memory usage might still climb faster than the system can offload it. 5. Session Variables for the MV When creating or altering the MV, ensure these properties are explicitly set:

sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.enable_spill" = "true",
    "session.spill_mode" = "force",
    "session.query_timeout" = "3600" -- Spilling is slow; increase timeout to prevent refresh failure
);

Summary Recommendation Start by partitioning the MV to reduce the data volume per refresh. If that isn't possible, create a Resource Group specifically for the MV tasks with a

spill_mem_limit_threshold

of 0.5 to force spilling to start much earlier.

@Rocky - materialized views are pretty much my only usage of starrocks. I have extremely large hard drives and refresh delays are of no concern time-wise, but, need the system to leverage hard drives instead of just failing if memory limit is reached. Can such no-failure mode be achieved?

While StarRocks provides powerful spilling capabilities, a true "unlimited memory/no-failure" mode does not exist because certain parts of query execution (like expression evaluation and metadata) must remain in RAM. However, since performance is not your priority and you have ample disk space, you can achieve a "stable-failure-resistant" mode by forcing the system to offload everything possible to the disk as early as possible. 1. Force Extreme Spilling via Resource Groups The standard

force

spill property on an MV ensures that spillable operators (Agg, Sort, Join, CTE) use disk, but it doesn't prevent the overall process or query memory limit from being hit by non-spillable components. By creating a dedicated Resource Group, you can force the system to trigger spilling at a much lower threshold (e.g., 10% of memory) to ensure the peak never hits your 57GB hard limit.

sql
CREATE RESOURCE GROUP mv_stable_group
  TO (user='your_mv_user') 
  WITH (
    "mem_limit" = "80%",                    -- Allow the MV to use up to 80% of CN RAM
    "spill_mem_limit_threshold" = "0.1",     -- START spilling to disk at 10% usage
    "concurrency_limit" = "1"                -- Only 1 MV refresh at a time to prevent memory spikes
);

* Why this works: Setting the

spill_mem_limit_threshold

to

0.1

ensures that as soon as the MV uses ~5.7GB, it starts writing to disk, leaving ~50GB of "headroom" for the non-spillable memory (expressions, buffers, etc.) that would otherwise cause a crash. 2. Identify and Eliminate Non-Spillable Memory If you have

force

spill on and are still hitting limits, the memory is likely being consumed by parts of StarRocks that cannot spill: * Complex Expressions: Very long

CASE WHEN

statements, complex string manipulations, or JSON parsing in the

SELECT

list. * Wide Rows: If your MV has hundreds of columns, the "Chunk" buffers in memory become very large. * Expression Evaluation: Memory used for calculating functions is currently not spillable. If your query has high cardinality calculations (e.g.,

distinct count

on millions of unique strings), this can still OOM. Action: Simplify the MV query if possible, or break one large MV into two smaller dependent MVs to reduce the complexity of the execution plan. 3. "Unlimited" Session Settings for the MV To prevent the query from killing itself before it even tries to spill, ensure the session limits are lifted for the MV. Modify your MV properties:

sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.query_mem_limit" = "0",          -- '0' means no per-query limit; use the process/group limit
    "session.enable_spill" = "true",
    "session.spill_mode" = "force",
    "session.query_timeout" = "86400"        -- 24 hours; spilling to disk is 10x+ slower
);

4. Mandatory Architectural Change: Partitioning If your MV is not partitioned, StarRocks must compute the entire dataset in one massive "morsel." * If you partition the MV (e.g.,

PARTITION BY date_col

), StarRocks will refresh it partition-by-partition. * This effectively caps the memory usage to the size of a single partition's data rather than the whole table. * Even with disk spilling, a partitioned MV is significantly more stable because the metadata and non-spillable overhead are scaled down to the partition level. 5. Check CN Configuration (

cn.conf

) Ensure your CNs are actually configured to handle massive spills: *

spill_local_storage_dir

: Ensure this points to your "extremely large hard drives." If it points to a small root partition, the spill will fail. *

memory_limitation_per_thread_for_schema_change

: For very large MVs, you may need to increase this from the default 2GB if the failure happens during the final "commit" or schema application phase of the MV.