Hi We upgraded our Starrocks cluster to 4.1 because we are very curious with the Incremental Materialized view feature. We are trying to create a basic MATERIALIZED VIEW with mode=INCREMENTAL and a large iceberg table (dwd_rest.levelplay.mediation_sdk_business_v3 - around 30B rows per day), but it doesn't work. We did multiple tests, sometime it stuck on PEDNING, sometimes we are getting this error, and in other it just takes forever without finish

Refresh mv XXX failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.DdlException: Backend node not found. Check if any backend node is down.

this is the view syntax. after create if im running SHOW CREATE - i see that a

__row_id__

is being added to the DISTRIBUTION BY and to the field definition. how can I debug it? am i doing something wrong? do you have example of INCREMENTAL MV on top of large iceberg tables? thanks view syntax:

CREATE MATERIALIZED VIEW levelplay.imv_on_sdk_events_only_test_1
PARTITION BY (`partition_date`,`partition_hour`)
REFRESH DEFERRED ASYNC EVERY(INTERVAL 30 MINUTE)
PROPERTIES (
"refresh_mode" = "INCREMENTAL",
  "replicated_storage" = "true",
  "partition_refresh_number" = "2",
  "session.new_planner_optimize_timeout" = "300000",
  "replication_num" = "1",
  "datacache.enable" = "true",
  "enable_async_write_back" = "false",
  "storage_volume" = "builtin_storage_volume",
  "warehouse" = "default_warehouse"
)
AS
  SELECT
    partition_date, partition_hour, platform,
    lp_publisher_application_id,
    ad_format, ad_network, lp_ad_unit_id, lp_ad_unit_name, banner_size_label,
    country, lp_segment_id, unity_gamer_id, ios_att_status, device_os_version,
    application_version, mediation_sdk_version, lp_publisher_ab_group,
    lp_group_name, lp_placement_name, lp_instance_id, ad_session_depth,
    publisher_bundle_id, publisher_store_id, connection_type,
    SUM(fulfillment_downsampling_factor) AS fulfillment_count,
    SUM(request_downsampling_factor) AS request_count,
    SUM(completion_downsampling_factor) AS completion_count
  FROM dwd_rest.levelplay.mediation_sdk_business_v3
  WHERE partition_date >= '2026-04-29'
group by     partition_date, partition_hour, platform,
    lp_publisher_application_id,
    ad_format, ad_network, lp_ad_unit_id, lp_ad_unit_name, banner_size_label,
    country, lp_segment_id, unity_gamer_id, ios_att_status, device_os_version,
    application_version, mediation_sdk_version, lp_publisher_ab_group,
    lp_group_name, lp_placement_name, lp_instance_id, ad_session_depth,
    publisher_bundle_id, publisher_store_id, connection_type

after creation - `show create table levelplay.imv_on_sdk_events_only_test_1`:

CREATE MATERIALIZED VIEW `imv_on_sdk_events_only_test_1` (`__ROW_ID__`, `partition_date`, `partition_hour`, `platform`, `lp_publisher_application_id`, `ad_format`, `ad_network`, `lp_ad_unit_id`, `lp_ad_unit_name`, `banner_size_label`, `country`, `lp_segment_id`, `unity_gamer_id`, `ios_att_status`, `device_os_version`, `application_version`, `mediation_sdk_version`, `lp_publisher_ab_group`, `lp_group_name`, `lp_placement_name`, `lp_instance_id`, `ad_session_depth`, `publisher_bundle_id`, `publisher_store_id`, `connection_type`, `fulfillment_count`, `request_count`, `completion_count`, `__AGG_STATE_sum(dwd_rest.levelplay.mediation_sdk_business_v3.fulfillment_downsampling_factor)`, `__AGG_STATE_sum(dwd_rest.levelplay.mediation_sdk_business_v3.request_downsampling_factor)`, `__AGG_STATE_sum(dwd_rest.levelplay.mediation_sdk_business_v3.completion_downsampling_factor)`)
COMMENT "MATERIALIZED_VIEW"
PARTITION BY (`partition_date`,`partition_hour`)
DISTRIBUTED BY HASH(`__ROW_ID__`)
REFRESH DEFERRED ASYNC EVERY(INTERVAL 30 MINUTE)
PROPERTIES (
"replicated_storage" = "true",
"partition_refresh_number" = "2",
"session.new_planner_optimize_timeout" = "300000",
"replication_num" = "1",
"refresh_mode" = "INCREMENTAL",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"storage_volume" = "builtin_storage_volume",
"warehouse" = "default_warehouse"
)
AS SELECT
    partition_date, partition_hour, platform,
    lp_publisher_application_id,
    ad_format, ad_network, lp_ad_unit_id, lp_ad_unit_name, banner_size_label,
    country, lp_segment_id, unity_gamer_id, ios_att_status, device_os_version,
    application_version, mediation_sdk_version, lp_publisher_ab_group,
    lp_group_name, lp_placement_name, lp_instance_id, ad_session_depth,
    publisher_bundle_id, publisher_store_id, connection_type,
    SUM(fulfillment_downsampling_factor) AS fulfillment_count,
    SUM(request_downsampling_factor) AS request_count,
    SUM(completion_downsampling_factor) AS completion_count
  FROM dwd_rest.levelplay.mediation_sdk_business_v3
  WHERE partition_date >= '2026-04-29'
