http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> why does my materialized view take ...
# questions-and-troubleshooting
t
@Rocky why does my materialized view take a long time to create when the query used to create it executes and returns in 4 seconds?
r
The difference in performance between running a query and creating a Materialized View (MV) is usually due to the fact that running a query only involves data retrieval, while creating an MV involves a full data ingestion and persistence cycle. Here are the primary reasons why your MV creation is taking longer: 1. Data Writing vs. Streaming When you run a standard 
SELECT
query, StarRocks streams the results to your client as they are processed. In contrast, creating an MV executes an 
INSERT OVERWRITE
operation. This requires: * Data Persistence: Writing the entire result set to disk across the cluster. * Replication: By default, StarRocks creates three replicas of the data. Writing and synchronizing these replicas across different BE (Backend) nodes adds significant overhead compared to a simple read. 2. Indexing and Sorting When StarRocks writes data into an MV, it doesn't just store the raw rows. It organizes the data based on the MV's schema: * Sorting: Data is sorted according to the 
DUPLICATE/AGGREGATE
key or the 
DISTRIBUTED BY
key. * Index Building: StarRocks builds internal indexes (like the Zone Map, or user-defined Bitmap/Bloom Filter indexes) to accelerate future queries. This compute-intensive process happens during the creation phase. 3. Task Queueing and Management Async MVs are managed as background tasks. * Queueing Time: The task might be waiting for available resources in the internal task queue. You can check if there was a delay between the task's creation and its actual start by querying the 
information_schema.task_runs
table:
Copy code
sql
    SELECT query_id, create_time, start_time, finish_time, status
    FROM information_schema.task_runs
    WHERE task_name = 'mv_name_here'
    ORDER BY create_time DESC;
* Transaction Overhead: MV creation happens within a transaction to ensure atomicity. Managing this transaction and committing the metadata for a large number of tablets can take more time than a simple query. 4. Memory and Spilling A query that returns in 4 seconds might fit entirely in memory during execution. However, the 
INSERT OVERWRITE
process for an MV might have different memory requirements. If the refresh task hits memory limits, it may spill to disk, which significantly slows down the process. * You can check the profile of the refresh task to see if spilling occurred by finding the 
QueryId
from 
task_runs
and using:
Copy code
sql
    GET_QUERY_PROFILE('your_query_id');
Recommended Next Steps * Check the Task Profile: Use 
ANALYZE PROFILE
or 
GET_QUERY_PROFILE
on the refresh task's 
query_id
to see exactly which operator (e.g., 
OlapTableSink
) is taking the most time. * Partitioning: If your data is large, ensure the MV is partitioned. This allows StarRocks to refresh only the changed partitions in the future rather than the entire dataset. entire dataset. * Check Resources: Use 
SHOW PROC '/current_queries'
while the MV is being created to monitor its real-time 
ScanBytes
and 
MemoryUsage
. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md
t
@Rocky no queries are even running
r
If you see "no queries running" while creating an asynchronous Materialized View (MV), it is likely because the data population phase has been decoupled into a background task. Unlike a standard query, an asynchronous MV creation doesn't always execute the query immediately in your current session. Instead, it submits a task to the StarRocks Task Manager. Here is how to find where the work is actually happening: 1. Check the Task Status The "query" part of an MV refresh is managed as a task. Even if 
SHOW PROCESSLIST
or 
current_queries
is empty, the task might be in a 
PENDING
state (waiting for resources) or already 
FAILED
. Run this to see the background status:
Copy code
sql
SELECT * FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' -- StarRocks names MV tasks 'mv-<id>'
ORDER BY create_time DESC 
LIMIT 5;
* PENDING: The task is in the queue. It won't show up in "running queries" yet because it hasn't been allocated a execution slot. * RUNNING: It should appear in 
current_queries
now. If it doesn't, check if you are looking at the correct FE (use 
show proc '/global_current_queries'
). * FAILED: Check the 
error_message
column to see why it stopped. 2. Common Reasons for "No Queries Running" * Task Queue Limits: StarRocks limits the number of concurrent background tasks (default 
max_concurrent_task_runs
is 10). If other MVs are refreshing or tasks are running, your new MV will sit in 
PENDING
. * Resource Group Constraints: If you assigned the MV to a specific Resource Group, it might be throttled or waiting for a "slot" if the group's concurrency limit is reached. * Initial Refresh Delay: By default, an 
ASYNC
MV triggers an initial refresh immediately, but if the cluster is under heavy load, the Task Manager might delay the start. * Metadata Only: If you created the MV with 
REFRESH MANUAL
, it will not run any query at all until you manually trigger it with 
REFRESH MATERIALIZED VIEW <mv_name>
. 3. How to Monitor the "Hidden" Query To see the actual execution details of an active refresh: 1. Get the 
QUERY_ID
from the 
information_schema.task_runs
query above. 2. Use that ID to inspect the profile:
Copy code
sql
    GET_QUERY_PROFILE('your_query_id_here');
Summary of Commands Goal | Command Check MV State | 
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name'\G
Check Task Queue | 
SELECT * FROM information_schema.task_runs
Check Global Queries | 
SHOW PROC '/global_current_queries'
If 
last_refresh_state
in 
SHOW MATERIALIZED VIEWS
is 
PENDING
, the system is simply waiting for a background worker to become available to start the 4-second query you expect. References • using_starrocks/running_queries.md • administration/management/resource_management/query_queues.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md
t
@Rocky Unexpected input 'GET_QUERY_PROFILE'
r
Apologies for the syntax error. 
get_query_profile
is a SQL function, so it must be used within a 
SELECT
statement. If you are running this in a CLI or editor, use the following syntax:
Copy code
sql
-- Use SELECT to call the function
SELECT get_query_profile('your_query_id_here')\G
Or, for a more readable tree-style output (available in StarRocks 3.1+):
Copy code
sql
ANALYZE PROFILE FROM 'your_query_id_here';
If "No Queries are Running" If you don't see any queries in 
SHOW PROCESSLIST
or 
information_schema.task_runs
, it usually means the refresh hasn't actually started yet. Here is how to find the specific bottleneck: 1. Find the Task ID Even if no query is running, the Task should exist. Check the history of the MV tasks:
Copy code
sql
SELECT task_name, create_time, start_time, state, error_message 
FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC;
* If State is `PENDING`: The task is stuck in the queue. This happens if the cluster is at its concurrency limit or if another heavy background job is running. * If State is `FAILED`: Check the 
error_message
. It might have failed instantly due to permission issues or metadata errors. 2. Find the Query ID If the task state is 
RUNNING
but you don't see it in your current session's processlist, it's running on a different FE or in the background. Get the 
query_id
from the 
information_schema.task_runs
table (the 
query_id
column). If you can't find it there, use:
Copy code
sql
SHOW PROFILELIST;
This will show all recent queries across the cluster, including those triggered by the background task manager. Look for an 
INSERT
or 
SELECT
statement related to your MV. Why it feels "Stuck" If the original query takes 4 seconds but the MV creation takes minutes without showing a "Running" query: 1. FE Metadata Lock: The FE might be waiting for a metadata lock to begin the 
INSERT OVERWRITE
operation. 2. Schema Change/Validation: StarRocks performs a strictly validated schema check before launching the data job. 3. Resource Group Throttling: If you have Resource Groups enabled, the background task might be assigned to a low-priority group that is waiting for a CPU/Memory slot. Recommendation: Run 
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name'\G
and check the 
last_refresh_error
and 
last_refresh_state
fields. This is often the fastest way to see why it hasn't started.t it hasn't progressed. References • best_practices/query_tuning/query_profile_overview.md • sql-reference/sql-statements/cluster-management/plan_profile/ANALYZE_PROFILE.md • using_starrocks/async_mv/materialized_view_task_run_details.md References • best_practices/query_tuning/query_profile_overview.md • best_practices/query_tuning/query_profile_text_based_analysis.md • sql-reference/sql-statements/cluster-management/plan_profile/ANALYZE_PROFILE.md • best_practices/query_tuning/query_profile_operator_metrics.md
t
@Rocky ERROR 1064 (HY000): query not exists I can't find the query anywhere, it says the refresh is running
r
The "query not exists" error combined with "no queries running" usually means the Materialized View (MV) task is stuck in the metadata preparation or locking phase before the actual SQL query is even launched. Here is why you can’t find the query and how to pinpoint the bottleneck: 1. The Task is "Running," but the Query hasn't started In StarRocks, an MV refresh is a two-step process: 1. Task Manager Phase: The FE (Frontend) calculates which partitions need refreshing, checks base table versions, and acquires necessary locks. During this time, 
SHOW MATERIALIZED VIEWS
will show 
RUNNING
, but no physical query exists yet. 2. Execution Phase: Once preparation is done, the FE submits an 
INSERT OVERWRITE
query. Only at this point does a 
QUERY_ID
appear and become visible in 
SHOW PROCESSLIST
. To verify this, run:
Copy code
sql
SELECT query_id, state, extra_message 
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 1;
* If 
query_id
is empty/null: The FE is still calculating the refresh plan (likely stuck on metadata or waiting for a lock). * If 
query_id
exists but you get "not exists": You are likely connected to a different FE than the one executing the task. 2. The "Multi-FE" Problem If you have multiple FEs, the query profile and the active query status are local to the FE that started the task. * Check which FE is the "Leader" using 
SHOW FRONTENDS
. * Connect directly to the Leader FE to run your 
get_query_profile
or 
SHOW PROCESSLIST
. * Alternatively, use 
SHOW PROC '/global_current_queries'
(StarRocks 3.4+) to see queries across the entire cluster. 3. Metadata Locking If your base tables are being written to constantly (e.g., via Routine Load or Flink), the MV refresh might be stuck waiting for a Metadata Lock. * Check for locks by running: 
SHOW PROC '/complain'
. (This is an internal diagnostic tool that sometimes shows blocked tasks). * Check the FE leader log (
fe.log
) for messages containing your MV name. Look for "waiting for lock" or "calculate partitions". 4. Enable Profiling for the Task If the query does start but finishes too fast (or fails) for you to catch it, the profile won't be saved unless profiling is enabled. Since MV refreshes use background sessions, you should enable it globally or via MV properties:
Copy code
sql
-- Enable for all future queries (not recommended for long-term production)
SET GLOBAL enable_profile = true;

