http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> my MV refresh is failing with this ...
# questions-and-troubleshooting
c
@Rocky my MV refresh is failing with this message
Copy code
ERROR_CODE: -1
ERROR_MESSAGE: Refresh mv mv_trader_delivery_by_minute 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:2926)
 [wrapped] com.starrocks.common.StarRocksException: Backend node not found. Check if any backend node is down.
        at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3167)
        at com.starrocks.load.InsertOverwriteJobRunner.executeInsert(InsertOverwriteJobRunner.java:410)
        at com.starrocks.load.InsertOverwriteJobRunner.doLoad(InsertOverwriteJobRunner.java:179)
        at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:159)
        at com.starrocks.load.InsertOverwriteJobRunner.transferTo(InsertOverwriteJobRunner.java:225)
        at com.starrocks.load.InsertOverwriteJobRunner.prepare(InsertOverwriteJobRunner.java:278)
        at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:156)
        at com.starrocks.load.InsertOverwriteJobRunner.run(InsertOverwriteJobRunner.java:144)
        at com.starrocks.load.InsertOverwriteJobMgr.executeJob(InsertOverwriteJobMgr.java:84)
        at com.starrocks.qe.StmtExecutor.handleInsertOverwrite(StmtExecutor.java:2659)
        at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:2775)
        at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:2668)
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.refreshMaterializedView(PartitionBasedMvRefreshProcessor.java:1300)
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedView(PartitionBasedMvRefreshProcessor.java:548)
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedViewWithRetry(PartitionBasedMvRefreshProcessor.java:454)
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doMvRefresh(PartitionBasedMvRefreshProcessor.java:385)
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.processTaskRun(PartitionBasedMvRefreshProcessor.java:218)
        at com.starrocks.scheduler.TaskRun.doExecuteTaskRun(TaskRun.java:435)
        at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:395)
        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)
     PROGRESS: 0%
But all compute nodes report when I run 
SHOW COMPUTE NODES
and regular DML queries seem to work
r
Hello! I'm Rocky, and I'd be happy to help you troubleshoot your Materialized View (MV) refresh failure. The error message 
Backend node not found. Check if any backend node is down
during an MV refresh usually indicates that the Frontend (FE) cannot find an available worker node (BE or CN) to execute the 
INSERT OVERWRITE
operation that powers the refresh. Since your 
SHOW COMPUTE NODES
reports all nodes are up and regular DML queries work, this suggests a mismatch between your current session's view of the cluster and the Task Manager's view, or a resource-specific constraint. Here are the most likely causes and how to diagnose them: 1. Check if you are on the Leader FE MV refreshes are scheduled and executed by the Leader FE. If you are connected to a Follower FE, it's possible the Follower sees the nodes as up, but the Leader does not (due to networking issues or a known metadata inconsistency bug in some versions of StarRocks). * Action: Run 
SHOW FRONTENDS;
and check which node is the 
Leader
(or 
Master
). Connect your SQL client directly to the Leader FE's IP and check if 
SHOW COMPUTE NODES
still shows everything as healthy there. 2. Verify Resource Groups By default, asynchronous MV refreshes use a system-defined resource group called 
default_mv_wg
. If this group is misconfigured or if your MV is assigned to a custom resource group that has no available nodes, the refresh will fail. * Action: 1. Check which resource group the MV uses: 
SHOW CREATE MATERIALIZED VIEW mv_trader_delivery_by_minute;
(look for the 
"resource_group"
property). 2. Check the status of resource groups: 
SHOW RESOURCE GROUPS;
. 3. If the MV is using a custom group, ensure that group isn't restricted to specific nodes (via tags) that are currently unavailable. 3. Check for Blacklisted Nodes StarRocks sometimes automatically (or manually) adds nodes to a blacklist if they experience frequent connection failures or timeouts. * Action: Run these commands to see if any nodes are temporarily sidelined:
Copy code
sql
    SHOW BACKEND BLACKLIST;
    SHOW COMPUTE NODE BLACKLIST;
If any nodes appear here, they won't be used for new tasks like MV refreshes. 4. Check MV "Active" State Sometimes, if a base table undergoes a schema change or a previous refresh failed catastrophically, the MV is marked as Inactive. While the error message usually says "MV is inactive", it can sometimes manifest as scheduling errors. * Action: Run 
SHOW MATERIALIZED VIEWS LIKE 'mv_trader_delivery_by_minute'\G
and check the 
is_active
column. If it is 
false
, try to reactivate it:
Copy code
sql
    ALTER MATERIALIZED VIEW mv_trader_delivery_by_minute ACTIVE;
