http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> How to warmup mat view data in a cl...
# questions-and-troubleshooting
a
@Rocky How to warmup mat view data in a cluster?
r
To warm up materialized view (MV) data in a StarRocks cluster, you can use the Block Cache Warmup feature (introduced in v3.3) or execute manual queries to populate the system's cache. 1. Using 
CACHE SELECT
(StarRocks v3.3+) StarRocks provides the 
CACHE SELECT
syntax to proactively load data into the local block cache. This is especially useful for MVs in shared-data clusters or MVs that accelerate data lake queries. Syntax:
Copy code
sql
CACHE SELECT * FROM <mv_name> [WHERE <condition>];
* How it works: It fetches the data from the underlying storage (like S3 or remote disks) and populates the local BE (Backend) cache. * Synchronous: The command returns only after the warmup is complete, providing metrics like 
WRITE_CACHE_SIZE
and 
TOTAL_CACHE_USAGE
. * Automation: You can schedule this as a recurring task using `SUBMIT TASK`:
Copy code
sql
    SUBMIT TASK mv_warmup_task
    SCHEDULE EVERY (INTERVAL 1 DAY)
    AS CACHE SELECT * FROM my_mv;
2. Manual Query Warmup (Shared-Nothing / Older Versions) If you are on an older version or using a shared-nothing cluster, "warming up" typically refers to populating the OS page cache or StarRocks page cache. * Action: Run a 
SELECT *
query or a common aggregation query against the MV. * Tip: To minimize network overhead during a simple warmup, you can use 
SELECT count(*) FROM my_mv
or select only specific columns that are frequently queried. 3. Warmup After Refresh Since an asynchronous MV's data is only as fresh as its last refresh, you should ideally trigger the warmup immediately after the refresh task finishes. * Strategy: Combine the refresh and warmup in a single workflow.
Copy code
sql
    -- 1. Refresh the MV
    REFRESH MATERIALIZED VIEW my_mv WITH SYNC MODE;

    -- 2. Warm up the cache
    CACHE SELECT * FROM my_mv;
Key Considerations * Enable Block Cache: Before using 
CACHE SELECT
, ensure the Block Cache feature is enabled (
starlet_use_star_cache = true
in BE config). * Capacity: Ensure your BE cache disk space is larger than the MV data size; otherwise, the warmup will trigger eviction of older data (SLRU policy). * Resource Groups: For heavy warmup tasks, it is recommended to assign them to a specific Resource Group to avoid impacting concurrent production queries. References • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md
a
why it can be that after running "CACHE SELECT * FROM my_mv;" query like "SELECT sum(c1) FROM my_mv" still takes a lot of time, like it wasn't cached at all?
@Rocky why it can be that after running "CACHE SELECT * FROM my_mv;" query like "SELECT sum(c1) FROM my_mv" still takes a lot of time, like it wasn't cached at all?
r
If your query is still slow after running 
CACHE SELECT
, it is usually due to one of the following reasons. 
CACHE SELECT
warms up the Data Cache (Block Cache) on disk, which primarily eliminates remote I/O latency, but it does not eliminate the time needed for CPU computation or memory management. 1. The bottleneck is CPU, not I/O The Data Cache only speeds up the "Data Loading" phase. A query like 
SELECT sum(c1) FROM my_mv
still requires the Backend (BE) to: * Read the cached blocks from the local disk into memory. * Decompress the data. * Iterate and aggregate (sum) all values in column 
c1
. If your MV contains billions of rows, the CPU time required for the summation can be significant even if the data is already on the local disk. 2. The Cache was bypassed (I/O Adaptation) StarRocks has an "I/O Adaptation" feature enabled by default. If the local disk where the cache is stored is under high pressure (high latency or high IOPS), StarRocks may decide to read directly from the remote storage (S3/HDFS) to avoid waiting for the local disk. * How to check: Call the BE API 
http://<BE_IP>:<BE_HTTP_PORT>/api/datacache/app_stat
and look at 
block_cache_hit_rate
. * How to fix: You can try disabling this behavior for testing by setting:
Copy code
sql
    SET GLOBAL enable_datacache_io_adapter = false;