-- OR, set it specifically for the MV
ALTER MATERIALIZED VIEW your_mv_name SET ("session.enable_profile" = "true");
Summary of what to do now: 1. Find the real status: Run the 
information_schema.task_runs
query above. 2. Check the Leader FE: Ensure you are running your commands on the same node where the task was submitted. 3. Check `EXTRA_MESSAGE`: In 
task_runs
, the 
EXTRA_MESSAGE
column often contains JSON details about which partitions it is trying to refresh. If this list is massive (thousands of partitions), the "preparation" phase will take a long time. References • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • best_practices/query_tuning/query_profile_overview.md • administration/management/resource_management/query_queues.md References • using_starrocks/async_mv/materialized_view_task_run_details.md
t
@Rocky the MV base table is populated by a ROUTINE LOAD
r
When the base table is populated by Routine Load, the "no queries running" issue is very common. It typically means the system is stuck in the Metadata Preparation Phase and hasn't reached the SQL execution phase yet. Here is why this happens and how to fix it: 1. High-Frequency Ingestion Overhead Routine Load often commits data every few seconds. If your MV is set to 
REFRESH ASYNC
(without an interval), StarRocks tries to trigger a refresh for every single commit. * The Bottleneck: Before running the 4-second query you expect, the Frontend (FE) must compare the "data versions" of the base table with the MV to calculate exactly which partitions need refreshing. * The Result: If Routine Load is creating new versions faster than the FE can calculate the "delta," the task stays in a 
RUNNING
state (metadata calculation) but never actually launches the physical 
INSERT
query. 2. The Solution: Add a Refresh Delay To fix this, you need to "batch" the updates so the MV doesn't try to refresh for every tiny Routine Load commit. Use the 
refresh_delay_seconds
property:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name 
SET ("refresh_delay_seconds" = "300"); -- Wait 5 minutes after a data change before starting
* How it works: StarRocks will wait until the base table has been "quiet" (or just wait for the timer) before starting the refresh. This prevents the FE from being overwhelmed by metadata checks. 3. Check for Version Accumulation Routine Load can create thousands of small "table versions." If the base table has too many versions, the MV planning phase becomes extremely slow. * Run 
SHOW TABLE STATUS LIKE 'your_base_table'
and check the 
VersionCount
. * If the version count is in the thousands, the FE is likely spending all its time just trying to parse the table's history. 4. How to confirm this is the problem Run this query to see the "Extra Message" from the Task Manager:
Copy code
sql
SELECT task_name, status, extra_message 
FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC LIMIT 1;
Look at the 
extra_message
column. If you see it stuck with a message like 
"calculate partitions to refresh"
, it confirms the FE is doing metadata work and hasn't started the query yet. Recommended Best Practices for Routine Load + MV: 1. Partition the MV: Ensure the MV is partitioned the same way as the base table so it only refreshes the "hot" partitions. 2. Set an Interval: Instead of 
REFRESH ASYNC
(trigger on change), use a fixed interval:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name REFRESH ASYNC EVERY (INTERVAL 10 MINUTE);
3. Increase Routine Load Batch Size: If possible, increase 
desired_txn_interval_ms
in your Routine Load job to reduce the number of versions created per minute. References • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
t
@Rocky there are no processes running when I check the lead FE
r
If you see 
RUNNING
in the MV status but nothing in 
SHOW PROCESSLIST
(even on the leader FE), it confirms that the refresh task is currently in the Metadata Planning Phase and has not yet submitted the actual SQL query to the execution engine. In StarRocks, an MV refresh is not one single query; it is a task that follows these stages: Stage 1: Metadata Calculation (Where you are likely stuck) The FE is currently looking at the base table (populated by your Routine Load) to determine which data versions have changed and which partitions need to be recalculated. * Why it's "invisible": This happens entirely within the FE's Java memory. Since no SQL 
INSERT
or 
SELECT
has been sent to the BEs yet, it won't appear in the processlist. * The Routine Load Problem: Routine Load can create a new version of your table every few seconds. If the FE is trying to calculate a refresh for "Version 100," but Routine Load has already moved to "Version 105," the FE often has to restart its calculation or wait for a metadata lock. This creates a "stuck in planning" loop. Stage 2: Physical Execution Only once Stage 1 completes does the FE generate a 
query_id
and start an 
INSERT OVERWRITE
query. This is the only stage where 
get_query_profile
will work. ────────── How to diagnose and fix this: 1. Check the "Plan Builder" Message Run the following to see the FE's internal diagnostic message for that specific run:
Copy code
sql
SELECT task_name, state, extra_message 
FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC LIMIT 1;
Look closely at 
extra_message
. If it is blank or says something like 
"calculating partitions"
, the planning phase is the bottleneck. 2. Check the Base Table Version Count If your Routine Load has been running for a long time without enough compaction, the version count might be too high for the MV to process quickly.
Copy code
sql
SHOW TABLE STATUS LIKE 'your_base_table_name';
If the 
VersionCount
is in the thousands, the MV planning will be extremely slow. You may need to wait for compaction or trigger a manual 
OPTIMIZE TABLE
(if using Primary Key model). 3. Implement a Refresh Delay (Highly Recommended) Since you are using Routine Load, you must stop the MV from trying to refresh on every tiny data commit. Add a buffer:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name 
SET ("refresh_delay_seconds" = "600"); -- Wait for 10 mins of data to accumulate
This gives the FE a "stable" version of the metadata to work with, allowing the planning phase to actually finish and start the physical query. 4. Peek into the FE Leader Log If you have access to the server, check 
fe.log
. Search for your MV name. You will likely see messages like: * 
analyze MV: [mv_name] start...
* 
waiting for lock...
* 
skip refresh because...
Summary: The reason 
get_query_profile
says "query not exists" is that the "Query" hasn't been born yet—the "Task" is still trying to decide what the plan. Applying the 
refresh_delay_seconds
is the most common fix for Routine Load-backed MVs.
t
@Rocky delaying the refresh to once a minute did nothing
r
If a 1-minute delay didn't help, the FE is likely stuck in a Metadata Heavy-Lift or a Lock Contention state. Since you have a Routine Load, you are likely hitting a "version explosion" where the FE is overwhelmed just trying to calculate what changed. Here is the deep-dive diagnostic path to find the "invisible" bottleneck: 1. Inspect the "Internal Plan" Message Even if there is no query in the processlist, the FE writes its current status into the 
task_runs
table. Run this and look specifically at the 
EXTRA_MESSAGE
column:
Copy code
sql
SELECT 
    TASK_NAME, 
    STATE, 
    EXTRA_MESSAGE 
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 1\G
What to look for in `EXTRA_MESSAGE`: * 
planBuilderMessage
: If this contains a long list of partitions or says "calculating...", the FE is stuck in the planning phase. * 
basePartitionsToRefreshMap
: If this map is empty or massive, it indicates a mapping logic failure. * 
status: PENDING
or 
RUNNING
: If it stays 
RUNNING
for a long time without a 
query_id
, the FE is likely struggling with the base table's metadata. 2. Check the Base Table "Version Explosion" Routine Load can easily create 10,000+ versions if not configured correctly. StarRocks' MV planning has to scan these versions. If the version count is too high, the FE will "hang" in the planning phase. Check your base table status:
Copy code
sql
SHOW TABLE STATUS LIKE 'your_base_table_name'\G
* Check 
VersionCount
: If this number is > 1000, your MV planning will be extremely slow. * The Fix: Increase the 
max_batch_interval
in your Routine Load job (e.g., set it to 20 or 30 seconds) to allow more data to batch per version, reducing the total version count. 3. Check for FE Metadata Locks The MV refresh needs a 
SCHEMA_CHANGE
or 
METADATA
lock on the base table. If your Routine Load is extremely aggressive, it might be holding onto locks. Run this command on the Leader FE to see if there are blocked tasks:
Copy code
sql
SHOW PROC '/complain';
If you see your MV name here, it will tell you exactly what it is waiting for (e.g., 
Waiting for lock
). 4. Is the MV Partitioned? If your MV is not partitioned, StarRocks must perform a full refresh every time. With a Routine Load constantly changing the base table, a full refresh can become a "forever task." * Check: 
SHOW CREATE MATERIALIZED VIEW your_mv_name;
* Recommendation: If it's not partitioned, and the table is large, it will almost always stay in a 
RUNNING
state because it can't finish the full refresh before the next data change arrives. 5. Force-Kill the Stuck Task If the task is stuck in a ghost state, "delaying" it now won't help because the old task is still occupying the slot. You must clear it: 1. Cancel the current refresh:
Copy code
sql
    CANCEL REFRESH MATERIALIZED VIEW your_mv_name;
