@Rocky I have created an asynchronous materialized view that is partitioned the same as the base table. I defined refresh policy as "REFRESH ASYNC" and I expect it to refresh when the base table changes. However, when looking at tasks I see

"isManual":true

, and the MV does not refresh automatically. Is there something I am missing?

Hello! I'm Rocky, and I'd be happy to help you figure out why your Materialized View (MV) isn't refreshing as expected. The

"isManual": true

flag you are seeing in the

EXTRA_MESSAGE

of the

information_schema.task_runs

table indicates that the specific refresh task run was triggered by a user (i.e., you manually ran a

REFRESH MATERIALIZED VIEW

command). If the MV is working correctly in automatic mode, you should see other task runs where

"isManual": false

. If your MV is not refreshing automatically when the base table changes, here are the most common reasons: 1. The Base Table is an External Table (External Catalog) This is the most frequent cause. While StarRocks supports

REFRESH ASYNC

for many table types, automatic triggers on data change primarily work for StarRocks native tables (Duplicate, Primary Key, etc., in the

default_catalog

). * Reason: StarRocks can immediately detect changes to its own internal tables. However, for external tables (like Hive, Iceberg, or Hudi), StarRocks doesn't "know" when a file is added to S3 or HDFS by an external process. * Solution: For external tables, you should use a scheduled refresh instead of just

REFRESH ASYNC

. Update your MV to use a refresh interval:

sql
    ALTER MATERIALIZED VIEW your_mv_name
    REFRESH ASYNC EVERY (INTERVAL 1 MINUTE); -- Or 10 MINUTES, 1 HOUR, etc.

2. The First Refresh Must Be Manual In some versions of StarRocks, an asynchronous MV will not begin its automatic trigger cycle until the initial refresh has been successfully completed manually. * Action: Run

REFRESH MATERIALIZED VIEW your_mv_name;

once. After it succeeds, try making a change to the internal base table and see if a new task is created. 3. Data Load Method If you are using an internal table, how are you loading data? * Triggering Loads: Operations like

INSERT INTO

,

STREAM LOAD

,

ROUTINE LOAD

, and

BROKER LOAD

should trigger the MV. * Non-Triggering: If you are using

ALTER TABLE ... TRUNCATE

or certain metadata-only operations, they might not always trigger the refresh depending on the version. 4. Property

excluded_trigger_tables

Check if the base table was accidentally excluded from triggering refreshes. * Check: Run

SHOW CREATE MATERIALIZED VIEW your_mv_name;

and look for the property

"excluded_trigger_tables"

. If your base table is listed there, it won't trigger a refresh. 5. Task State and Failures If the last automatic refresh failed, the system might not trigger it again immediately until the next data change or might require manual intervention if the error is persistent. * Action: Check the

STATE

and

ERROR_MESSAGE

in `information_schema.task_runs`:

sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name = (SELECT task_name FROM information_schema.tasks WHERE definition LIKE '%your_mv_name%')
    ORDER BY create_time DESC LIMIT 5;

Summary Recommendation If your base table is in an External Catalog, switch to a scheduled refresh (

REFRESH ASYNC EVERY...

). If it is an Internal Table, ensure the first refresh was done manually and then check if subsequent

INSERT

or

LOAD

operations create new tasks with

"isManual": false

. Let me know if your base table is internal or external, and I can give you more specific advice! References • data_source/catalog/hive_catalog.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • data_source/catalog/maxcompute_catalog.md • data_source/feature-support-data-lake-analytics.md

@Rocky It is an internal table and it is not an excluded trigger table. It is being updated by routine load at a rate of 8,000 per second. Is that rate too fast for refresh async without an interval modifier?

For an internal table updated via Routine Load at 8,000 rows per second, using

REFRESH ASYNC

without an interval modifier (which makes the Materialized View (MV) event-driven) is likely too fast and can lead to significant performance issues. Here is why this configuration might be problematic and how to optimize it: 1. The Interaction between Routine Load and Event-Driven MV When you specify

REFRESH ASYNC

without an interval (e.g.,

EVERY (INTERVAL 1 MINUTE)

), StarRocks triggers a refresh task every time the base table's transaction commits. * Routine Load Commit Frequency: Routine Load typically commits every

max_batch_interval

(default is 10 seconds) or when it reaches

max_batch_rows

(default is 200,000). * Resulting Trigger Rate: At your current ingestion rate (8,000/sec), you will likely hit the 10-second interval first. This means the MV will attempt to refresh every 10 seconds. 2. Risks of High-Frequency Refresh * Resource Exhaustion: Even if the MV is partitioned and only refreshes the latest data, launching a refresh every 10 seconds consumes constant CPU and Memory in the

default_mv_wg

resource group. * Metadata Bloat: Frequent commits and refreshes create a large number of tablet versions. This can eventually lead to "too many versions" errors (the

too many tablet versions

error), which degrades query performance and can stop ingestion. * Task Queuing: If a single refresh takes longer than 10 seconds, the subsequent tasks will be merged or queued. While StarRocks handles this, it indicates that the system is under constant pressure. 3. Recommended Adjustments A. Decouple with an Interval Instead of event-driven refreshes, use a time-based interval. This "batches" multiple Routine Load commits into a single MV refresh.

sql
ALTER MATERIALIZED VIEW your_mv_name 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE); -- Or 5 minutes

Even a 1-minute interval reduces the refresh frequency by 6x compared to the default Routine Load commit rate. B. Use

excluded_trigger_tables

If you have multiple base tables and only want the MV to refresh when a specific (less frequent) table is updated, you can exclude the high-frequency Routine Load table from triggering the refresh:

sql
PROPERTIES (
  "excluded_trigger_tables" = "high_frequency_table_name"
)

C. Ensure Partitioned Refresh If you must maintain high frequency, ensure both the base table and the MV are partitioned on the same column (usually a time-based column). This ensures that each 10-second refresh only processes the latest partition rather than the entire dataset. D. Tune Routine Load If you want to stick with event-driven refreshes but reduce the frequency, you can increase the

max_batch_interval

or

max_batch_rows

in your Routine Load job to commit less often (e.g., every 30 or 60 seconds). Summary While StarRocks can handle the load, refreshing every 10 seconds (the default behavior for Routine Load + ASYNC MV) is generally not recommended for production environments unless the MV is extremely simple and low-latency is a hard requirement. Switching to an interval of 1 to 5 minutes is a much safer best practice. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md