3. Data was evicted immediately The Block Cache uses an LRU-like policy. If your MV is larger than the allocated cache space (
starlet_star_cache_disk_size_percent
), or if other concurrent queries are loading different data, the warmed-up data might have been evicted by the time you ran your query. * Check: Run 
CACHE SELECT ... PROPERTIES("verbose" = "true")
and look at the 
TOTAL_CACHE_USAGE
. If it is close to 100%, your cache might be too small for the dataset. 4. Shared-Nothing vs. Shared-Data * Shared-Data Cluster / Data Lake: 
CACHE SELECT
is specifically designed for these. It populates the local disk cache. * Shared-Nothing (OLAP tables with replicas): In this mode, StarRocks relies more on the OS Page Cache (memory). 
CACHE SELECT
may not be as effective here because it focuses on the Block Cache (Data Cache) which is typically used for external tables or cloud-native tables. For Shared-Nothing, "warmup" is usually achieved by simply running the query once to populate the memory-based Page Cache. 5. Columnar Storage Efficiency If you ran 
CACHE SELECT _
, you warmed up _all* columns. However, your query 
SELECT sum(c1)
only needs one column. If the MV is very wide, you might have wasted cache space and I/O bandwidth warming up columns you don't need. Try warming up only the specific column:
Copy code
sql
CACHE SELECT c1 FROM my_mv;
Recommendation for Troubleshooting 1. Check Profile: Run your query and then check the Query Profile. Look for 
DataCacheMetrics
. If 
BlockCacheReadBytes
is high and 
RemoteReadBytes
is 0, the cache worked, and the slowness is due to computation. 2. Verify Metrics: Use the BE API mentioned above (
/api/datacache/app_stat
) to confirm the hit rate during your query execution. References • data_source/data_cache_troubleshooting.md • data_source/data_cache_observe.md • data_source/block_cache_warmup.md References • using_starrocks/caching/query_cache.md • faq/Sql_faq.md
a
@Rocky disabling "enable_datacache_io_adapter" didn't change anything. I still see behavior where CACHE SELECT * FROM my_mv populates cache (I see metrics are up for cache) but query like "SELECT sum(c1) FROM my_mv" still goes to remote storage for first execution and use cache only after.
r
The behavior you're seeing—where 
CACHE SELECT
populates the cache but the first subsequent query still hits remote storage—is often caused by one of three specific factors in StarRocks v3.3+: 1. The "No-Populate" Rules (v3.3.2+) Starting from v3.3.2, StarRocks introduced rules to prevent large "one-off" queries from evicting the entire cache. One of these rules is: * Queries that scan all columns of a table do not populate the cache. * Queries that scan all partitions do not populate the cache. If you ran 
CACHE SELECT * FROM my_mv
, the system might have marked this task as "non-populating" to protect the cache, especially if the MV has multiple partitions or many columns. Solution: Try warming up only the specific columns you need:
Copy code
sql
CACHE SELECT c1, c2 FROM my_mv;
Or force the population mode in your session:
Copy code
sql
SET populate_datacache_mode = 'always';
CACHE SELECT * FROM my_mv;
2. Asynchronous Population Delay By default, 
enable_datacache_async_populate_mode
is set to 
true
. Even though the 
CACHE SELECT
command is synchronous (it waits for the scan to finish), the actual writing of data blocks to the physical disk cache happens in background threads. If you run your query immediately after the warmup, the data might still be in the write queue and not yet readable from the local disk cache. Solution: Disable async population for the warmup task to ensure data is strictly written before the command returns:
Copy code
sql
SET enable_datacache_async_populate_mode = false;
CACHE SELECT * FROM my_mv;
3. Native Tables vs. External Tables There is a critical distinction based on your cluster architecture: * Shared-Data (Cloud-Native): Data Cache (Block Cache) is used for native tables. 
CACHE SELECT
works as expected. * Shared-Nothing (Traditional): Data Cache (Block Cache) is only used for External Tables (Data Lake). Native StarRocks tables (including Async MVs) use the Page Cache (memory) instead. If you are in a Shared-Nothing cluster, 
CACHE SELECT
will not affect your native MV data. To "warm up" a native MV in Shared-Nothing, you must run a standard query (e.g., 
SELECT count(*) FROM my_mv
) to pull data into the OS and StarRocks Page Cache. How to Verify Run your query and then execute 
GET TYPE query_profile
. Look for the 
DataCacheMetrics
 section: _ If 
