http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> - what is the easiest way to pull t...
# questions-and-troubleshooting
a
@Rocky - what is the easiest way to pull the schedule of all asynchronous materialized view updates across all databases in starrocks
r
The easiest way to pull the schedule of all asynchronous materialized view updates across all databases is to query the 
tasks
table in the 
information_schema
database. When an asynchronous materialized view (MV) is created, StarRocks automatically generates a corresponding background task to manage its refresh schedule. You can join the 
materialized_views
and 
tasks
metadata views to get a clear list of MV names, their parent databases, and their defined schedules. Recommended Query
Copy code
sql
SELECT 
    mv.TABLE_SCHEMA AS database_name,
    mv.TABLE_NAME AS mv_name,
    t.SCHEDULE AS refresh_schedule,
    mv.REFRESH_TYPE,
    mv.IS_ACTIVE
FROM information_schema.materialized_views mv
JOIN information_schema.tasks t 
  ON mv.TASK_NAME = t.TASK_NAME
ORDER BY database_name, mv_name;
Key Columns in the Result: * database_name: The database where the MV resides. * mv_name: The name of the materialized view. * refresh_schedule: * For scheduled MVs, this will display the interval (e.g., 
START "..." EVERY "..."
). * For manual MVs, this will display 
MANUAL
. * IS_ACTIVE: Shows whether the MV is currently active (only active MVs will trigger their schedules). Alternative: Quick View If you only need a quick list of all background task schedules (including those created via 
SUBMIT TASK
) without joining for the MV name:
Copy code
sql
SELECT DATABASE, TASK_NAME, SCHEDULE 
FROM information_schema.tasks 
WHERE TASK_NAME LIKE 'mv-%';
(Note: Materialized view tasks are prefixed with 
mv-
followed by their internal ID.) References • sql-reference/information_schema/tasks.md • sql-reference/information_schema/materialized_views.md • sql-reference/information_schema/task_runs.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/information_schema/fe_tablet_schedules.md
a
@Rocky - what if I also want average task duration
r
To get the average task duration along with the refresh schedule for all asynchronous materialized views, you can join the 
materialized_views
, 
tasks
, and 
task_runs
tables in the 
information_schema
database. Recommended Query This query joins the metadata to provide the database name, MV name, schedule, and the average time (in seconds) it took for successful refresh tasks to complete.
Copy code
sql
SELECT 
    mv.TABLE_SCHEMA AS database_name,
    mv.TABLE_NAME AS mv_name,
    t.SCHEDULE AS refresh_schedule,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, tr.CREATE_TIME, tr.FINISH_TIME)), 2) AS avg_total_duration_sec,
    COUNT(tr.QUERY_ID) AS total_runs
FROM information_schema.materialized_views mv
JOIN information_schema.tasks t 
  ON mv.TASK_NAME = t.TASK_NAME
JOIN information_schema.task_runs tr 
  ON t.TASK_NAME = tr.TASK_NAME