2. Verify it's gone: 
SHOW MATERIALIZED VIEWS
should show 
last_refresh_state: CANCELLED
or 
FAILED
. 3. Wait 1 minute, then try a manual refresh:
Copy code
sql
    REFRESH MATERIALIZED VIEW your_mv_name;
_ _If the manual refresh also shows no processlist entry but says RUNNING, then the issue is 100% FE metadata/planning logic.* Final Note: If 
EXTRA_MESSAGE
in Step 1 is empty or doesn't provide enough info, check the 
fe.log
on your leader node for the string 
[MV]
. It will log the exact reason the optimizer is failing to generate a physical query plan. References • using_starrocks/async_mv/materialized_view_task_run_details.md • administration/management/monitoring/metrics.md • faq/loading/Routine_load_faq.md
t
@Rocky SHOW PROC '/complain'; does not work
r
I apologize—`SHOW PROC '/complain'` was a mistake. That path does not exist in StarRocks. The reason you see "no processes running" despite the MV being in a 
RUNNING
state is that the refresh task is stuck in the Metadata/Planning phase inside the Frontend (FE). In this phase, the FE is calculating partition deltas and hasn't actually issued a SQL command to the execution engine yet. To diagnose this properly, please use these supported StarRocks commands: 1. Inspect the "Stuck" Metadata The most detailed information for MVs is kept in the 
task_runs
table. Run this to see the internal reason for the delay:
Copy code
sql
SELECT 
    task_name, 
    state, 
    get_json_string(extra_message, '$.planBuilderMessage') as plan_info,
    get_json_string(extra_message, '$.mvPartitionsToRefresh') as partitions,
    get_json_string(extra_message, '$.progress') as progress
FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC LIMIT 1\G
* If 
plan_info
is long or mentions "calculating," the FE is overwhelmed by the number of changes or versions from your Routine Load. 2. Check for Transaction Locks Routine Load commits many transactions. If one is hanging, it can block the MV from obtaining a metadata lock. Check the status of active transactions:
Copy code
sql
SHOW PROC '/transactions';
Look for any transactions that have been in 
PREPARED
or 
COMMITTED
state for an unusually long time. 3. Check Base Table "Version Explosion" If your Routine Load is frequent, the base table might have too many "versions." MVs struggle to plan refreshes when the version count is high.
Copy code
sql
SHOW TABLE STATUS LIKE 'your_base_table_name'\G
* Action: If 
VersionCount
is > 1000, your Routine Load is too aggressive. Increase the 
desired_txn_interval_ms
in your Routine Load job (e.g., to 30000ms or 60000ms) to reduce the number of commits. 4. Search the FE Log for "DeadlockChecker" If the system is truly stuck on a lock, StarRocks (v2.5.15+, v3.0.9+) automatically logs this. Search your 
fe.log
file for the keyword 
DeadlockChecker
. It will print a JSON block showing exactly which thread is holding the lock and which thread (likely your MV refresh) is waiting. Why 1-minute delay didn't work: If a previous refresh task was already "stuck" in the planning phase, adding a delay now won't kill the existing task. You should manually clear the queue: 1. 
CANCEL REFRESH MATERIALIZED VIEW your_mv_name;
2. Wait for the status to show 
FAILED
or 
CANCELLED
. 3. Try a manual 
REFRESH MATERIALIZED VIEW your_mv_name;
and immediately check 
information_schema.task_runs
as shown in Step 1. References • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • knowledge/trouble_shooting/fe_deadlock_problems.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
t
@Rocky the transaction command does not show the column you refer to, and the SHOW TABLE command does not show version
r
I apologize for the confusion. StarRocks uses internal paths for 
SHOW PROC
that require specific IDs, and some metadata columns are only found in system views rather than standard 
SHOW
commands. Here are the correct ways to see the details you need: 1. Check Table Version and Compaction (The Bottleneck) The "Version" that slows down MV planning is actually the number of data versions (rowsets). You can find this in the 
partitions_meta
table:
Copy code
sql
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    VISIBLE_VERSION, 
    MAX_CS  -- This is the Compaction Score
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_base_table_name';
* VISIBLE_VERSION: If this number is increasing very rapidly (due to Routine Load), the MV planner struggles to catch up. * MAX_CS: If this score is high (e.g., above 50 or 100), it means there are too many uncompacted versions. The FE must scan all these metadata versions to plan the MV refresh, which is why it appears "stuck" without starting a process. 2. Drill Down into Transactions 
SHOW PROC '/transactions';
only shows a list of databases. To see the actual running transactions, you must include the Database ID: 1. Get your Database ID:
Copy code
sql
    SELECT DATABASE_ID FROM information_schema.databases WHERE SCHEMA_NAME = 'your_db_name';