DataCacheWriteBytes
is high during your _query*, it means the query missed the cache and is currently populating it itself. * Check for 
dataCacheOptions={populate: false}
in the plan. If you see this, the "No-Populate" rules mentioned in point #1 are active. Summary Checklist 1. Check Version: If v3.3.2+, use specific columns in 
CACHE SELECT
instead of 
*
. 2. Sync Write: Set 
SET enable_datacache_async_populate_mode = false;
before running 
CACHE SELECT
. 3. IO Adapter: Keep 
SET enable_datacache_io_adapter = false;
if your cache disks are slow, as this prevents "falling back" to remote storage. References • data_source/data_cache_troubleshooting.md • data_source/block_cache_warmup.md • data_source/data_cache.md References • data_source/data_cache.md • using_starrocks/caching/query_cache.md
a
@Rocky I did
Copy code
SET GLOBAL enable_datacache_async_populate_mode = false;
SET GLOBAL enable_datacache_io_adaptor = false;
SET Global populate_datacache_mode = 'always';
but I still see that cache select doesn't help for one of my mv. even though seems fine for different MV..
r
The behavior you are describing—where 
CACHE SELECT
confirms data is written to the cache, but the first query execution still shows remote I/O—is a known edge case usually tied to Metadata/Footer caching or Tablet distribution. Since you have already set the "heavy-hitter" variables (
always
population, sync mode, and disabling the IO adaptor), here are the remaining reasons why this specific MV might be ignoring your warmed-up cache: 1. Metadata & Footer Cache Miss 
CACHE SELECT
is highly effective at warming up the data blocks, but for columnar formats like Parquet or ORC (which StarRocks uses internally for MVs), the query engine must first read the File Footer to understand the schema and block locations. * The Issue: Footers are often stored in the Page Cache (memory) or a separate metadata cache. If 
CACHE SELECT
didn't effectively pin the footer in memory on the specific BE node that handles the subsequent query's coordinator/scan tasks, the first query will perform a remote "head" request to fetch that metadata. * Solution: Before your main query, run a "schema-only" warmup:
Copy code
sql
    SELECT * FROM my_mv LIMIT 0;
    -- Or a simple count to ensure metadata is loaded
    SELECT count(*) FROM my_mv;
2. Tablet-to-BE Mapping Inconsistency Block Cache is local to each BE. In a StarRocks cluster, tablets (shards) are distributed across BEs. * The Issue: If your 
CACHE SELECT
was executed by a session that was routed to one set of BEs, but your subsequent 
SELECT sum(c1)
query is scheduled (by the FE) on a different set of BE nodes (e.g., due to a different 
consistent_hash
or leader change), the new BEs will have empty local caches for those tablets. * Why it happens for only one MV: This is common if that specific MV has a different Bucket/Distribution key or a different Number of Buckets than your other MVs, causing the tablets to be spread differently or causing the FE to choose different "preferred" nodes for the scan. * Verification: Run 
EXPLAIN VERBOSE SELECT sum(c1) FROM my_mv
and look at the 
SCAN
node to see which BE IPs are assigned. Compare this with the BE IPs shown in 
CACHE SELECT ... PROPERTIES("verbose" = "true")
. 3. The 
datacache.partition_duration
Property Check if this specific MV (or its base table) was created with a specific cache duration property. * The Issue: If the MV has a property like 
PROPERTIES("datacache.partition_duration" = "7 days")
and the data you are querying is older than 7 days, StarRocks will deliberately bypass the cache and read from remote storage to avoid "polluting" the cache with cold data, even if you manually forced it in. * Check: Run 
SHOW CREATE MATERIALIZED VIEW my_mv;
and look for any 
datacache
related properties. 4. Query Rewrite vs. Direct Scan Is your query 
SELECT sum(c1) FROM my_mv
actually hitting the MV, or is the Optimizer rewriting it to hit the base table? * The Issue: If the first query triggers a complex rewrite logic, the "first-time" latency might be the Optimizer's overhead or the Optimizer deciding to hit the base table (which isn't cached) instead of the MV. * Verification: Run 
EXPLAIN SELECT sum(c1) FROM my_mv;
. If the plan shows 
SCAN [base_table_name]
instead of 
SCAN [my_mv]
, your warmup on the MV was ignored because the query isn't using it. Recommended Troubleshooting Steps: 1. Run with Verbose Property: Use 
CACHE SELECT ... PROPERTIES("verbose" = "true")
to confirm exactly which BEs are holding the data. 2. Compare Query Profiles: Capture the profile of the "slow" first run and the "fast" second run: * Look for 
CompressedBytesReadRemote
. If it's non-zero in the first run, look at 
DataCacheReadBytes
. * If 
DataCacheReadBytes
is 0, the cache was bypassed. If it is $>0$ but remote is also $>0$, the cache is partial.
3. Check for "No-Populate" Plan: Check the Profile for 
dataCacheOptions={populate: false}
. If you see this despite your 
GLOBAL
setting, try setting it at the Session level right before the query:
Copy code
sql
    SET populate_datacache_mode = 'always';
    SELECT sum(c1) FROM my_mv;
