@Rocky As far as I know, MV workloads are not isolated by Resource Groups in StarRocks, but could you double-check that for me?

Actually, StarRocks does support isolating Materialized View (MV) workloads using Resource Groups. Since version 3.1, StarRocks has improved its handling of asynchronous MV refresh tasks to prevent them from impacting interactive queries. Here is how it works: 1. System-defined Resource Group (

default_mv_wg

) By default, all asynchronous MV refresh tasks are assigned to a system-defined resource group called

default_mv_wg

. * This group is designed to limit the impact of background refreshes. * The default limits (which you can modify) usually restrict CPU and memory usage (e.g.,

cpu_weight

is often set to a low value like 1, and

mem_limit

defaults to 80%). 2. Custom Resource Group Isolation You can explicitly assign a specific MV to a dedicated resource group to enforce stricter isolation. This is useful if you have a "heavy" MV that you don't want competing with other background tasks. * During creation:

sql
    CREATE MATERIALIZED VIEW mv_example
    REFRESH ASYNC
    PROPERTIES (
        "resource_group" = "your_dedicated_rg"
    )
    AS SELECT ...;

* For an existing MV:

sql
    ALTER MATERIALIZED VIEW mv_example SET ("resource_group" = "your_dedicated_rg");

3. Key Parameters (v3.1+) If you are on version 3.1 or later, you can also control the default behavior via BE configuration parameters like: *

default_mv_resource_group_cpu_limit

*

default_mv_resource_group_memory_limit

*

default_mv_resource_group_concurrency_limit

Summary If you are running a version older than 3.1, the isolation might be less robust or require manual mapping via user-based classifiers. However, in current versions (3.1+), MV isolation is a core part of the resource management framework. References • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • administration/management/FE_configuration.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

@Rocky Why is the overall query latency slowing down even though there is sufficient CPU headroom when the MV is running? It slows down repeatedly every 10 minutes. I suspected that compaction might be the cause of the query latency when the MV transforms and inserts data, but the actual compaction score was stable. If it is not due to compaction, what part do you suspect is the issue?

If you have verified that CPU headroom is sufficient and compaction scores are stable, but still experience periodic latency spikes during Materialized View (MV) refreshes, the issue is likely not resource exhaustion in the traditional sense. Based on StarRocks' architecture, there are three primary suspects for this behavior: 1. Metadata Lock Contention (FE Lock) Asynchronous MV refreshes perform an

INSERT OVERWRITE

operation. This requires an exclusive lock on the table or partition metadata during the transaction commit phase. * The Issue: If your queries are hitting the same MV or its base tables while the refresh is committing, they may be queued behind the exclusive lock. This creates a "stutter" in query performance that won't show up as high CPU usage because the threads are simply in a

WAITING

state. * How to check: During the 10-minute spike, run:

sql
    SELECT * FROM sys.fe_locks WHERE hold_time_ms > 100;

Look for

EXCLUSIVE

locks held by the MV refresh task. 2. Post-Refresh Statistics Collection (

analyze_mv

) By default, StarRocks triggers an automatic statistics collection task after an MV refresh to ensure the optimizer has up-to-date data. * The Issue: The session variable

analyze_mv

defaults to

sample

. Even a sampled analysis involves scanning data and updating FE metadata. If your MV is large, this background "Analyze" task can cause I/O contention or FE metadata pressure immediately after the data is inserted. * How to test: Try disabling it for the MV to see if the spikes disappear:

sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("analyze_mv" = "");

3. Disk I/O Contention (Write vs. Read) Even if the CPU is idle, the

INSERT OVERWRITE

operation generates significant Write I/O. * The Issue: If your BE nodes are using disks with limited IOPS (like HDD or lower-tier Cloud SSDs), the heavy write load from the MV refresh can increase the latency of the concurrent Read I/O required by your queries. Compaction scores only measure the accumulation of versions, not the instantaneous disk throughput saturation. * How to check: Monitor the

iostat

or disk utilization on your BE nodes during the refresh. If