2. View Running Transactions:
Copy code
sql
    -- Replace <DbId> with the ID from step 1
    SHOW PROC '/transactions/<DbId>/running';
3. Inspect a Specific Transaction: If you find a long-running ID, use:
Copy code
sql
    SHOW TRANSACTION WHERE ID = <TransactionId>;
3. Check for FE Metadata Locks If the MV is in a 
RUNNING
state but no query has started, the FE might be waiting for a lock. Run this to check for blocked threads:
Copy code
sql
SHOW PROC '/frontends';
Look at the 
IsLeader
node. If you have terminal access to the FE leader, check the 
fe.log
and search for 
lock
 or 
slow
. Recommended Action: If your 
MAX_CS
is high or 
VISIBLE_VERSION
is moving too fast: 1. Slow down the Routine Load: Increase 
desired_txn_interval_ms
(e.g., to 
60000
for 1 minute) to reduce the number of metadata versions created. 2. Manual Compaction: You can trigger a manual compaction on the base table to clear the metadata backlog:
Copy code
sql
    -- For Primary Key tables
    OPTIMIZE TABLE your_base_table_name;
3. Kill the Ghost Task: If the MV is stuck, cancel it: 
CANCEL REFRESH MATERIALIZED VIEW your_mv_name;
. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/Materialized_view.md
t
@Rocky SELECT DATABASE_ID does not work
r
I apologize—I used a standard SQL syntax that StarRocks doesn't support for system IDs. In StarRocks, these IDs are found using the 
SHOW PROC
navigation system. 1. How to get your Database ID Run this command to see all databases and their IDs:
Copy code
sql
SHOW PROC '/dbs';
* Locate your database name in the 
DbName
column and note the 
DbId
. * (Alternatively, 
SHOW PROC '/statistic';
also works). 2. How to see the "Invisible" Bottleneck (Rowset/Version Count) Since 
SHOW TABLE STATUS
didn't give the internal version info, use the 
SHOW TABLETS
command. If you have too many versions (rowsets) from Routine Load, the FE will "freeze" while planning the MV refresh.
Copy code
sql
-- Check the rowset count for your base table
SHOW TABLETS FROM your_base_table_name;
* Look at the 
RowsetCount
column. * If this number is higher than 100-200 for many tablets, the FE is struggling to scan the metadata. * The Fix: You need to trigger compaction or slow down your Routine Load frequency. 3. How to check for Blocked Transactions Now that you have the 
DbId
from step 1, drill into the transactions:
Copy code
sql
-- Substitute <DbId> with the ID you found
SHOW PROC '/transactions/<DbId>/running';
* If you see transactions that have been running for a long time, they might be holding the metadata locks required for the MV refresh. 4. Check Partition Versions To see the actual version number the MV is trying to "catch up" to:
Copy code
sql
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    VISIBLE_VERSION 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_base_table_name';
Summary of why "1 minute" didn't work: If your 
RowsetCount
is high (from Step 2), the Frontend is doing a massive amount of "Metadata Heavy-Lifting" before it even starts the query. It isn't "idle"—it's maxing out a single thread in the FE trying to calculate which rows changed. Recommended Action: 1. Kill the current task: 
CANCEL REFRESH MATERIALIZED VIEW your_mv_name;
2. Reduce Load Frequency: In your Routine Load, set 
desired_txn_interval_ms
to 
30000
or 
60000
. This forces StarRocks to batch more data into fewer "versions," which is the only way to unblock the MV planner. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/monitoring/metrics.md • faq/loading/Routine_load_faq.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
t
@Rocky there are 83 stuck transactions
r
Having 83 transactions in a 
RUNNING
state is the "smoking gun." In StarRocks, an MV refresh must acquire a Database Metadata Lock to calculate the difference between the base table and the MV. If many transactions are active or stuck, they hold these locks, and the MV refresh thread will wait indefinitely in the "Planning" phase without ever appearing in 
SHOW PROC '/jobs'
. Here is how to identify and clear those 83 transactions: 1. Identify what is running Check a few of the Transaction IDs you found. This will tell you if they are from Routine Load, Flink (Stream Load), or manual SQL.
Copy code
sql
-- Replace <id> with one of the Transaction IDs you found
SHOW TRANSACTION WHERE ID = <id>;
* Label: Look for keywords like 
routine_load
, 
flink
, or a UUID. * LoadJobSourceType: Tells you if it's 
INSERT
, 
ROUTINE_LOAD
, or 
STREAM_LOAD
. * TimeoutMs: Check if the transaction is already past its timeout. 2. How to clear them StarRocks doesn't have a single 
ABORT TRANSACTION <id>
command. You must stop the source of the transaction: * If Routine Load: If the transactions have labels like 
rt_load_xxx
, pause or stop the job to clear the backlog:
Copy code
sql
    PAUSE ROUTINE LOAD FOR <job_name>;
    -- After a few seconds, the transactions should abort.
    RESUME ROUTINE LOAD FOR <job_name>;
* If Manual SQL (Insert/Update): Find the connection ID and kill it.
Copy code
sql
    SHOW FULL PROCESSLIST;
    -- Find the 'Id' column for the long-running queries
    KILL <connection_id>;
* If Stream Load (e.g., Flink): The transactions will only abort if the Flink job is paused or if the 
TimeoutMs
is reached. If Flink is "stuck" in a 2PC (Two-Phase Commit) state, you may need to restart the Flink task. 3. Check for FE "Lock Contention" (The Deadlock) With 83 transactions, your Frontend (FE) is likely experiencing a lock queue. To verify this, check your 
fe.log
on the Leader FE node:
Copy code
bash
grep "DeadlockChecker" fe.log
If you see JSON output with 
"lockState":"readLocked"
or 
"writeLocked"
, it will explicitly list the Thread ID that is holding the lock and preventing your MV from refreshing. 4. Immediate fix for the MV Once you have reduced the number of running transactions (ideally to < 10): 1. Cancel the MV task: 
CANCEL REFRESH MATERIALIZED VIEW your_mv_name;
2. Clear the base table versions: If the base table has a high "Compaction Score" (from the 
MAX_CS
query earlier), run:
Copy code
sql
    OPTIMIZE TABLE your_base_table_name;