group by     partition_date, partition_hour, platform,
    lp_publisher_application_id,
    ad_format, ad_network, lp_ad_unit_id, lp_ad_unit_name, banner_size_label,
    country, lp_segment_id, unity_gamer_id, ios_att_status, device_os_version,
    application_version, mediation_sdk_version, lp_publisher_ab_group,
    lp_group_name, lp_placement_name, lp_instance_id, ad_session_depth,
    publisher_bundle_id, publisher_store_id, connection_type;

same exact view but without the "refresh_mode" = "INCREMENTAL" is being refresh succefully (takes between 10-20 minutes to refresh 2 full partitions) would appreciate any help @Rocky

Hi there! It's great to see you're testing the new Incremental Materialized View (IVM) feature in StarRocks 4.1. This is a significant milestone for handling large-scale data lake refreshes more efficiently. The behavior you are seeing—specifically the addition of

*ROW_ID*

and the "Backend node not found" error—is related to how the new incremental engine (IVM) operates compared to the traditional Partition Change Tracking (PCT) mode. *1. Why is

*ROW_ID*

being added?* This is expected and required behavior for

refresh_mode = "INCREMENTAL"

. In StarRocks 4.1, the IVM engine uses a "differential" approach. Instead of refreshing entire partitions by re-calculating them (which is what happens in the default

PCT

mode), the incremental engine: * Identifies exactly which files/rows were added in Iceberg via snapshot metadata. * Uses a unique identifier to map those changes to the existing results in the MV. * For Iceberg, StarRocks automatically generates or maps an internal

*ROW_ID*

to track these row-level changes and use it as the distribution key to ensure that updates to the same "logical" row are routed to the same BE. 2. Debugging "Backend node not found" This error is usually a sign that a Backend (BE) process has crashed or is becoming unresponsive during the refresh. Since you are dealing with 30 billion rows per day, the initial build or a large refresh is likely hitting resource limits. How to investigate: 1. Check BE Logs: Check the

<http://be.INFO|be.INFO>

and

be.out

logs on your backend nodes. Look for "OOM killer" messages or "Segmentation fault." A query of this scale with 24

GROUP BY

columns creates a massive hash table in memory. 2. Check Task History: Run this query to see the specific failure reason for the refresh task:

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

Look at the

ERROR_MESSAGE

and

EXTRA_MESSAGE

columns. 3. Monitor FE Logs: The

fe.log

and

fe.warn.log

might show why the FE thinks the BE is "missing" (e.g., failed heartbeat or RPC timeout). 3. Why it might be "stuck" or taking forever When you first create the MV, even in

INCREMENTAL

mode, StarRocks must perform an initial full build of the data matching your

WHERE

clause. * Massive State: Grouping 30B rows by 24 columns is extremely heavy. In

INCREMENTAL

mode, StarRocks also has to maintain "state" columns (like the

__AGG_STATE_sum

columns you saw in the

SHOW CREATE

output). This metadata overhead makes the initial build heavier than a standard

PCT

refresh. * Resource Group: By default, MVs use the

default_mv_wg

resource group, which has low CPU priority. Recommendations for Large Iceberg Tables Since you're pushing the limits with 30B rows, try these steps: 1. Iterative Initial Build: Don't let the background scheduler try to build the whole thing at once. Manually refresh small chunks first:

sql
    -- Refresh just one hour or one day to test
    REFRESH MATERIALIZED VIEW levelplay.imv_on_sdk_events_only_test_1
    PARTITION (partition_date='2026-04-29', partition_hour='01');

2. Increase Memory/Spill: If the BEs are crashing due to memory, enable spilling to disk for the MV:

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

3. Check Version Stability: You mentioned StarRocks 4.1.0. Note that 4.1.0 had a known issue with BE stability in certain container environments (fixed in 4.1.1). If you are on 4.1.0, upgrading to 4.1.1+ is highly recommended. 4. Verify Iceberg Version: Incremental refresh is most stable on Iceberg V1 tables (Append-only). If your Iceberg table has deletes/updates (V2), it might cause the IVM refresh to fail or fall back to a full refresh.

i think i solved the OOM crash, but it still takes forever. and im using "partition_refresh_number" = "2" to let the view refresh only 2 partitions each time which should be OK

Hello, @Asaf Sneh! How can I assist you today?