References • data_source/data_cache_troubleshooting.md • data_source/data_cache.md • using_starrocks/async_mv/materialized_view_task_run_details.md • faq/shared_data_faq.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
a
@Rocky another issue, I run single CN cluster. I restrated the CN node and removed cache, so it is fresh. I have LV that joins 3 mat views. I did prewarm all materialized views from the LV, but it still doesn't use the cache. In profile I see. "MissingInstanceIds" but CN didn't restart
r
The behavior you are describing, especially the presence of 
MissingInstanceIds
in the profile after a CN restart, suggests a mismatch between the Frontend's (FE) query plan and the actual execution state of your Single CN cluster. Here is why your cache pre-warming might be failing to show results: 1. The "MissingInstanceIds" Clue (Zombied Nodes) In a single-node setup, 
MissingInstanceIds
typically means the FE (Coordinator) scheduled query fragments to a specific node but never received the execution metrics back. * The Cause: When you restarted your CN, did its IP address or Port change (common in K8s/Docker)? Or does the FE still see the "old" CN as a dead node? * The Impact: If the FE thinks there are two nodes (one dead, one alive), it may still be trying to assign some tablets (shards) to the dead node. This results in the FE waiting for RPC timeouts, causing the "long time" you see, and the profile reports "MissingInstanceIds" for the fragments that were sent to the non-existent node. * Check: Run 
SHOW COMPUTE NODES;
(or 
SHOW BACKENDS;
). If you see more than one entry, or if the 
LastStartTime
doesn't match your recent restart, you must manually drop the old node:
Copy code
sql
    ALTER SYSTEM DROP COMPUTE NODE "IP:PORT";
2. Metadata & Footer Cache Miss Even if you ran 
CACHE SELECT *
to warm up the data blocks, a query joining 3 MVs requires the File Footers and Table/Partition Metadata to be in memory. * After a restart, the FE's metadata cache is empty. * The first query execution must perform several "Head" requests to remote storage to fetch the file schemas for all 3 MVs before it can even start reading the data blocks you cached. * Solution: For the first query after a restart, run a "Metadata Warmup":
Copy code
sql
    -- This forces the FE to load the file list and footers into memory
    SELECT count(*) FROM mv1;
    SELECT count(*) FROM mv2;
    SELECT count(*) FROM mv3;