3. Try manual refresh: 
REFRESH MATERIALIZED VIEW your_mv_name;
Long-term Prevention: 83 transactions suggests your Routine Load or Stream Load is committing too frequently. * For Routine Load: Increase 
desired_txn_interval_ms
to 
30000
(30 seconds). * For Flink: Increase the checkpoint interval to at least 60 seconds. References • loading/SQL_transaction.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • knowledge/trouble_shooting/fe_deadlock_problems.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/compaction.md References • loading/SQL_transaction.md • administration/management/resource_management/Query_management.md
t
@Rocky each transaction has an error similar to identical to this: Fail to publish partition 4905617 error Fail to publish version for tablets:[[4905621]], error msg: link rpc channel failed, host: starrocks-share-data-mode-cn-2.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local
r
This error confirms that your StarRocks cluster is in shared-data mode (using Compute Nodes/CN) and is experiencing a network or node-level failure that is blocking the final step of your data loads. Why this is happening In StarRocks, a transaction has two main phases: 1. Commit: Data is written to the object storage (S3/Azure/HDFS). 2. Publish: The FE sends an RPC (Remote Procedure Call) to a CN node to "finalize" the metadata and make the data VISIBLE. The error 
link rpc channel failed
means your Leader FE cannot connect to CN node #2 (the one named 
starrocks-share-data-mode-cn-2...
). Because the FE can't talk to that node, it can't finish the "Publish" phase. This is why your MV is stuck: The MV refresh is waiting for the base table to reach a specific version, but that version is trapped in the "Committed" state (83 times over) because of this RPC failure. ────────── Step-by-Step Troubleshooting 1. Check CN Node Health The node 
starrocks-share-data-mode-cn-2
is likely crashed, OOM (Out of Memory), or restarting. Run this in StarRocks:
Copy code
sql
SHOW PROC '/compute_nodes';
* Check the 
Alive
column for node 
cn-2
. If it is 
false
, the node is down. * If you are on Kubernetes, check the pod status: 
kubectl get pods -n spinnaker | grep cn-2
2. Verify Network Connectivity If the node is "Alive" but still failing, the FE cannot reach the CN's bRPC port (default is 8060). * From your FE leader pod, try to ping or telnet the address in the error:
Copy code
bash
    telnet starrocks-share-data-mode-cn-2.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local 8060
* If this fails, check your K8s NetworkPolicies or Security Groups to ensure the FE is allowed to talk to the CN nodes on port 8060. 3. Inspect CN Logs If the node is up but the link fails, the CN might be overwhelmed or stuck. Access the logs for `cn-2`:
Copy code
bash
# Look for 'rpc' or 'fail' in the CN info log
grep -E "rpc|fail" <http://cn.INFO|cn.INFO>
Look for errors like 
process_publish_version
or 
brpc error
. 4. How to clear the 83 Transactions Once you fix the connectivity to 
cn-2
, StarRocks has a background thread called 
PublishVersionDaemon
that will automatically retry these 83 tasks. You don't usually need to manually kill them. If the transactions stay stuck after the node is fixed: 1. Restart the FE Leader: This sometimes forces a refresh of the RPC client pool. 2. Check for "Version Skip": If these transactions are very old, they may have timed out. You can check their status using:
Copy code
sql
    SHOW TRANSACTION WHERE ID = <txn_id>;
Summary for your MV: Your MV is not broken; it is a "victim" of the base table's inability to publish data. 1. Fix the CN-2 node or network link. 2. Wait for 
SHOW PROC '/transactions/<DbId>/running';
to clear. 3. The MV refresh will then start automatically as soon as the 
VISIBLE_VERSION
of the base table matches what the MV expects. References • administration/management/FE_configuration.md • deployment/shared_data/azure.md • deployment/shared_data/feature-support-shared-data.md • administration/management/BE_configuration.md
t
@Rocky CN had failed previously, but recovered. Now there are a bunch of transactions with error messages that had previously failed but never finished with TransactionStatus: COMMITTED
r
In StarRocks shared-data mode, a transaction status of 
COMMITTED
means the data has been successfully written to your object storage (S3/GCS/etc.), but the *Publish Phase*—the final step that makes the data visible to queries—has failed or is stuck. When a CN node fails and recovers, the FE's internal 
PublishVersionDaemon
is supposed to retry these tasks. If they are still hanging, it usually indicates a "stuck" daemon thread or a resource bottleneck in the FE. 1. Why it's stuck in 
COMMITTED
 The FE must send a 
publish_version
RPC to the CN nodes and then update its own metadata (the 
VisibleVersion
). If the CN was down, the RPC failed. If the FE's internal task queue for publishing is full or the thread encountered an unhandled exception (like a temporary deadlock), it may stop retrying those specific transactions. 2. Immediate Fix: Restart the Leader FE The most effective way to "kick" the publish daemon into retrying 
COMMITTED
transactions is to restart the Leader FE. * This resets the 
PublishVersionDaemon
thread and clears its internal RPC retry queue. * Upon becoming the Leader again (or if a Follower takes over), the FE will scan for transactions in the 
COMMITTED
state and attempt to publish them immediately. 3. Check FE Resource Limits If you have a high volume of transactions (the 83 you mentioned), you may have hit the thread limit for publishing. Check these configurations:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'lake_publish_version_max_threads';
* Default: 512. * If your FE logs show 
rejectedExecution
or 
agent-task-pool
errors, you may need to increase this. 4. Verify Database Locks The 
Publish
phase requires a Database Write Lock. If other long-running loads or metadata-heavy operations (like manual schema changes or a massive MV refresh "planning" phase) are holding the lock, the Publish thread will wait. Check the FE 
fe.warn.log
or 
fe.log
for:
Copy code
bash
grep "Database.*wait.*lock" fe.log
# OR
grep "PublishVersionDaemon" fe.log
If you see "failed to get database lock," the transactions are 
COMMITTED
but simply waiting for a lock to become 
VISIBLE
. 5. Check Inconsistency (The "Mark Visible" trick) If only a few tablets are lagging, you can check the discrepancy between the FE metadata and the tablets:
Copy code
sql
SELECT * FROM information_schema.be_tablets WHERE TABLE_NAME = 'your_table';
SELECT * FROM information_schema.partitions_meta WHERE TABLE_NAME = 'your_table';
If the versions match but the transaction status is still weird, the restart in Step 2 is your best path. Summary Checklist 1. Confirm CNs are back: 
SHOW PROC '/compute_nodes';
(Ensure 
Alive
is 
true
). 2. Restart Leader FE: This is the standard "unstick" mechanism for the metadata daemon. 3. Check Version: Once the FE restarts, monitor 
SHOW TRANSACTION
for those IDs. They should flip from 
COMMITTED
to 
VISIBLE
within 1–2 minutes. 4. MV Refresh: Once the transactions are 
VISIBLE
, your MV refresh will finally proceed past the planning phase. References • faq/shared_data_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • knowledge/usage/load/shared_data_batch_ingestion.md • administration/management/monitoring/metrics.md References • faq/shared_data_faq.md • administration/management/FE_configuration.md
• sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • administration/management/monitoring/metrics.md • knowledge/usage/load/shared_data_batch_ingestion.md • quick_start/shared-data.md • deployment/shared_data/feature-support-shared-data.md • knowledge/trouble_shooting/fe_deadlock_problems.md • data_source/catalog/iceberg_catalog.md • loading/SQL_transaction.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/compaction.md • sql-reference/sql-statements/data-definition/ALTER_SYSTEM.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • sql-reference/sql-statements/data-definition/OPTIMIZE_TABLE.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/data-manipulation/CANCEL_REFRESH_MATERIALIZED_VIEW.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • sql-reference/sql-statements/loading_unloading/routine_load/PAUSE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/RESUME_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_FULL_PROCESSLIST.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/data-manipulation/REFRESH_MATERIALIZED_VIEW.md • administration/management/BE_configuration.md • loading/RoutineLoad.md • deployment/shared_data/azure.md
• sql-reference/sql-statements/data-definition/SHOW_PARTITIONS.md • sql-reference/sql-statements/data-definition/SHOW_TABLE_STATUS.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_TABLETS.md • faq/loading/Routine_load_faq.md • administration/management/resource_management/Query_management.md • faq/Sql_faq.md • administration/management/vacuum.md • loading/Flink_connector.md • administration/management/FE_configuration.md • faq/shared_data_faq.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • administration/management/monitoring/metrics.md • knowledge/usage/load/shared_data_batch_ingestion.md • quick_start/shared-data.md • deployment/shared_data/feature-support-shared-data.md • knowledge/trouble_shooting/fe_deadlock_problems.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • faq/shared_data_faq.md • deployment/shared_data/feature-support-shared-data.md • administration/management/FE_configuration.md • knowledge/usage/load/shared_data_batch_ingestion.md • quick_start/shared-data.md
t
@Rocky the materialized view eventually runs, it just takes much longer than expected. It takes several minutes to create or refresh, but query only takes 4 seconds at most.
r
In StarRocks shared-data mode, a large gap between query speed (4s) and refresh speed (minutes) usually indicates metadata overhead or lock contention, rather than a problem with the data processing itself. Since you recently had 83 stuck transactions and a CN failure, your cluster is likely suffering from "Version Bloat" or a backlog of metadata tasks. 1. Identify where the time is being spent Run this query to see if the delay is happening before the refresh starts (queueing) or during the refresh (execution):
Copy code
sql
SELECT 
    TASK_NAME,
    CREATE_TIME,
    get_json_bigint(EXTRA_MESSAGE, '$.processStartTime') AS process_start_ms,
    (get_json_bigint(EXTRA_MESSAGE, '$.processStartTime') - unix_timestamp(CREATE_TIME) * 1000) / 1000 AS queue_seconds,
    (unix_timestamp(FINISH_TIME) * 1000 - get_json_bigint(EXTRA_MESSAGE, '$.processStartTime')) / 1000 AS processing_seconds