WHERE tr.STATE = 'SUCCESS' -- Only average successful runs for accuracy
GROUP BY 1, 2, 3
ORDER BY database_name, mv_name;
Key Considerations: * Total Duration vs. Execution Duration: * The query above uses 
TIMESTAMPDIFF(SECOND, tr.CREATE_TIME, tr.FINISH_TIME)
, which calculates the total time (including any time spent waiting in the task queue). * If you only want the actual execution time (excluding queuing time), you can check the 
PROCESS_TIME
column in the 
task_runs
table, though its format can vary by StarRocks version (often a string like "1s"). * Performance History: By default, StarRocks retains a limited history of task runs (typically 10,000 records or a few days, depending on your 
task_runs_keep_max_num
and 
task_runs_ttl_second
configurations). * Partitioned MVs: For materialized views that refresh individual partitions, one refresh operation may trigger multiple "task runs." This query will average those individual sub-task durations. Related System Views • 
information_schema.tasks
: Contains the 
SCHEDULE
definition. • 
information_schema.task_runs
: Contains execution metrics like 
CREATE_TIME
, 
FINISH_TIME
, and 
STATE
. • 
information_schema.materialized_views
: Links the user-friendly MV name to the internal 
TASK_NAME
. References • sql-reference/information_schema/task_runs.md • sql-reference/information_schema/materialized_views.md • sql-reference/information_schema/tasks.md • using_starrocks/async_mv/materialized_view_task_run_details.md References • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/information_schema/task_runs.md • faq/Others.md • best_practices/query_tuning/query_profile_operator_metrics.md • using_starrocks/running_queries.md
a
@Rocky - in starrocks when a nested materialized view gets upgraded - does it trigger upgrade of the underlying ones?
r
In StarRocks, the relationship between nested materialized views (MVs) primarily follows a bottom-up dependency flow. Here is how it works regarding updates and "upgrades": 1. Does refreshing a top-level MV trigger underlying ones? No. By default, if you manually refresh a nested (top-level) MV, it does not trigger a refresh of the underlying base MVs. * When a top-level MV is refreshed, StarRocks checks the status of its base tables (including other MVs). * If the underlying MVs are "stale" (out of sync with their own base tables), the top-level MV will typically refresh based on the current data present in those underlying MVs, rather than forcing them to update first. _ The official documentation states: _"The refreshing strategy for each materialized view does not affect the materialized views on the upper or lower layers."* 2. Does refreshing an underlying MV trigger the top-level one? Yes (if configured). This is the "cascading" mechanism StarRocks supports. * If your top-level MV is set to 
REFRESH ASYNC
(without a specific interval), it can be triggered automatically whenever its base tables change. * If an underlying MV completes a refresh, it counts as a data change for the top-level MV, which can then trigger a cascading refresh of the top-level view. 3. What if "Upgrade" refers to Schema Changes? If you "upgrade" an underlying MV by changing its schema (e.g., dropping and recreating it or using 
ALTER
), it may have the opposite effect: * Inactivation: StarRocks automatically sets a dependent (top-level) MV to Inactive if its base tables or underlying MVs undergo significant schema changes (like being dropped and recreated). * Manual Fix: You must manually set the top-level MV back to active using 
ALTER MATERIALIZED VIEW <mv_name> ACTIVE
once the underlying dependencies are stable. Summary Table Action | Effect on Underlying MVs | Effect on Top-level MVs Refresh Top-level | None. | Updates data using current base table state. Refresh Underlying | Updates itself. | May trigger top-level refresh (if 
REFRESH ASYNC
). Schema Change (Underlying) | Updates itself. | Often makes top-level MV Inactive. Recommendation: To ensure an entire nested pipeline is up-to-date, it is best to refresh them bottom-up (starting from the one closest to the raw base tables) or rely on the 
ASYNC
trigger mechanism to propagate changes upward. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md
a
@Rocky - for the average task duration - what if I want to look at a week worth of runs?
r
To pull the average task duration for the last 7 days, you can add a date filter to the 
task_runs
join. Recommended Query
Copy code
sql
SELECT 
    mv.TABLE_SCHEMA AS database_name,
    mv.TABLE_NAME AS mv_name,
    t.SCHEDULE AS refresh_schedule,
    ROUND(AVG(TIMESTAMPDIFF(SECOND, tr.CREATE_TIME, tr.FINISH_TIME)), 2) AS avg_duration_sec_7d,
    COUNT(tr.QUERY_ID) AS total_runs_7d
FROM information_schema.materialized_views mv
JOIN information_schema.tasks t 
  ON mv.TASK_NAME = t.TASK_NAME
JOIN information_schema.task_runs tr 
  ON t.TASK_NAME = tr.TASK_NAME
WHERE tr.STATE = 'SUCCESS'
  AND tr.CREATE_TIME >= NOW() - INTERVAL 7 DAY
GROUP BY 1, 2, 3
ORDER BY avg_duration_sec_7d DESC;
Important Caveats on History Retention Whether you can actually see a full week's worth of data depends on two Frontend (FE) configuration parameters. By default, StarRocks is configured to keep exactly 7 days of history, but volume limits may apply: 1. 
task_runs_ttl_second
: The default is 604,800 seconds (7 days). If this has been lowered by your administrator, older runs will be automatically purged. 2. 
task_runs_max_history_number
: The default is 10,000 records. If your MVs refresh very frequently (e.g., every minute), you might hit this 10,000-record cap before reaching the 7-day time limit. How to Check Your Retention Settings If the query returns fewer days than expected, you can check your cluster's current limits with:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%task_runs%';
* If you need to keep more history, an administrator can increase these values (e.g., 
ADMIN SET FRONTEND CONFIG ("task_runs_ttl_second" = "1209600");
for 14 days). References • administration/management/FE_configuration.md • sql-reference/information_schema/task_runs.md • using_starrocks/async_mv/materialized_view_task_run_details.md References • administration/management/FE_configuration.md • using_starrocks/async_mv/materialized_view_task_run_details.md
Open in Slack
PreviousNext
Powered by