3. MV Status & Query Rewrite If you are querying a Logical View that references the MVs, ensure the MVs are still in an ACTIVE state. * If an MV becomes 
INACTIVE
(common after schema changes or metadata issues), the optimizer might bypass it and hit the base tables instead. * Check: 
SHOW MATERIALIZED VIEWS;
and check the 
IsActive
column. * Check Plan: Run 
EXPLAIN <your_query>;
. Look at the 
SCAN
nodes. Are they scanning the MVs or the original base tables? If they are scanning the base tables, your 
CACHE SELECT
on the MVs is being ignored. 4. Data Cache vs. Query Cache 
CACHE SELECT
warms up the Data Cache (blocks on disk). It does not cache the result of the Join. * Joining 3 MVs is a CPU-intensive operation. Even if the data is read instantly from the local disk cache, the BE still needs to perform the Hash Joins in memory. * If your MVs are large, the "lot of time" you are seeing might be the Join Computation, not the Data Loading. * Verify: Look at the 
IOTimeRemote
vs 
DataCacheReadBytes
in the profile. If 
IOTimeRemote
is 0 and 
DataCacheReadBytes
is high, the cache worked, and your bottleneck is simply the SQL logic itself. Summary Checklist: 1. Clean up Nodes: 
SHOW COMPUTE NODES
and remove any dead ones. 2. Verify Scan Target: Use 
EXPLAIN
to confirm the query is actually scanning the MVs you warmed up. 3. Check Metrics: In the profile, find the 
SCAN
node for each MV. Check if 
DataCacheReadBytes > 0
. If it is 0, the cache was bypassed (likely due to the 
populate_datacache_mode
or metadata issues mentioned in previous steps). 4. Wait for Metadata: Run a simple 
SELECT * FROM mv LIMIT 1
for each MV to ensure footers are cached before running the heavy join. References • using_starrocks/caching/block_cache.md • data_source/data_cache_troubleshooting.md
a
@Rocky if "enable_datacache_io_adaptor" is set to true, is there any metrics (like prometheus metrics) that can tell if remote storage was used and not cache?
r
When the 
enable_datacache_io_adaptor
is set to 
true
, StarRocks does not provide a single specific "IO_ADAPTOR_BYPASS" metric. Instead, the behavior is observed through general cache hit/miss metrics and disk latency indicators. If the IO Adaptor triggers (due to high disk latency), it routes requests to remote storage, which effectively appears as a cache miss in the metrics. 1. Prometheus / BE Metrics You can monitor the following metrics on your BE/CN nodes (usually at 
:8040/metrics
): * 
starrocks_be_block_cache_miss_bytes
: This is the most direct indicator. When the IO Adaptor decides to skip the local disk cache because of high latency, the bytes read from remote storage are counted as "misses." * 
starrocks_be_block_cache_hit_bytes
: If this value stays low while you know the data should be cached, it suggests the IO Adaptor (or another rule) is bypassing the local disks. * 
starrocks_be_block_cache_hit_rate
: A significant drop in the hit rate during high system load often indicates the IO Adaptor is shedding load to remote storage. * Disk Latency Metrics: Since the IO Adaptor triggers based on disk pressure, monitor: * 
starrocks_be_fslib_read_io_latency
(v3.3+): Specifically tracks the latency of the data cache disks. * If this latency spikes, the IO Adaptor will likely begin bypassing the cache. 2. Query Profile Metrics For a specific query, check the Scan Node section in the Query Profile: * 
DataCacheReadBytes
: Bytes read from the local cache. * 
BytesRead
: Total bytes read (Local + Remote). * Interpretation: If 
BytesRead
is significantly higher than 
DataCacheReadBytes
for a query that you've already "warmed up," and your disks are showing high utilization, the IO Adaptor is likely the cause. 3. BE API Interface You can get a JSON summary of these statistics by calling the BE's HTTP interface:
Copy code
bash
curl http://{BE_IP}:{BE_HTTP_PORT}/api/datacache/app_stat
Look for 
block_cache_miss_bytes
. If this increments during a "hot" query execution while the IO Adaptor is enabled, it confirms remote storage is being used. How to Confirm the IO Adaptor is the Cause If you suspect the IO Adaptor is bypassing your cache unnecessarily: 1. Toggle the variable: Run the query with 
SET enable_datacache_io_adaptor = false;
. 2. Compare Profiles: If the 
DataCacheReadBytes
increases and the query actually runs faster (despite higher disk latency), then your disks are the bottleneck, but the IO Adaptor might be being too aggressive. If it runs slower, the IO Adaptor is doing its job by avoiding disk tail-latency. References • data_source/data_cache_observe.md • data_source/data_cache.md • administration/management/monitoring/metrics.md References • data_source/data_cache.md • data_source/data_cache_observe.md • administration/management/monitoring/metrics.md
a
@Rocky is there any known issue in 4.0.9 that can explain why "cache select" may not warmup MV data?
r
In version 4.0.9 (and the 4.0.x series), there aren't many "regressions" specific to 
CACHE SELECT
, but there are several architectural behaviors and a few recently fixed bugs that explain why your MV warmup is failing in a single CN setup. 1. The "MissingInstanceIds" and FE-CN Sync Issue The error 
MissingInstanceIds
in the query profile is the most telling clue. In StarRocks, this happens when the Frontend (FE) attempts to send a query fragment to a node that it believes is "Alive," but the node either doesn't exist, is unreachable, or has a different identity (IP/Port/ID) after the restart. * The Problem: If you have "Ghost" nodes in your cluster (old registrations of the CN from before the restart), the FE may be splitting the scan ranges between the "Live" CN and the "Ghost" CN. * The Result: The fragments sent to the "Live" CN use the cache, but the fragments sent to the "Ghost" CN fail or time out, leading to 
MissingInstanceIds
and forcing the FE to potentially re-route or fall back to remote reads, bypassing the local cache. * Fix: Run 
SHOW COMPUTE NODES;
and ensure there is only one entry. If there are duplicates or dead nodes, run 
ALTER SYSTEM DROP COMPUTE NODE "host:port";
. 2. Known 4.0.x Bug: Deadlock during Metadata Warmup (#71459) StarRocks 4.0 introduced more aggressive metadata caching. A known issue in early 4.0.x versions (including 4.0.8/4.0.9) involved a deadlock when warming up the LRU cache for rowset metadata while the cache was full. * If your 
CACHE SELECT
triggered this condition, the warmup might have completed "successfully" in the UI but failed to actually pin the metadata needed for the subsequent query. Without the metadata (footers/indexes) being cached, the query must go to remote storage first, making the data cache feel ineffective. 3. MV Query Rewrite Bypassing the Cache Since you are using a Logical View (LV) that joins 3 Materialized Views (MVs), there is a high probability that the Optimizer is doing one of two things: 1. Rewriting to Base Tables: The Optimizer might decide it's "cheaper" to hit the base tables instead of the MVs. If you warmed up the MVs but the query hits the base tables, you'll see 100% remote I/O. 2. Rewrite Failure: If the MV is not 
ACTIVE
or if there's a version mismatch after the CN restart, the LV will default to the base tables. * Check: Run 
EXPLAIN SELECT ... FROM your_lv
and confirm the 
SCAN
nodes are actually pointing to your Materialized Views. 4. Logic Change in 4.0: 
INSERT INTO BLACKHOLE
 In 4.0.9, 
CACHE SELECT
is implemented as an 
INSERT INTO BLACKHOLE
operation. This means it follows the exact same path as a regular query. * If your MV has Row-level Security or Session-specific variables that weren't set during the 
CACHE SELECT
but are present in the final query, the "Scan Ranges" might be different. The Block Cache is extremely sensitive to the exact file offsets; if the query plan changes even slightly, it might miss the cached blocks. Summary of what to do: 1. Clean the Node List: Check 
SHOW COMPUTE NODES
and drop any dead nodes to fix 
MissingInstanceIds
. 2. Verify Scan Target: Use 
EXPLAIN
to ensure the LV is actually using the MVs you warmed up. 3. Warm up Metadata: Run 
SELECT count(*)
on each of the 3 MVs once after the CN restart. This ensures the FE has the file metadata in memory before the join starts. 4. Check MV Active State: Ensure 
SHOW MATERIALIZED VIEWS
shows 
IsActive = true
for all three. If they are inactive, the cache select will not help the MV won't help the LV query. References • data_source/block_cache_warmup.md • data_source/data_cache_troubleshooting.md • release_notes/release-4.0.md
a
@Rocky I dropped all nodes, added new one with empty cache. I ran 
cache select * from mv
for all MVs I have. but I still see "MissingInstanceIds" when query LV, what can be the reason?
r
The presence of 
MissingInstanceIds
in your query profile—especially after dropping and re-adding nodes in a single CN cluster—strongly suggests a node identity and metadata synchronization issue between the Frontend (FE) and the new Compute Node (CN). Even though you see the CN as "Alive," the FE might be struggling with the transition from the "old" nodes to the "new" one. Here is why your cache warming is seemingly ignored and why the profile is reporting missing data: 1. The Node Identity "Zombie" Issue When you drop all nodes and add a new one, the FE assigns a unique Internal ID (e.g., 10001) to the new node. * The Problem: If you didn't restart the FE, it may still have cached query plans or tablet-to-node mappings from the "old" nodes. * MissingInstanceIds: This error occurs when the FE schedules pieces of your query (fragments) to nodes it thinks should exist. If it tries to send a join fragment to an old ID that was dropped, that fragment never executes. The profile then reports "MissingInstanceIds" because it never received metrics back for that part of the plan. * Action: Run 
SHOW COMPUTE NODES;
and verify there is exactly one row. If you see duplicates or "dead" entries, drop them. If the issue persists, restart the FE to force it to clear its internal node and plan caches. 2. Why 
CACHE SELECT
might be "failing" to warm up the LV A Logical View (LV) joining 3 Materialized Views is much more complex than a simple 
CACHE SELECT *
. * Metadata vs. Data: 
CACHE SELECT
warms up the Block Cache (the raw data on disk). However, for a 3-way join, the FE needs the Metadata Cache (file footers, row group indexes, and schema info) for all three MVs. * If you restarted the CN and cleared the cache, the FE's metadata for those files is gone. The first query will spend a significant amount of time performing "Head" requests to remote storage just to understand the file structures, regardless of whether the data blocks are on the CN's disk. * Action: Run 
SELECT count(*) FROM mv_name
for each of the 3 MVs. This forces the FE to load the metadata into memory before you attempt the heavy Join. 3. Optimizer "Bypass" (MV Query Rewrite) It is possible that when you query the Logical View, the Optimizer is not using the Materialized Views you just warmed up. * The Reason: If the MVs are stale (need refresh) or if the Optimizer's "cost model" thinks hitting the base tables is better, it will bypass the MVs entirely. Since you didn't warm up the base tables, the query is slow and hits remote storage. * Check: Run 
EXPLAIN SELECT ... FROM your_logical_view;
. * Look at the 
OlapScanNode
or 
ConnectorScanNode
. * Does the 
table:
field show the name of your Materialized Views or the Base Tables? If it shows the base tables, your 
CACHE SELECT
on the MVs was useless for this query. 4. Fragment Crash (OOM) In a single CN cluster, joining 3 MVs can be memory-intensive. * The Symptom: If the CN hits its memory limit during the join, it might kill a specific query fragment. The FE detects the failure, cancels the query, and produces a profile where some fragment instances are "Missing" because they were killed before they could report metrics. * Check: Look at the CN's 
<http://be.INFO|be.INFO>
or 
be.WARNING
logs (found in the 
log/
directory) for "Memory Limit Exceeded" or "OOM" messages around the time of the query. Recommended Steps: 1. Clean Node Registry: Run 
SHOW COMPUTE NODES;
. If there is any node other than your one active CN, drop it. 2. Verify Rewrite: Run 
EXPLAIN
on the LV query. Ensure the 
ScanNode
is actually hitting the MVs. 3. Warmup Metadata: Run 
SELECT * FROM mv LIMIT 1
for all 3 MVs to ensure footers are in the cache.
4. Check I/O Adaptor: If you have 
enable_datacache_io_adaptor = true
, turn it off temporarily (
SET enable_datacache_io_adaptor = false;
) to ensure the system isn't "helping" you by bypassing the local disk cache during the high-load join. References • administration/management/FE_configuration.md • data_source/data_cache_troubleshooting.md • best_practices/query_tuning/query_profile_operator_metrics.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • data_source/block_cache_warmup.md _warmup) References • administration/Meta_recovery.md • faq/Deploy_faq.md • release_notes/release-4.0.md
Open in Slack
PreviousNext
Powered by