FROM information_schema.task_runs
WHERE TASK_NAME LIKE 'mv-%' -- or your specific MV name
ORDER BY CREATE_TIME DESC LIMIT 5;
* High `queue_seconds`: The MV is waiting for a Metadata Lock. Your 83 transactions (or new incoming loads) are holding the database lock, and the MV refresh is stuck in the FE's task queue. * High `processing_seconds`: The MV is struggling to "Plan" the refresh. This happens if the base table has too many versions (backlog from your 83 transactions). ────────── 2. Check for "Version Bloat" Every transaction creates a new "version." If your 83 transactions were small and frequent, or if they were retried many times during the CN failure, the base table now has a high number of metadata files in object storage. Check the Compaction Score and Version Count of your base tables:
Copy code
sql
SELECT 
    DB_NAME, TABLE_NAME, PARTITION_NAME, 
    VISIBLE_VERSION, 
    MAX_CS  -- Compaction Score
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_base_table_name'
ORDER BY MAX_CS DESC;
* VISIBLE_VERSION > 1000: The FE has to scan too many metadata entries to plan the refresh. * MAX_CS > 10: Compaction is lagging. The CNs are still busy cleaning up the mess from the previous failure. ────────── 3. Solutions A. Force a Compaction (Manual Cleanup) If the 
MAX_CS
is high, you can manually trigger a compaction to merge those 83+ versions into one, which will make the next MV refresh much faster:
Copy code
sql
-- This tells the CNs to prioritize merging versions for this table
OPTIMIZE TABLE your_base_table_name;
B. Check for FE Lock Contention Even if the transactions are finished, new loads might be "bullying" the MV refresh. StarRocks prioritizes data ingestion locks over MV refresh locks. If you use Routine Load or Flink, try increasing the 
checkpoint
or 
desired_txn_interval_ms
to 60 seconds. This gives the FE a "gap" to grant the lock to the MV refresh thread. C. FE Restart (The "Clean Slate" option) Since you mentioned the CNs recovered but things are still slow, the Leader FE might have a backlogged 
LakePublish
queue. Restarting the Leader FE will: 1. Clear the internal metadata RPC retry queues. 2. Force a fresh calculation of the MV dependency graph. Summary The query is fast (4s) because it only reads the latest data. The refresh is slow (minutes) because it has to account for the history of those 83 transactions and negotiate locks with ongoing loads. Running 
OPTIMIZE TABLE
on your base tables is the most likely fix. References • using_starrocks/async_mv/materialized_view_task_run_details.md • knowledge/admin_and_operation/shared_data_compaction.md • faq/shared_data_faq.md
t
@Rocky OPTIMIZE TABLE isn't a real comman d
r
You are absolutely right—I apologize for the confusion. 
OPTIMIZE TABLE
is not a StarRocks command; I likely confused it with other database engines. In StarRocks (specifically starting from v3.1), the correct way to manually trigger compaction is using the 
ALTER TABLE
syntax. The Correct Command To force the cluster to merge the backlog of versions (like those 83 transactions) into larger files:
Copy code
sql
-- To trigger compaction for the entire table:
ALTER TABLE your_table_name COMPACT;

-- Or to target specific partitions that have a high version count:
ALTER TABLE your_table_name COMPACT PARTITION (partition_name);
How to monitor the progress Since compaction is an asynchronous background process, you can monitor how many versions are being merged and if the "Compaction Score" is dropping: 1. Check the active compaction tasks:
Copy code
sql
    SHOW PROC '/compactions';
Look for your table name and check the 
Status
column. 2. Verify the Version Count and Compaction Score:
Copy code
sql
    -- Check if MaxCS (Compaction Score) or VisibleVersion is decreasing
    SELECT * FROM information_schema.partitions_meta
    WHERE TABLE_NAME = 'your_table_name'
    ORDER BY MAX_CS DESC;
Why the MV Refresh is slow but Query is fast This confirms my theory about metadata overhead: * Queries (4s): When you query, StarRocks only needs to find the latest consistent state of the data. It's fast because the CN nodes have likely cached the relevant data blocks. * MV Refresh (Minutes): To refresh an MV, the FE must perform Transaction Planning. If you have 100+ uncompacted versions, the FE has to scan 100+ metadata files to determine exactly which rows changed and "calculate" the delta. This metadata "walk" happens on the FE and can take minutes if the table is fragmented. Running 
ALTER TABLE ... COMPACT
will consolidate those 83+ versions into 1, which should bring your MV refresh time back down to normal. References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • faq/shared_data_faq.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md • loading/SQL_transaction.md • administration/management/vacuum.md • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • deployment/shared_data/feature-support-shared-data.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • administration/management/monitoring/metrics.md
• knowledge/usage/load/shared_data_batch_ingestion.md • knowledge/trouble_shooting/fe_deadlock_problems.md • deployment/shared_data/azure.md • sql-reference/information_schema/task_runs.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/data-definition/SHOW_PARTITIONS.md • loading/Load_to_Primary_Key_tables.md • loading/RoutineLoad.md • loading/Flink_connector.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_TABLETS.md • sql-reference/sql-statements/data-manipulation/REFRESH_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-manipulation/CANCEL_REFRESH_MATERIALIZED_VIEW.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • sql-reference/sql-statements/data-definition/SHOW_TABLE_STATUS.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_FULL_PROCESSLIST.md • faq/Sql_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/PAUSE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/RESUME_ROUTINE_LOAD.md • data_source/catalog/iceberg_catalog.md • sql-reference/sql-statements/data-definition/ALTER_SYSTEM.md • sql-reference/sql-statements/data-definition/OPTIMIZE_TABLE.md
t
@Rocky the highest MAX_CS for that table is 82
r
A 
MAX_CS
of 82 confirms that your table has a significant "version backlog." In StarRocks, a score this high means there are roughly 82 unmerged data versions (rowsets) for that partition. The FE must process the metadata for all 82 versions every time the Materialized View tries to refresh, which is why it takes minutes to plan while the actual query (which may benefit from data caches) remains fast. 1. Trigger Manual Compaction Since the automatic compaction hasn't caught up yet, you should manually force it:
Copy code
sql
-- This will tell the CNs to start merging those 82 versions immediately
ALTER TABLE <your_table_name> COMPACT;
2. Monitor the Cleanup You can watch the 
MAX_CS
drop and see if the CNs are actively working on it: * Check compaction tasks:
Copy code
sql
    SHOW PROC '/compactions';
