@Rocky WHen trying to create a resource group for my materialized views CREATE RESOURCE GROUP mv_wg WITH ( "cpu_weight" = "1", "mem_limit" = "30%", "spill_mem_limit_threshold" = "0.1", "concurrency_limit" = "3" ); I get This type Resource Group need define classifiers.

In StarRocks, the

CREATE RESOURCE GROUP

statement requires a classifier (the

TO

clause) to be valid. Classifiers define which queries or tasks should be mapped to that resource group. To fix the error, you must add a

TO

clause. For materialized view (MV) refresh tasks, which are internally executed as

INSERT

operations, you typically use a classifier that matches the

query_type

or the

user

who owns the MV. Solution 1: Use a classifier for MV tasks If you want to create a group specifically for MV refreshes, you can use

query_type in ('insert')

and/or a specific user:

sql
CREATE RESOURCE GROUP mv_wg
TO 
  (query_type in ('insert'), user='mv_admin_user') -- Matches inserts by this user
WITH (
  "cpu_weight" = "1",
  "mem_limit" = "30%",
  "spill_mem_limit_threshold" = "0.1",
  "concurrency_limit" = "3"
);

Solution 2: Assign a group directly to an MV If you prefer to manually assign a resource group to a specific Materialized View, you still need to define the group with a classifier first (StarRocks syntax requires it). You can then link the MV to it via properties: 1. Create the group (with a dummy or specific classifier):

sql
   CREATE RESOURCE GROUP mv_wg
   TO (user='dummy_user')
   WITH (
     "cpu_weight" = "1",
     "mem_limit" = "30%",
     "concurrency_limit" = "3"
   );

2. Assign it to the MV:

sql
   -- During creation
   CREATE MATERIALIZED VIEW my_mv
   REFRESH ASYNC
   PROPERTIES ("resource_group" = "mv_wg")
   AS SELECT ...;

   -- Or for an existing MV
   ALTER MATERIALIZED VIEW my_mv SET ("resource_group" = "mv_wg");

Important Notes: * System Default: StarRocks already provides a system-defined resource group called

default_mv_wg

specifically for asynchronous MV refreshes. If you don't specify a

resource_group

property for an MV, it uses this group. You can modify it using

ALTER RESOURCE GROUP default_mv_wg WITH (...)

. * Classifier Matching: If multiple classifiers match a query, StarRocks uses a weighting system to pick the best match. Using the

db

or

user

classifier is a reliable way to ensure MV tasks land in the correct group. References • best_practices/audit_log_resource_group.md • administration/management/resource_management/resource_group.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md

@Rocky I am hitting Refresh mv mv_ticket_piab_watchdog_wtn failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.DdlException: Memory of mv_wg exceed limit. read and decompress page with the following setting: CREATE RESOURCE GROUP mv_wg TO (user='mv_user') WITH ( "cpu_weight" = "1", "mem_limit" = "40%", "spill_mem_limit_threshold" = "0.1", "concurrency_limit" = "2" ); How should I adjust it?

This error occurs because the memory required to execute the materialized view (MV) refresh exceeds the 40% quota you've allocated to the

mv_wg

resource group. The "read and decompress page" part of the error indicates the failure happened while the system was trying to load data from storage into memory. Here are the recommended steps to resolve this: 1. Increase the Memory Limit A 40% limit is relatively restrictive for heavy MV refreshes. The system-defined default for MV refreshes (

default_mv_wg

) is actually 80%. Try increasing your limit to allow the task more headroom:

sql
ALTER RESOURCE GROUP mv_wg WITH ("mem_limit" = "80%");

2. Reduce Concurrency You currently have

concurrency_limit

set to

2

. This means if two refresh tasks run simultaneously, they must share that 40% (effectively 20% each). If your MV is large, 20% of BE memory is likely insufficient. Try setting concurrency to

1

to ensure the task has the full group's memory:

sql
ALTER RESOURCE GROUP mv_wg WITH ("concurrency_limit" = "1");

3. Adjust the Spill Threshold Your

spill_mem_limit_threshold

is set to

0.1

