http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> 3.5.16 - shared data mode deployed ...
# questions-and-troubleshooting
a
@Rocky 3.5.16 - shared data mode deployed on kubernetes through kubernetes-operator. 4FEs, 3CNs. I am getting a 
Mem usage has exceed the limit of the resource group [default_mv_wg].
Where should I increase the memory?
r
The error 
Mem usage has exceed the limit of the resource group [default_mv_wg]
indicates that your asynchronous Materialized View (MV) refresh tasks are consuming more memory than the 
default_mv_wg
resource group is allowed to use. By default, 
default_mv_wg
is restricted to 80% of the BE/CN's query memory pool. You have two main ways to increase the memory for these tasks: 1. Increase the Resource Group Limit (SQL) You can increase the percentage of memory allocated to the MV resource group without restarting any services. Run this SQL command from any MySQL client connected to your cluster:
Copy code
sql
ALTER RESOURCE GROUP default_mv_wg WITH ('mem_limit' = '90%');
Note: The value is a percentage of the total query pool (which is usually 90% of the pod's total memory by default). 2. Increase CN Pod Resources (Kubernetes Operator) If the resource group is already set to a high percentage, you likely need to increase the absolute memory available to your Compute Nodes (CNs). Since you are using the StarRocks Operator, you should modify your 
StarRocksCluster
CRD:
Copy code
yaml
apiVersion: <http://starrocks.com/v1|starrocks.com/v1>
kind: StarRocksCluster
metadata:
  name: starrockscluster-sample
spec:
  starrocksCnSpec:
    requests:
      memory: "32Gi"  # Increase this value
    limits:
      memory: "32Gi"  # Increase this value
After applying this, the Operator will perform a rolling restart of your CNs. Because the resource group limit is a percentage, giving the pod more RAM automatically increases the total bytes available to 
default_mv_wg
. Additional Recommendations * Spilling to Disk: If your MV refresh is very large, you can enable or adjust the spill threshold to prevent it from failing when memory is tight. 
default_mv_wg
has a default 
spill_mem_limit_threshold
of 80% (meaning it tries to spill when it reaches 80% of its own 80% limit).
Copy code
sql
    ALTER RESOURCE GROUP default_mv_wg WITH ('spill_mem_limit_threshold' = '60%');
* Specific Resource Groups: If only one specific MV is the "memory hog," you can create a dedicated resource group with a higher limit and assign only that MV to it:
Copy code
sql
    CREATE RESOURCE GROUP big_mv_rg WITH ('mem_limit' = '95%');
    ALTER MATERIALIZED VIEW your_mv_name SET ("resource_group" = "big_mv_rg");
Summary: Start by running the 
ALTER RESOURCE GROUP
SQL command to see if a higher percentage (e.g., 90% or 95%) solves the issue. If the physical pods are simply too small for the data volume, increase the memory in your Kubernetes YAML. References • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • deployment/shared_data/gcs.md • deployment/shared_data/minio.md
a
@Rocky - in this setup - where and how do I specify the spill size?
r
In StarRocks (especially version 3.x+), "spill size" isn't a single "max bytes" parameter. Instead, it refers to two things: the memory threshold that triggers the spill and the disk capacity available to store the spilled data. In your Kubernetes-operator setup for shared data mode, here is how you specify and configure these: 1. The Memory Trigger (When to spill) If you are hitting the memory limit of 
default_mv_wg
, you should lower the spill threshold so that the system starts writing to disk before it hits the hard memory limit. Run this SQL to trigger spilling earlier (e.g., at 60% of the resource group's memory):
Copy code
sql
ALTER RESOURCE GROUP default_mv_wg WITH ('spill_mem_limit_threshold' = '60%');
* Default: 80%. * Mechanism: If your resource group has 10GB RAM, it will now start spilling to disk once it uses 6GB, leaving a 4GB "buffer" for non-spillable operations. 2. The Physical Disk Capacity (Where it spills) In Kubernetes, you must ensure the CN pods have a directory to write to. By default, StarRocks spills to the path defined in 
spill_local_storage_dir
(default is the StarRocks installation directory, which might be a small ephemeral root partition in a container). Step A: Define the path in the Operator CRD Modify your 
StarRocksCluster
YAML to specify a directory. For CNs, use 
cn.conf
(or 
config
section in the spec):
Copy code
yaml
apiVersion: <http://starrocks.com/v1|starrocks.com/v1>
kind: StarRocksCluster
spec:
  starrocksCnSpec:
    config: |
      spill_local_storage_dir=/opt/starrocks/cn/spill
    # Step B: Add storage for this path
    storageVolumes:
    - name: cn-spill-data
      storageClassName: standard # Use your K8s storage class
      storageSize: 100Gi        # This is your "Spill Size" limit
      mountPath: /opt/starrocks/cn/spill
3. Required Settings Even with a directory and threshold, spilling must be enabled globally. In v3.5.x, you should verify these variables: * FE Configuration: Ensure 
enable_materialized_view_spill
is 
true
(it is by default). * System Variable: The global spill switch might be off by default. To enable it for the system (including MV refreshes):
Copy code
sql
    SET GLOBAL enable_spill = true;
Summary of where to check: 1. Memory Size: Increase the pod RAM in the Operator YAML 
resources.limits
. 2. Disk Size: Increase the 
storageSize
of the volume mounted to the spill directory in the Operator YAML. 3. Threshold: Use 
ALTER RESOURCE GROUP default_mv_wg
to lower the 
spill_mem_limit_threshold
if it's hitting the limit too fast. Caution: Spilling significantly slows down MV refreshes (often by 10x). If you have enough RAM, increasing the CN memory limit is always preferred over spilling. References • administration/management/resource_management/spill_to_disk.md • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md • sql-reference/System_variable.md References • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md • administration/management/BE_configuration.md
a
@Rocky On fe I have
Copy code
enable_spill=false
enable_spill_buffer_read=true
enable_spill_to_remote_storage=false
But as far as I understand fe settings are for queries only On CN I have
Copy code
enable_load_spill=true

default_mv_resource_group_spill_mem_limit_threshold=0.8

spill_init_partition=16
spill_local_storage_dir=/opt/starrocks/cn/spill
spill_max_dir_bytes_ratio=0.8
spill_max_log_block_container_bytes=10737418240
spill_max_partition_level=7
spill_max_partition_size=1024
spill_read_buffer_min_bytes=1048576
query_pool_spill_mem_limit_threshold=1

load_spill_max_chunk_bytes=10485760
load_spill_max_merge_bytes=1073741824
load_spill_merge_max_thread=16
load_spill_merge_memory_limit_percent=30
experimental_spill_skip_sync=true
r
Your understanding that FE settings are for queries only is partially correct, but since Materialized View (MV) refresh tasks are essentially background queries orchestrated by the FE, the FE's session variables and configurations directly control whether those tasks are allowed to spill. Based on the configuration you shared, here is where you need to make changes to fix the 
Mem usage has exceed the limit
error: 1. Enable Spilling for the MV (The "Switch") Even though you have spill directories configured on the CN, spilling is disabled by default for queries and MV tasks. Your FE shows 
enable_spill=false
. You have two options to enable it: * Option A: Globally (for everything)
Copy code
sql
    SET GLOBAL enable_spill = true;
* Option B: Specifically for that MV (Recommended) This allows other queries to stay in-memory for performance while letting the "heavy" MV spill to disk.
Copy code
sql
    ALTER MATERIALIZED VIEW <your_mv_name> SET ("session.enable_spill" = "true");
2. Specifying the "Spill Size" In your CN config, you have two parameters that define the "size" or limit of how much data can be spilled: * 
spill_local_storage_dir
: This is the physical path. The "size" is determined by the Persistent Volume (PV) size you assigned to this mount point in your Kubernetes 
StarRocksCluster
CRD. * 
spill_max_dir_bytes_ratio
(0.8): This tells StarRocks to stop spilling if the disk reaches 80% capacity. _ _To increase capacity:* Increase the storage request in your K8s YAML for the volume mounted at 
/opt/starrocks/cn/spill
. * 
spill_max_log_block_container_bytes
: You have this set to ~10GB. This limits the size of individual spill data containers. Generally, the default is fine, but if you have extremely large MVs, ensure your total disk space is significantly larger than this. 3. Adjusting the Trigger (The "Threshold") Your CN config shows: 
default_mv_resource_group_spill_mem_limit_threshold=0.8
This means: 1. The 
default_mv_wg
has a 
mem_limit
(default 80% of the query pool). 2. The CN will trigger a spill once the MV uses 80% of that 80%. If you are still hitting the "Mem usage has exceed" error after enabling spill, it means the task is hitting the hard limit before the spill can offload enough data. Lower the threshold to trigger spilling earlier:
Copy code
sql
ALTER RESOURCE GROUP default_mv_wg WITH ('spill_mem_limit_threshold' = '0.5');
Important Distinctions in your Config: * 
enable_load_spill
: This is for 
INSERT INTO ... SELECT
or Stream Load operations. It is not the same as the intermediate result spilling required for MV refreshes (which use 
enable_spill
). * 
enable_materialized_view_spill
: This is an FE 
fe.conf
parameter (default 
true
) that allows the FE to even consider spilling for MVs. Since it's likely true, the bottleneck is the session variable 
enable_spill
. Summary Checklist: 1. Check K8s: Ensure the CN pod has a large enough Volume mounted at 
/opt/starrocks/cn/spill
. 2. Run SQL: 
ALTER MATERIALIZED VIEW your_mv SET ("session.enable_spill" = "true");
3. Run SQL: 
ALTER RESOURCE GROUP default_mv_wg WITH ('spill_mem_limit_threshold' = '0.6');
(Trigger spill at least 20-30% buffer before the hard limit). References • administration/management/resource_management/spill_to_disk.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md
a
@Rocky - considering how I have starrocks deployed - is there a way to permanent enable_spill = true settings in the configs somewhere?
r
In StarRocks, there is a distinction between Configuration Items (set in 
fe.conf
or 
cn.conf
) and System Variables (set via SQL). 
enable_spill
is a System Variable. To enable it permanently in your Kubernetes deployment, you should use the following method: 1. The Standard Permanent Method: 
SET GLOBAL
 The most reliable way to make this setting permanent is to run this command once from any MySQL client:
Copy code
sql
SET GLOBAL enable_spill = true;
Why this works in Kubernetes: StarRocks stores "Global" variables in its internal metadata (the FE's edit logs). As long as your FE pods are using *Persistent Volumes (PVs)*—which they should be in a production-ready Kubernetes-operator deployment—this setting is saved to disk. When the FE pods restart or the cluster is upgraded, they reload the metadata and 
enable_spill
remains 
true
. 2. The MV-Specific Permanent Method If you only want to enable spilling for the Materialized View tasks (to avoid slowing down normal interactive queries), you can attach the setting directly to the MV definition. This is stored in the database metadata permanently:
Copy code
sql
ALTER MATERIALIZED VIEW <your_mv_name> SET ("session.enable_spill" = "true");
3. Why it isn't in 
fe.conf
or 
cn.conf
 Unlike 
spill_local_storage_dir
(which is a node-level hardware path), 
enable_spill
is a logic switch for the query engine. StarRocks does not currently have a "default_session_variables" string in the 
fe.conf
file to set these at the file level. Summary Checklist for Kubernetes: 1. Ensure FE Persistence: Verify your 
StarRocksCluster
YAML has 
storageVolumes
defined for the FEs. If you are using ephemeral storage for FEs (not recommended), 
SET GLOBAL
will be lost on a full cluster wipe. 2. Apply the Variable: Run 
SET GLOBAL enable_spill = true;
. 3. Verify: You can check if it's active for new sessions by opening a new connection and running:
Copy code
sql
   SHOW VARIABLES LIKE 'enable_spill';
Important Note: Since you are on version 3.5.x, also ensure your 
default_mv_wg
resource group has a lower 
spill_mem_limit_threshold
(as discussed previously) to ensure the spill starts before the memory limit reached error is triggered. References • sql-reference/sql-statements/cluster-management/config_vars/SET.md • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • quick_start/helm.md • deployment/helm.md • deployment/sr_operator.md
a
@Rocky - after enabling spill and setting spill to threshold to 0.5, still getting`mv_mymv_v2` Used: 39661466032, Limit: 39659728010.
r
The fact that your memory usage reached 36.94 GB (matching the 36.93 GB limit) despite setting a 0.5 (50%) threshold suggests that either the spill was never triggered, or the memory was consumed by "unspillable" parts of the query. Here is how to diagnose and fix this: 1. Verify if Spill is actually running The most common reason for this is that the background MV task isn't "seeing" the 
enable_spill
variable. Background tasks use the session variables stored inside the MV's metadata. Run this to force the setting into the MV itself:
Copy code
sql
ALTER MATERIALIZED VIEW mv_mymv_v2 SET ("session.enable_spill" = "true");
-- Also set this to ensure it triggers early regardless of other queries
ALTER MATERIALIZED VIEW mv_mymv_v2 SET ("session.spill_mode" = "force");
_Setting 
spill_mode
to 
force
will bypass the threshold logic and spill everything possible to disk immediately. This is a good test to see if spilling is even working._ 2. Check for "Unspillable" Operations Not all memory in StarRocks can be spilled. If your MV refresh hits the limit, it might be due to: * Expression Evaluation: Complex nested functions or massive string manipulations happen in-memory and cannot be spilled. * Small Buckets/Skew: If you are joining on a column with a massive data skew (one key has millions of rows), that specific "hash bucket" must fit in RAM to be processed. * Metadata/Framework overhead: The memory used to manage the query plan itself. How to check: After the failure, look at the Profile of the refresh task (accessible via the FE Web UI at 
http://<FE_IP>:8030/query
or by searching the 
information_schema.task_runs
table). Look for the operator with the highest 
PeakMemoryUsage
. If it's a 
CHUNK_SOURCE
or an expression-heavy operator, spilling won't help. 3. Check the CN Node's Global Spill Threshold In your configuration, you have: 
query_pool_spill_mem_limit_threshold=1
This is a "master switch" on the CN. It means: "Even if a resource group wants to spill, don't allow any spilling on this node until the entire query pool is 100% full." Fix: Change this in your CN config (or via 
ADMIN SET FRONTEND CONFIG
if applicable, but usually it's a BE/CN flag) to a lower value like 
0.8
to give the node breathing room to manage spill buffers. 4. Enable Partition-wise Aggregation (If using v3.5.x) Since you are on a recent version, if your MV does heavy 
GROUP BY
operations, enable this specific variable on the MV:
Copy code
sql
ALTER MATERIALIZED VIEW mv_mymv_v2 SET ("session.spill_partitionwise_agg" = "true");
This reduces the peak memory required for large aggregations during a spill. 5. Final Troubleshooting Steps 1. Check Disk Permissions: Ensure the user running the StarRocks process has write access to 
/opt/starrocks/cn/spill
. 2. Monitor Live: While the MV is refreshing, run:
Copy code
sql
    SHOW PROC '/current_queries';
Look at the 
DiskSpillSize
column. If it stays 
0.000
while 
MemoryUsage
climbs toward 36GB, the spill is definitely not triggering. 3. Check Task History:
Copy code
sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name = 'mv_mymv_v2'
    ORDER BY create_time DESC LIMIT 1\G
Check the 
EXTRA_MESSAGE
field; it sometimes contains specific details about why the plan couldn't execute. References • administration/management/resource_management/spill_to_disk.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/System_variable.md References • administration/management/resource_management/spill_to_disk.md
a
@Rocky - how do I delete
Copy code
ALTER MATERIALIZED VIEW mv_mymv_v2 SET ("session.enable_spill" = "true");
-- Also set this to ensure it triggers early regardless of other queries
ALTER MATERIALIZED VIEW mv_mymv_v2 SET ("session.spill_mode" = "force");
after the test?
r
To "delete" or revert these settings after your test, you simply set them back to their default values using the same 
ALTER MATERIALIZED VIEW
command. StarRocks does not have a specific 
UNSET
or 
DELETE
keyword for these properties. Run the following to return to the default behavior (spill disabled and mode set to auto):
Copy code
sql
ALTER MATERIALIZED VIEW mv_mymv_v2 SET (
    "session.enable_spill" = "false",
    "session.spill_mode" = "auto"
);
How to verify they are gone/changed You can confirm the current settings attached to your Materialized View by running:
Copy code
sql
SHOW CREATE MATERIALIZED VIEW mv_mymv_v2;
In the output, look at the 
PROPERTIES
section at the end of the statement. * If you set them back to the system defaults, they might still show up in the text but with the new values (
"false"
and 
"auto"
). * In some versions, setting a property to an empty string or its exact default might remove it from the display, but explicitly setting it to the default value is the standard way to "undo" the change. Summary of Defaults: * 
session.enable_spill
: Default is 
false
. * 
session.spill_mode
: Default is 
auto
. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/System_variable.md References • sql-reference/System_variable.md
Open in Slack
PreviousNext
Powered by