Look for your table. If the 
Status
is 
RUNNING
, the CN is merging the files. * Check the score again: Run your 
MAX_CS
query every few minutes. You want to see that 82 drop toward 10 or lower. 3. Why it got this high A score of 82 usually happens in shared-data mode when: 1. CNs were down: Compaction is performed by CN nodes. If they were offline or unstable, versions simply piled up in object storage. 2. High-frequency small loads: If you are doing many small writes (like 83 separate transactions in a short window), each one creates a version. 3. Compaction threads are limited: If the score doesn't drop after the 
ALTER TABLE
command, your CNs might be restricted by their thread limits. 4. Optional: Speed up the compaction (CN Config) If the score isn't dropping, you may need to allow the CNs to work harder. You can check these CN (BE) configurations: * `max_cumulative_compaction_threads`: Default is often 1. If you have many CPU cores, you can increase this. * `lake_compaction_max_tasks`: (For shared-data) Controls how many concurrent compaction tasks a CN can handle. Note: Once that 
MAX_CS
drops below 10-20, your Materialized View refresh times should return to normal (or beat) your query performance of 4 seconds. References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • faq/shared_data_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
t
@Rocky the compaction doesn't show after I submit it
r
In StarRocks shared-data mode, 
ALTER TABLE ... COMPACT
initiates an asynchronous task. If it’s not showing up in 
SHOW PROC '/compactions'
, it usually means the task was either completed instantly (unlikely with a score of 82), or it's being blocked by the metadata cleanup from those 83 stuck transactions you mentioned earlier. Here are the specific places to look to find where the task went: 1. Check Tablet-Level Progress In shared-data clusters (v3.2+), the partition-level 
SHOW PROC
can sometimes be empty if the task is still being scheduled at the tablet level. Run this instead:
Copy code
sql
SELECT * FROM information_schema.be_cloud_native_compactions 
WHERE STATUS != 'OK' OR PROGRESS < 100
ORDER BY START_TIME DESC;
* If this returns rows: Your manual compaction is running. You can see the 
PROGRESS
percentage for each tablet. * If this is empty: The FE has not yet successfully handed the task to the Compute Nodes (CN). 2. Check for Transaction Locks Compaction in shared-data mode requires a Transaction ID (TxnID). If your cluster is still "recovering" from the 83 stuck transactions, the FE might be refusing to start a new compaction transaction. Run this to see if there are any active transactions blocking the way:
Copy code
sql
SHOW TRANSACTION WHERE TableName = 'your_table_name';
If you see transactions with a 
State
of 
PREPARE
or 
COMMITTED
that are very old, they are likely the remnants of your previous failure and are preventing the manual compaction from starting. 3. Verify you are on the Leader FE Manual compaction commands must be processed by the Leader FE. While StarRocks usually forwards these, try connecting directly to the Leader FE to run the command and check the status. You can find the leader by running:
Copy code
sql
SHOW FRONTENDS;
4. Check CN Node Health Compaction is executed by CNs. If your CNs just recovered from a failure, they might have their compaction threads saturated with automatic background cleanup. Check the CN logs (
<http://be.INFO|be.INFO>
) for lines containing 
lake_compaction
. If you see "no idle thread," the manual task is simply waiting in line. Summary / Next Step: If 
be_cloud_native_compactions
is empty and no transactions are visible, re-run the command for a single partition to see if it triggers:
Copy code
sql
ALTER TABLE your_table_name COMPACT PARTITION (partition_name_with_82_score);
If this still doesn't show up in 
SHOW PROC '/compactions'
, the FE is likely stuck in a metadata "wait" state due to the previous 83 failed loads, and an FE Restart may be necessary to clear the internal task state. References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • knowledge/admin_and_operation/shared_data_compaction.md • deployment/shared_data/feature-support-shared-data.md • administration/management/compaction.md
t
@Rocky compaction completed for the table but the MAX_CS is the same
r
If your compaction task shows as "completed" in 
information_schema.be_cloud_native_compactions
but the 
MAX_CS
is still 82, it is usually due to one of three things: the size-tiering logic, thread limits, or a metadata refresh lag. 1. The "Size-Tiering" Logic (Most Likely) StarRocks uses a size-tiered compaction strategy. Rowsets are grouped by size (e.g., <10MB, 10-100MB, etc.). The Compaction Score is the number of files in the single largest group, not the total number of files. If you have 82 very small files and StarRocks merges 40 of them into one 50MB file: * You now have 42 small files + 1 medium file. * Your 
MAX_CS
only drops from 82 to 42. * Since the score is still high, you need to run the compaction command multiple times until the files are large enough to move into the next tier and the "Max" count finally drops below 10. 2. Check the "Input Rowsets" Count To see if the compaction actually did anything, check exactly how many files it merged:
Copy code
sql
SELECT 
    TABLET_ID, 
    START_TIME, 
    FINISH_TIME, 
    STATUS, 
    PROGRESS 
FROM information_schema.be_cloud_native_compactions 
WHERE STATUS = 'OK' 
ORDER BY START_TIME DESC LIMIT 5;
If the 
PROGRESS
reached 100% but the 
MAX_CS
is unchanged, check if the task was only performed on a few tablets. In a table with many buckets, 
MAX_CS
only tracks the "worst" tablet. If your command only compacted 5 out of 20 tablets, the overall partition score (the Max) will stay at 82. 3. Adjust CN (BE) Configs to Merge More at Once If the compaction is "too polite" (merging only a few files at a time), you can force it to be more aggressive. On each Compute Node (CN), you can increase the limit of how many files a single task can grab:
Copy code
sql
-- Increase the number of rowsets allowed in one merge (Default is often 1000, but try explicitly setting it)
UPDATE information_schema.be_configs 
SET VALUE = "500" 
WHERE name = "max_cumulative_compaction_num_singleton_deltas";

-- Ensure enough threads are available
UPDATE information_schema.be_configs 
SET VALUE = "8" 
WHERE name = "compact_threads";
4. Metadata Refresh Lag The 
partitions_meta
table is a view of the FE's internal state. Occasionally, there is a slight delay between a CN finishing a task and the FE "Publishing" the new version and recalculating the score. * Wait 60 seconds and check 
MAX_CS
again. * Check 
CompactVersion
vs 
VisibleVersion
in 
SHOW PARTITIONS FROM your_table
. If 
CompactVersion
is much lower than 
VisibleVersion
, the FE is still catching up on the metadata. Recommended Next Step: Run the manual compaction specifically for the affected partition one more time:
Copy code
sql
ALTER TABLE <your_table> COMPACT PARTITION (<partition_name>);
If the score still doesn't budge, check the Leader FE logs for "publish version" errors, which would prevent the new compacted version from becoming "visible" and lowering the score. References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
t
@Rocky how to check logs
r
To investigate why your compaction score isn't dropping, you need to check the logs on both the Frontend (FE) and the Compute Nodes (CN). 1. Frontend (FE) Logs The FE manages the metadata and schedules compaction tasks. If the score isn't updating, the FE might be failing to "publish" the results of a compaction. * Location: 
${STARROCKS_HOME}/log/fe.log
(or 
fe.warn.log
) * What to look for: * Search for 
publish version
or 
compaction
. * Check for errors related to the specific table or partition. * Example: 
grep "compaction" fe.log | tail -n 50
2. Compute Node (CN/BE) Logs The CNs do the actual work of merging files. If a task "completed" but the score didn't change, the CN logs will show if it actually merged any data. * Location: 
${STARROCKS_HOME}/log/cn.INFO
(or 
cn.WARNING
) * What to look for: * Search for 
lake_compaction
or the 
Tablet ID
from the partition you are troubleshooting. * Look for lines like 
merge rowsets
to see how many input files were used and how many output files were created. * Example: 
grep "lake_compaction" <http://cn.INFO|cn.INFO> | tail -n 50
3. Standard Output (For Startup/Crashes) If you can't find anything in the main logs, check the 
.out
files which catch low-level system errors or JVM crashes: * FE: 
fe.out
* CN: 
cn.out
Helpful Commands If you are logged into the server terminal:
Copy code
bash
# To watch logs in real-time while you trigger a compaction
tail -f log/cn.INFO | grep "compaction"