. While this triggers spilling very early (at 10% of the group's memory), some operations are not "spillable" or require a minimum amount of memory to process data pages. If the 40% limit is hit before the spill can mitigate the usage, it will still fail. Note: Ensure spilling is also enabled at the MV session level, otherwise the resource group threshold won't trigger anything:

sql
ALTER MATERIALIZED VIEW mv_ticket_piab_watchdog_wtn 
SET ("session.enable_spill" = "true");

4. Use Partitioning If the MV is still hitting memory limits even with more resources, the most sustainable fix is to ensure the MV is partitioned. This allows StarRocks to refresh one partition at a time rather than the entire dataset, significantly reducing the memory footprint of each individual refresh task. Summary of Recommended Config

sql
ALTER RESOURCE GROUP mv_wg WITH (
    "mem_limit" = "80%", -- Match the system default for MVs
    "concurrency_limit" = "1", -- Give the full quota to a single task
    "spill_mem_limit_threshold" = "0.5" -- Spill when using 50% of this group's memory
);

If you continue to see this error after these changes, you may need to check the

query_id

in

information_schema.task_runs

and examine the Profile to see which specific operator (e.g., a massive Join or Aggregation) is consuming the most memory. References • faq/Sql_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • knowledge/usage/best-practice.md • administration/management/BE_configuration.md • best_practices/audit_log_resource_group.md

@Rocky - now I am getting Refresh mv failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.StarRocksException: Exceed concurrency limit: 1 backend [id=10003] [

The error

Exceed concurrency limit: 1 backend [id=10003]

means that on a specific backend (BE), there is already a query or task running in the

mv_wg

resource group, and your limit of

1

is preventing a second one from starting. Since your group is tied to

user='mv_user'

, any query executed by that user—including manual

SELECT

statements or overlapping MV refreshes—will count against this limit. To resolve this, you have three options: 1. Enable Query Queuing (Recommended) Instead of failing immediately when the limit is hit, you can make the task wait in a queue. This is the best way to handle concurrency limits without failing tasks.

sql
-- Enable the global query queue
SET GLOBAL enable_query_queue = true;

-- For StarRocks 3.1.4+, you can also manage this at the resource group level
-- by ensuring the concurrency_limit is set (which you already have).

2. Increase the Concurrency Limit A limit of

1

is extremely strict. If a previous refresh task hasn't finished or if you are running a metadata query as

mv_user

, the refresh will fail. Increase it to at least

2

or

4

to allow some overlap.

sql
ALTER RESOURCE GROUP mv_wg WITH ("concurrency_limit" = "4");

_Note: If you are worried about memory, the

mem_limit

and

spill_mem_limit_threshold

you already set will protect the system better than a hard concurrency limit of 1._ 3. Check for "Stuck" or Concurrent Tasks If you believe nothing else should be running, check if a previous attempt is still active or if another query is occupying the slot: * Check running tasks:

sql
    SELECT * FROM information_schema.task_runs WHERE status = 'RUNNING';

* Check active queries:

sql
    SHOW PROCESSLIST; -- Look for queries by 'mv_user'

Summary of why this happened When you set

concurrency_limit

to

1

(following the previous suggestion to give the MV maximum memory), it made the resource group "exclusive" to only one query at a time per backend. The

try lock failed: 0

error is the Materialized View manager failing to start the refresh because it couldn't acquire a concurrency "slot" in your resource group. Suggested Final Configuration:

sql
ALTER RESOURCE GROUP mv_wg WITH (
    "mem_limit" = "80%",           -- Allow more memory
    "concurrency_limit" = "4",     -- Allow some concurrency to prevent immediate failure
    "spill_mem_limit_threshold" = "0.5" 
);
SET GLOBAL enable_query_queue = true;

References • administration/management/resource_management/query_queues.md • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/monitoring/metrics.md

@Rocky - starrocks - concurrency limit on a resource group acts as a hard limit and does not queue

You are correct that by default, the

concurrency_limit

on a resource group acts as a hard limit. When it is reached, StarRocks immediately returns an "Exceed concurrency limit" error instead of waiting. To make queries wait in a queue instead of failing, you must explicitly enable the Query Queue feature. How to enable queuing You need to set two session variables to change this behavior (available from version 3.1.4 onwards for group-level queuing): 1. Enable the Global Queue:

sql
    SET GLOBAL enable_query_queue = true;

2. Enable Group-Level Queuing: This tells StarRocks that the

concurrency_limit

in your resource group should trigger a queue rather than an error.

sql
    SET GLOBAL enable_group_level_query_queue = true;

How it works after enabling * Queuing: If the

concurrency_limit

(e.g., 1) is reached, the next MV refresh task will enter a

PENDING

state. * Timeout: The query will wait until a slot opens up or until it hits the

query_queue_pending_timeout_second

(default is usually 300s). * Max Queue Size: You can control how many queries are allowed to wait using

query_queue_max_queued_queries

. If the queue is full, it will still fail with a "queue full" error. Summary for your MV Refresh Since you want your MV to succeed eventually even if the limit is tight, run:

sql
SET GLOBAL enable_query_queue = true;
SET GLOBAL enable_group_level_query_queue = true;

Note: If you are on a version older than 3.1.4, resource groups do not support queuing based on their own

concurrency_limit

. In those older versions, queuing only triggers based on global BE resource thresholds (CPU/Mem), and the resource group limit remains a hard cap. References • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/resource_management/query_queues.md

@Rocky - version 3.5.16 - concurrency limit on a resource group is getting treated as hard limit even though

SET GLOBAL enable_query_queue = true;
SET GLOBAL enable_group_level_query_queue = true;

have been executed. I am getting Refresh mv my_mv failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.StarRocksException: Exceed concurrency limit: 2 backend [id=10003] [ at com.starrocks.qe.DefaultCoordinator.dealStatusToTryRetry(DefaultCoordinator.java:930) at com.starrocks.qe.DefaultCoordinator.handleErrorExecution(DefaultCoordinator.java:750) at com.starrocks.qe.scheduler.Deployer.waitForDeploymentCompletion(Deployer.java:309) at com.starrocks.qe.scheduler.Deployer.deployFragments(Deployer.java:171) at com.starrocks.qe.scheduler.dag.AllAtOnceExecutionSchedule.schedule(AllAtOnceExecutionSchedule.java:133) at com.starrocks.qe.DefaultCoordinator.deliverExecFragments(DefaultCoordinator.java:663) at com.starrocks.qe.DefaultCoordinator.startScheduling(DefaultCoordinator.java:556) at com.starrocks.qe.scheduler.Coordinator.exec(Coordinator.java:95) at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:2721) [wrapped] com.starrocks.common.StarRocksException: Exceed concurrency limit: 2 backend [id=10003] [ at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3006) at com.starrocks.load.InsertOverwriteJobRunner.executeInsert(InsertOverwriteJobRunner.java:409) at com.starrocks.load.InsertOverwriteJobRunner.doLoad(InsertOverwriteJobRunner.java:178) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:158) at com.starrocks.load.InsertOverwriteJobRunner.transferTo(InsertOverwriteJobRunner.java:224) at com.starrocks.load.InsertOverwriteJobRunner.prepare(InsertOverwriteJobRunner.java:277) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:155) at com.starrocks.load.InsertOverwriteJobRunner.run(InsertOverwriteJobRunner.java:143) at com.starrocks.load.InsertOverwriteJobMgr.executeJob(InsertOverwriteJobMgr.java:90) at com.starrocks.qe.StmtExecutor.handleInsertOverwrite(StmtExecutor.java:2522) at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:2615) at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:2531) at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.refreshMaterializedView(PartitionBasedMvRefreshProcessor.java:1255) at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedView(PartitionBasedMvRefreshProcessor.java:502) at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedViewWithRetry(PartitionBasedMvRefreshProcessor.java:408) at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doMvRefresh(PartitionBasedMvRefreshProcessor.java:342) at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.processTaskRun(PartitionBasedMvRefreshProcessor.java:195) at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:382) at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$0(TaskRunExecutor.java:60) at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840)