5. Warehouse Configuration (StarRocks 3.2+) If you are using the Multi-Warehouse feature, the MV refresh task might be assigned to a warehouse that is currently suspended or has no nodes assigned to it. * Action: Check if the MV is pinned to a specific warehouse in its properties and verify that warehouse is active with 
SHOW WAREHOUSES;
. 6. "Try lock failed: 0" The message 
try lock failed: 0
in your log often implies that the FE encountered an error while trying to acquire the internal refresh lock. The underlying reason provided is 
Backend node not found
. This reinforces that the planner failed to find a node to assign the work to before it could even proceed with the data processing. Summary Recommendation: The most common fix for this specific behavior (DML works but MV fails) is restarting the Leader FE or reactivating the MV. However, I recommend first connecting directly to the Leader FE to see if it has a different 
SHOW COMPUTE NODES
output than your current session. Let me know what you find from 
SHOW FRONTENDS
and 
SHOW COMPUTE NODES
on the leader-ES` on the leader! References
c
@Rocky restarting the leader FE (to move LEADER to a different node) fixed this situation and I was able to build my MV. However now it seems like queries that require a bitmap indexed field are very slow. Do I need to refresh the index?
r
I'm glad to hear that restarting the Leader FE resolved the "Backend node not found" issue! That often points to a temporary metadata inconsistency between the Leader and its followers. To answer your question: No, you do not need to manually "refresh" a bitmap index. In StarRocks, bitmap indexes are part of the physical data storage (segment files). When an asynchronous Materialized View (MV) is refreshed, it executes an 
INSERT OVERWRITE
operation. This process automatically builds the bitmap index for the new data as it is written to disk. However, if your queries are now slow on those indexed fields, there are three likely reasons and steps to fix them: 1. Stale Statistics (Most Likely) While the index is built during the refresh, the Query Optimizer needs updated statistics to decide whether using that index is actually faster than a full scan. If the FE Leader was just restarted or the MV was just created, the statistics might be missing or stale. * Fix: Manually trigger a statistics collection for the MV:
Copy code
sql
    ANALYZE TABLE mv_trader_delivery_by_minute;
* Tip: Check your session variable 
analyze_mv
. By default, it is set to 
sample
, which analyzes the MV after each refresh. If it was set to an empty string, StarRocks won't collect stats automatically. 2. Adaptive Index Selection StarRocks uses an "adaptive" mechanism to decide if a bitmap index should be used. It calculates a ratio: 
(distinct values in condition) / (total cardinality)
. If this ratio is higher than 
1/1000
(by default), StarRocks may skip the index because it determines a scan is more efficient. * Verify: Run 
EXPLAIN ANALYZE
on your slow query. Look for a metric called 
BitmapIndexFilterRows
or 
BitmapIndexFilter
. If it is 
0
or missing, the index is being ignored. * Fix: You can force the use of the index for testing by setting this in your session:
Copy code
sql
    SET bitmap_max_filter_ratio = 1000;
3. Verify the Index Exists on the MV Since you just built this MV, ensure the bitmap index was actually defined in the MV's schema. Bitmap indexes on base tables do not automatically carry over to an MV unless you explicitly define them in the MV's 
CREATE
statement or add them later. * Check:
Copy code
sql
    SHOW INDEX FROM mv_trader_delivery_by_minute;
* Fix: If it's missing, you can add it without rebuilding the whole MV:
Copy code
sql
    CREATE INDEX index_name ON mv_trader_delivery_by_minute (column_name) USING BITMAP;
Note: This is an asynchronous task. Monitor it with 
SHOW ALTER TABLE COLUMN;
. Summary Checklist 1. Run 
SHOW INDEX
to make sure the index is actually on the MV. 2. Run 
ANALYZE TABLE
to ensure the optimizer knows the index is useful. 3. Use 
EXPLAIN ANALYZE
to confirm if the query is actually hitting the index. If 
EXPLAIN ANALYZE
shows the index is being used but it's still slow, the column might have too many distinct values (high cardinality), which can sometimes make bitmap index loading overhead more expensive than the filtering benefit. 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 • administration/management/monitoring/metrics-materialized_view.md
c
@Rocky the indexed field is not included in the MV
r
That explains the slowness! If the indexed field is not included in the Materialized View (MV) definition, you are facing a two-fold performance issue: 1. The Query is likely not using the MV For the StarRocks optimizer to "transparently rewrite" a query to use an MV, the MV must contain all the columns required to satisfy the query, including those in the 
WHERE
clause. * Result: Since your filter column is missing from the MV, the optimizer is forced to query the original base table. Even with a bitmap index, scanning a massive base table is significantly slower than scanning a pre-aggregated or pre-joined MV. * How to verify: Run 
EXPLAIN <your_query>;
. If you don't see the name of your MV in the plan (look for 
OlapScanNode
and the table name), the query is hitting the base table. 2. Bitmap Indexes are not inherited Bitmap indexes are physical structures tied to a specific table or MV. If you created a bitmap index on the base table, it does not exist on the MV unless you explicitly create it there. ────────── The Solution To get the performance you're looking for, you need to follow these steps: Step 1: Redefine the MV to include the column You must add the column to the 
SELECT
list of your MV so the optimizer can use the MV to resolve the filter.
Copy code
sql
CREATE MATERIALIZED VIEW mv_trader_delivery_by_minute 
-- ... other properties ...
AS 
SELECT 
    trader_id, -- the field you want to filter on
    minute_time,
    SUM(delivery_amount)
FROM base_table
GROUP BY trader_id, minute_time;
Step 2: Create the Bitmap Index on the MV Once the MV is built and the column is present, you must manually create the bitmap index on the MV itself.
Copy code
sql
CREATE INDEX index_trader_id ON mv_trader_delivery_by_minute (trader_id) USING BITMAP;
Note: This is an asynchronous operation. Check progress with 
SHOW ALTER TABLE COLUMN;
. Step 3: Run ANALYZE After the index is finished, update the statistics so the optimizer knows the index is available and efficient:
Copy code
sql
ANALYZE TABLE mv_trader_delivery_by_minute;
Summary If you want to filter by a field and have it be fast: 1. Include it in the MV (so the query can be rewritten to the MV). 2. Index it on the MV (so the scan within the MV is accelerated). Without the column in the MV, you are essentially trying to use a "shortcut" (the MV) that doesn't have the "signpost" (the filter column) needed to reach your destination. References • table_design/indexes/Bitmap_index.md • knowledge/usage/best-practice.md
c
@Rocky to clarify: I am not "mixing" MV-included fields into the query that uses the bitmap indexed field. The field is "zip code", which would boost the MV cardinality too much to make it very effective. It is meant to be used in isolation, perhaps as a CTE
r
This is a classic performance trade-off in data modeling. If the "zip code" field is not in the Materialized View (MV), StarRocks cannot use the MV to answer your query directly. Instead, it must fall back to the base table, which is likely much larger, leading to the slowness you are seeing. Since you are using a CTE or manual join to keep the MV cardinality low, here is why it’s slow and how to architect around it: 1. The "Base Table Fallback" Problem If your query looks like this:
Copy code
sql
WITH traders_in_zip AS (
    SELECT trader_id FROM base_table WHERE zip_code = '90210' -- Uses Bitmap Index
)
SELECT minute_time, SUM(delivery)
FROM base_table -- <--- PROBLEM: This hits the raw fact table
WHERE trader_id IN (SELECT trader_id FROM traders_in_zip)
GROUP BY minute_time;
Even if the CTE is fast, the main body of the query is still scanning the 
base_table
to perform the aggregation. StarRocks cannot "jump" from the base table filter to the MV's pre-aggregated data if the link (the joining column) isn't in the MV. 2. Is Zip Code really a "Bloat" factor? You mentioned that including Zip Code would boost cardinality too much. This is only true if you aren't already grouping by 
trader_id
. * Case A: You already group by 
trader_id
: If each Trader ID belongs to exactly one Zip Code (a 1:N relationship), adding 
zip_code
to your MV's 
GROUP BY
list adds zero extra rows. It just adds one more column of metadata. * Case B: You aggregate by Time only: If your MV is just 
minute, sum(amount)
, then yes, adding 
zip_code
increases rows. Recommendation: If you frequently filter by Zip Code, the most performant approach is to include it in the MV. If it increases the row count by 5x but makes the query 100x faster than the base table, it is usually worth the disk space. 3. Why the Bitmap Index on the Base Table feels slow Bitmap indexes in StarRocks are highly effective for low-to-medium cardinality fields. For high-cardinality fields like Zip Code (where a single value might represent a tiny fraction of the data): * Loading Overhead: The BE has to load the bitmap for that specific value from disk. If the table is huge, this "seek and load" can be slow. * Bloom Filters: For high-cardinality equality filters (
zip_code = '12345'
), a Bloom Filter is often significantly faster than a Bitmap Index. _ _Action*: Try adding a Bloom Filter to the base table:
Copy code
sql
      ALTER TABLE base_table SET ("bloom_filter_columns" = "zip_code");
4. The "Dimension Table" Strategy (Best Practice) If you truly cannot put Zip Code in the MV, you should ensure you are joining the MV to a Dimension Table, not the Base Fact Table. 1. Create a small mapping table: 
trader_metadata (trader_id, zip_code)
. 2. Include 
trader_id
in your MV. 3. Query like this:
Copy code
sql
    SELECT mv.minute_time, SUM(mv.delivery_amount)
    FROM mv_trader_delivery_by_minute mv
    JOIN trader_metadata dim ON mv.trader_id = dim.trader_id
    WHERE dim.zip_code = '90210'
    GROUP BY mv.minute_time;
This allows StarRocks to use the tiny 
dim
table for the filter and the medium-sized 
mv
for the data, completely avoiding the massive 
base_table
. Summary Checklist 1. Check the Plan: Run 
EXPLAIN
on your query. If you see the name of your base table instead of the MV, the optimizer has abandoned the MV. 2. Verify N:1 Mapping: If one trader = one zip, just add 
zip_code
to the MV 
GROUP BY
. It won't bloat the row count. 3. Use Bloom Filters: If you must query the base table, a Bloom Filter is usually better than a Bitmap Index for high-cardinality fields. 4. Analyze: Ensure you have run 
ANALYZE TABLE <base_table>
so the optimizer knows how many rows the Zip Code filter will actually return. References
Open in Slack
PreviousNext
Powered by