@Rocky How does MVs refresh work on StarRocks. I have external Glue catalog with iceberg tables and I have created MVs for faster access using following syntax:

CREATE MATERIALIZED VIEW mv_dim_merchant
PARTITION BY (merchant_id)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (interval 1 day)
AS
SELECT * FROM xeno_prod_ib.reporting_staging.dim_merchant;

First of all does starrocks automatically update my query to read from mv if I query like

select id, name from xeno_prod_ib.reporting_staging.dim_merchant;

. Secondly what kind of resources (CPU/Memory/etc) does starrocks uses to update these MVs. My usecase is reads should be faster and support high concurrency like 50-60 atleast. How can I monitor my MV health like data freshness & performance. My StarRocks version:

4.1.3-8a8e186

I have deployed StarRocks on my EKS cluster using help chart with 3 FEs, 3 BEs and auto-scaling CNs.

Based on your version

4.1.3

(which includes features from the latest 3.x branches) and your EKS deployment, here is how Materialized View (MV) refresh, query rewriting, and monitoring work in StarRocks. 1. Automatic Query Rewrite Yes, StarRocks can automatically rewrite your query to read from the MV. When you query the base Iceberg table (

SELECT id, name FROM xeno_prod_ib...

), the StarRocks Optimizer checks if any existing MVs can satisfy the query. However, for this to work with an external Iceberg catalog, there are two key considerations: * Consistency Check: By default, StarRocks ensures data consistency. If the base Iceberg table has new data that hasn't been refreshed into the MV yet, StarRocks will not rewrite the query (it will fall back to the base table) to avoid returning stale data. * Staleness Tolerance: Since you want high concurrency (50-60), you might prefer speed over absolute freshness. You can allow the optimizer to use the MV even if it's slightly stale by setting the

mv_rewrite_staleness_second

property.

sql
    ALTER MATERIALIZED VIEW mv_dim_merchant SET ("mv_rewrite_staleness_second" = "3600");
    -- Allows rewrite if MV was refreshed within the last 1 hour

* Configuration: Ensure query rewrite is enabled for external tables (it is usually default for Iceberg, but you can explicitly set it):

sql
    PROPERTIES ("force_external_table_query_rewrite" = "true")

2. Resource Usage (CPU/Memory) MV refreshes in StarRocks are essentially

INSERT OVERWRITE

jobs. They consume resources on your BE/CN nodes: * Execution: The refresh task scans data from your Glue/Iceberg source (S3), performs any transformations/joins, and writes it into StarRocks' local storage. * Resource Groups: By default, these tasks run in a system resource group called

default_mv_wg

, which is configured with lower priority (lower CPU weight) so it doesn't starve your front-end queries. * Isolation: To support your target of 50-60 concurrent queries, it is highly recommended to create a dedicated Resource Group for your MVs. This ensures that a heavy refresh task doesn't spike CPU/Memory and impact your user queries.

sql
    CREATE RESOURCE GROUP mv_refresh_rg
    TO (user='mv_user') -- or link to specific MVs
    WITH (
        "cpu_weight" = "10",
        "mem_limit" = "20%",
        "concurrency_limit" = "2"
    );

* Scaling: Since you use auto-scaling CNs, the refresh load can be distributed across them. StarRocks will use the compute power of your CNs to handle the "Scan + Insert" logic. 3. Monitoring Health & Performance You can monitor MVs using SQL commands or integrated metrics: A. SQL-based Monitoring * Check State: See if the MV is active and when it last refreshed.

sql
    SHOW MATERIALIZED VIEWS LIKE 'mv_dim_merchant'\G
    -- Focus on: is_active, last_refresh_state, last_refresh_duration

* History & Errors: View the history of every refresh attempt to find failures.

sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name LIKE 'mv-%'
    ORDER BY create_time DESC;

* Real-time Performance: While a refresh is running, check resource usage:

sql
    SHOW PROC '/current_queries';

B. Metrics (Prometheus/Grafana) StarRocks exports MV-specific metrics that you can visualize in Grafana: * `mv_refresh_total_failed_jobs`: High counts indicate issues with Glue/S3 connectivity or schema mismatches. * `mv_query_total_hit_count`: Tracks how often your MV is actually being used for query rewriting (crucial for verifying "faster access"). * `mv_refresh_duration`: Monitor this to see if your "1 day" refresh window is being met. Recommendation for Iceberg