In StarRocks 3.5, even with

enable_group_level_query_queue

enabled, the system distinguishes between types of queries that are allowed to queue. Because an Asynchronous Materialized View refresh is executed as an

INSERT OVERWRITE

(a load operation), it is governed by the "load" queue toggle rather than the general or select toggle. The error you are seeing comes directly from the Backend (BE), which means the Frontend (FE) "skipped" the queue and tried to execute the task immediately, leading the BE to reject it for exceeding the hard resource group limit. Solution You must explicitly enable queuing for load tasks (which include

INSERT

and MV refreshes). Run the following:

sql
-- This is likely the missing piece for MV refreshes
SET GLOBAL enable_query_queue_load = true;

-- Ensure these are also set (as you mentioned)
SET GLOBAL enable_query_queue_select = true; 
SET GLOBAL enable_group_level_query_queue = true;

Why this happens * Selective Queuing: StarRocks allows you to enable queuing for

SELECT

,

LOAD

, and

STATISTIC

queries independently. If

enable_query_queue_load

is

false

(the default),

INSERT OVERWRITE

statements bypass the FE queue and hit the BE directly. * BE Enforcement: Since the FE didn't queue the task, it reached the BE. The BE sees that the resource group

mv_wg

already has 2 active tasks (your

concurrency_limit

), and since BEs do not have internal queues, it returns the

Exceed concurrency limit

error immediately. Additional Recommendations If the tasks are still failing or timing out after enabling the load queue, consider these settings: 1. Check Pending Timeout: If many refreshes trigger at once, they might time out while waiting. Increase the wait time if necessary:

sql
    SET GLOBAL query_queue_pending_timeout_second = 600; -- Default is often 300s

2. Verify Materialized View Session Variables: Some background MV tasks use the session variables captured at the time of the MV creation. If the global setting doesn't seem to take effect for the background task, you can force it on the MV itself:

sql
    ALTER MATERIALIZED VIEW my_mv SET ("session.enable_query_queue_load" = "true");

References • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/resource_management/resource_group.md • sql-reference/System_variable.md References • administration/management/resource_management/query_queues.md • administration/management/FE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • best_practices/audit_log_resource_group.md