# To find errors in the last hour
grep "ERROR" log/cn.INFO
Note: Since you have a high 
MAX_CS
(82), pay close attention to the CN logs for any "Too many open files" or "No space left on device" errors, as large compactions can sometimes hit system resource-starve a node. References • administration/management/logs.md • deployment/deploy_manually.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md References • administration/management/logs.md • deployment/deploy_manually.md • administration/management/BE_configuration.md
t
@Rocky seeing this: 2026-07-16 054202.839Z ERROR (publish-task-326108|7975059) [PublishVersionDaemon.publishPartition():1104] Fail to publish partition 6637839 of txn 7252781: Fail to publish version for tablets:[], error msg: link rpc channel failed, host: starrocks-share-data-mode-cn-0.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local
r
This log message is the "smoking gun." It explains why your compaction is completing on the CN but the 
MAX_CS
score isn't dropping. The error 
link rpc channel failed
 means the Frontend (FE) cannot communicate with the Compute Node (CN) to finalize the transaction. Because the "Publish Version" task failed, the FE never records the compaction as "finished" in its metadata, so it keeps pointing to the old, uncompacted files. This is also likely the cause of your 83 stuck transactions. Why this is happening: 1. CN Node is Unreachable: The FE is trying to talk to 
starrocks-share-data-mode-cn-0
, but the network connection is being refused or timed out. 2. DNS/Service Discovery Issues: Since you are on Kubernetes (indicated by the 
.svc.cluster.local
address), the FE might be using a stale or incorrect DNS name for the CN. 3. Port Blockage: The RPC port (default 
9060
) or the heartbeat port (default 
9050
) on that specific CN might be blocked or the service is down. Steps to Fix: 1. Check CN Process Health Check if the process on 
starrocks-share-data-mode-cn-0
is actually running or if it's stuck in a crash loop:
Copy code
bash
# In Kubernetes
kubectl get pods -n spinnaker
kubectl logs starrocks-share-data-mode-cn-0 -n spinnaker --tail=100
If the pod is 
Running
but the logs show 
OOMKilled
or 
Heartbeat timeout
, the node is unhealthy. 2. Test Connectivity from the FE Pod Exec into your FE pod and try to reach the CN on its RPC port:
Copy code
bash
# Inside the FE pod
nc -zv starrocks-share-data-mode-cn-0.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local 9060
* If it fails: You have a network/DNS issue in your K8s cluster. * If it succeeds: The CN might be "zombie-ing" (alive but not responding to actual RPC requests due to thread exhaustion). 3. Clear the Stuck Transactions The FE will keep retrying these "Publish" tasks, which can clog up the system. Since these are likely the manual compaction tasks that failed to publish: 1. Restart the CN pod: 
kubectl delete pod starrocks-share-data-mode-cn-0
to force a clean state. 2. Wait for recovery: Once the CN is back, the FE should automatically retry and potentially succeed in publishing those versions. 4. Verify 
priority_networks
 If you recently changed your network setup, ensure the 
priority_networks
configuration in 
fe.conf
and 
cn.conf
matches your K8s pod CIDR. If they are mismatched, the FE might be trying to contact the CN on an internal IP that is no longer valid. Summary Your compaction did work, but the FE couldn't "save" the result because it lost contact with the CN at the very last second. Fix the connectivity to 
starrocks-share-data-mode-cn-0
, and the score should finally drop.
t
@Rocky all CN nodes seem to be up and in a healthy states, there are old logs from a month ago: W20260604 023129.746369 140468158408256 tablet_sink_sender.cpp:183] Open channel failed. load_id: 019e9078-8ba5-713c-8d03-f5d9d96e8cf0, error: Memory limit exceeded: memory limit exceeded, please reduce load frequency or increase config 
load_process_max_memory_hard_limit_ratio
or add more BE nodes: be:192.168.35.252
r
The error 
link rpc channel failed
 in your FE log is the reason your compaction score isn't dropping. Even though the Compute Node (CN) finished the work (status "OK"), the Publish Version phase—where the FE officially updates the table's metadata to point to the new, merged files—is failing because the FE cannot talk to the CN to confirm the update. Because the "Publish" never finishes, the FE continues to use the old metadata, and your 
MAX_CS
stays at the old value. 1. Why the "Memory Limit" log might be relevant You mentioned a log from a month ago: 
Memory limit exceeded... be:192.168.35.252
. * While that specific log is old, it suggests your CNs have a history of running out of memory. * If a CN is currently under heavy memory pressure (even if the Pod says "Running"), its RPC service (port 9060) can become unresponsive or the process can "zombie." This causes the 
link rpc channel failed
error you see today. 2. How to fix the "Link RPC Channel Failed" error You need to verify that the FE can actually reach the CN host listed in your error: 
starrocks-share-data-mode-cn-0.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local
. Step A: Test Connectivity from the FE Pod Exec into one of your FE pods and try to ping or telnet to that CN:
Copy code
bash
# From inside the FE pod
curl -v starrocks-share-data-mode-cn-0.starrocks-share-data-mode-cn-search.spinnaker.svc.cluster.local:9060
* If this times out or says "Connection refused," the network/DNS in your Kubernetes cluster is broken, or the CN process has hung. Step B: Check 
SHOW COMPUTE NODES
 Run this in your SQL client:
Copy code
sql
SHOW COMPUTE NODES;
Check the 
IP
and 
Alive
columns. If the IP for 
cn-0
does not match the IP that the DNS name resolves to, the FE has "stale" info. Step C: Restart the "unreachable" CN If the CN pod is "healthy" but refusing RPC connections, the easiest fix is to force a restart so it re-registers with the FE:
Copy code
bash
kubectl delete pod starrocks-share-data-mode-cn-0 -n spinnaker
3. Cleaning up the "Stuck" Transactions Once the connection is fixed, the FE should automatically retry the "Publish" tasks. If they remain stuck: 1. Check Version Gap: Run 
SHOW PARTITIONS FROM <table_name>
. If 
VisibleVersion
is much lower than 
CompactVersion
, the metadata is definitely waiting on these RPCs. 2. Wait for the "PublishDaemon": The FE has a background thread that retries these every minute. Once the CN is reachable, you should see 
VisibleVersion
catch up to 
CompactVersion
, and your 
MAX_CS
will finally drop. Summary Your compaction is working, but it's not finishing because the FE can't "shake hands" with the CN at the end. Focus on the network/RPC connectivity between your FE and 
cn-0
. References • faq/shared_data_faq.md • administration/management/compaction.md • faq/loading/Loading_faq.md • deployment/sr_operator.md References • administration/management/compaction.md
3 Views
Open in Slack
PreviousNext
Powered by