%util

hits 100%, your queries are waiting for disk scheduling. 4. FE Task Scheduling Overload If your MV has many partitions and you are using a version like v3.3+, the system might be splitting the refresh into many small sub-tasks (controlled by

partition_refresh_number

). * The Issue: A high frequency of task state updates in the FE metadata (BDBJE) can cause the FE's internal log buffer to flush frequently, potentially leading to "Latch timeouts" or general FE responsiveness issues if the FE's own metadata disk is slow. Recommended Next Steps: 1. Check Locks: Use the

sys.fe_locks

query provided above during the spike. 2. Disable Auto-Analyze: Set

analyze_mv

to an empty string and monitor. 3. Audit Log Analysis: Check the

starrocks_audit_db*.starrocks_audit_tbl*

for the queries that slowed down. Look at the

ScanTime

and

WriteEndTime

to see which phase of the query specifically lengthened. References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sys/fe_locks.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/Cost_based_optimizer.md • administration/Meta_recovery.md References • administration/Meta_recovery.md

@Rocky Does StarRocks Materialized View refresh acquire a global lock?

StarRocks does not have a single "global lock" that halts the entire cluster, but it does use a Database-level lock which can exhibit similar symptoms, especially in environments where many tables are concentrated in a single database. Here is the breakdown of how locking works during an MV refresh and why you might be seeing those periodic slowdowns: 1. Database-level Lock (The Main Bottleneck) StarRocks FE (Frontend) uses a

ReentrantReadWriteLock

for each database. * Query Planning: Every query needs a

Database.readLock

during the planning phase. * MV Refresh/Commit: When an asynchronous MV refresh finishes its calculation, it performs an

INSERT OVERWRITE

to swap data. This operation requires a

Database.writeLock

to update the metadata in the catalog. 2. The "Fair Lock" Problem Crucially, StarRocks implements these as Fair Locks (

FairSync

). In a fair lock system: 1. If a thread requests a Write Lock (like an MV refresh committing) and has to wait for existing queries to finish, it enters a queue. 2. All subsequent Read Lock requests (new queries) must wait behind that writer in the queue, even if they could technically run in parallel with existing readers. 3. The Result: A single heavy MV refresh or a metadata update can "clog" the database lock queue, making it feel like a global lock because no new queries can start until the writer is satisfied. 3. The 10-Minute Periodic Slowdown The 10-minute interval you mentioned is a specific clue. In StarRocks, the default interval for External Catalog Metadata Refresh is exactly 10 minutes: * Parameter:

background_refresh_metadata_interval_millis

(Default: 600,000ms / 10 mins). * What it does: If you use Hive, Iceberg, or other external catalogs, the FE periodically refreshes the metadata cache to perceive data changes. * Impact: This background task can compete for FE locks every 10 minutes. If your MV is based on or involves external catalogs, this task might be the "silent" culprit causing the spike. 4. Why CPU looks fine Lock contention happens on the FE (Leader) threads, not the BE (Backend) workers. * The BEs might have 0% CPU usage because they are waiting for the FE to send them a fragment to execute. * The FE might show low CPU because its threads are simply in a

WAITING

state, blocked on the Java lock. Recommendations 1. Check FE Locks: During the slowdown, run the following to see exactly who is holding or waiting for a lock:

sql
    SELECT * FROM sys.fe_locks WHERE hold_time_ms > 100;

2. Audit `background_refresh`: If you use external catalogs, check if the 10-minute spikes align with your FE logs for metadata refresh. You can increase the interval or disable it for specific catalogs. 3. Lock Timeout: Check the

mv_refresh_try_lock_timeout_ms

parameter (v3.3+), which controls how long the MV refresh will wait for the DB lock before retrying, preventing it from blocking the queue indefinitely. References • knowledge/trouble_shooting/fe_deadlock_problems.md • administration/management/FE_configuration.md • knowledge/usage/best-practice.md • sql-reference/sys/fe_locks.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • knowledge/usage/best-practice.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md