@Rocky - Can you comment on this behavior and delay - what can be attempted to diagnose or speed this up? ====== i think i solved the OOM crash, but it still takes forever. and im using "partition_refresh_number" = "2" to let the view refresh only 2 partitions each time which should be OK

Refresh mv imv_on_sdk_events_only_test_1 failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.DdlException: Backend node not found. Check if any backend node is down.
	at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:100)
	at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3255)
 [wrapped] com.starrocks.common.StarRocksException: Backend node not found. Check if any backend node is down.
	at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3509)
	at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:2971)
	at com.starrocks.scheduler.MVTaskRunProcessor.executePlan(MVTaskRunProcessor.java:357)
	at com.starrocks.scheduler.mv.ivm.MVIVMBasedRefreshProcessor.execProcessExecPlan(MVIVMBasedRefreshProcessor.java:163)
	at com.starrocks.scheduler.MVTaskRunProcessor.doProcessTaskRun(MVTaskRunProcessor.java:324)
	at com.starrocks.scheduler.MVTaskRunProcessor.retryProcessTaskRun(MVTaskRunProcessor.java:282)
	at com.starrocks.scheduler.MVTaskRunProcessor.processTaskRun(MVTaskRunProcessor.java:194)
	at com.starrocks.scheduler.TaskRun.doExecuteTaskRun(TaskRun.java:459)
	at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:419)
	at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$1(TaskRunExecutor.java:66)
	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)

It is great to see you testing the new Incremental Materialized View (IVM) feature in StarRocks 4.1. Handling 30 billion rows per day is a high-scale use case, and what you are seeing is a combination of expected behavior for this new engine and some likely environmental/version issues. 1. Understanding the Error & Behavior * *`*ROW_ID*` is Expected:* In

refresh_mode = "INCREMENTAL"

, StarRocks must track row-level changes (deltas) from Iceberg. It automatically adds

*ROW_ID*

and uses it as the distribution key to ensure that updates/aggregations for a specific row are always routed to the same Backend (BE). This is a requirement for the IVM engine to maintain state correctly. * "Backend node not found": This specific error in the

MVIVMBasedRefreshProcessor

stack trace usually indicates that the Frontend (FE) lost track of a BE during the execution of the refresh plan. * Version Bug: If you are on StarRocks 4.1.0, there is a known stability issue in containerized/K8s environments that causes BEs to appear offline or crash under load. It is highly recommended to upgrade to 4.1.1+, which specifically addresses cluster stability in the 4.1 branch. * Heartbeat Timeout: With 30B rows, the FE might be overwhelmed by metadata or the BEs might be so busy with the heavy 24-column

GROUP BY

that they miss heartbeats. 2. Why it "Takes Forever" Even in incremental mode, the first refresh after creation is essentially a full build. * Massive State Maintenance: Unlike a standard MV that just stores the final result, an Incremental MV (IVM) stores the Aggregation State (the

__AGG_STATE_sum

columns you saw). This allows StarRocks to update the sum without reading the old data. Writing these state columns for 30B rows is significantly more I/O intensive than a standard

INSERT OVERWRITE

. * Default Resource Limits: By default, MV refreshes run in a low-priority resource group (

default_mv_wg

). For a table this size, the refresh will likely be throttled. 3. Diagnostic & Tuning Steps Step 1: Check the Task Progress Query the detailed task history to see if it's stuck in planning or execution:

sql
SELECT 
    query_id, 
    state, 
    error_message, 
    extra_message 
FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC LIMIT 5;

Look at the

extra_message

JSON. It contains a field called

refreshMode

. Ensure it actually says

INCREMENTAL

and check the

mvPartitionsToRefresh

list. Step 2: Increase Resource Allocation For a 30B row table, the default warehouse settings may be too restrictive. 1. Enable Spilling: This prevents "Backend node not found" crashes if memory peaks:

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

2. Increase Timeout: The default insert timeout might be too low for the initial build:

sql
    ALTER MATERIALIZED VIEW levelplay.imv_on_sdk_events_only_test_1
    SET ("session.insert_timeout" = "36000"); -- 10 hours

Step 3: Test with a Single Partition To verify the IVM logic is working without the overhead of the entire table, try a manual refresh for a very small time slice:

sql
REFRESH MATERIALIZED VIEW levelplay.imv_on_sdk_events_only_test_1 
PARTITION (partition_date='2026-04-29', partition_hour='01');

If this finishes quickly, the issue is purely the scale of the initial data volume. Step 4: Verify Iceberg Compatibility The IVM engine in 4.1 Phase 1 is optimized for Iceberg V1 (Append-only) tables. If your source table

mediation_sdk_business_v3

has frequent updates or deletes (V2), the IVM engine has to do much more work to "retract" old values, which can lead to the "forever" behavior. Summary Recommendation 1. Upgrade to 4.1.1+ immediately to fix the "Backend node not found" stability issue. 2. Enable Disk Spilling to handle the massive 24-column aggregate state.