starrocks/starrocks-debug-skills/materialized-view

-- Refresh history with error details
SELECT task_name, state, error_message, start_time, finish_time,
       TIMESTAMPDIFF(SECOND, start_time, finish_time) AS duration_sec
FROM information_schema.task_runs
WHERE task_name LIKE 'mv-%'
ORDER BY start_time DESC LIMIT 20;
 
-- Running refresh tasks right now
SELECT * FROM information_schema.task_runs
WHERE state = 'RUNNING';

-- Queries running during refresh (to identify resource contention)
SHOW PROC '/current_queries' \G
 
-- Analyze a specific refresh profile (get profile_id from task_runs)
ANALYZE PROFILE FROM '<profile_id>';

-- Check if a query uses MV rewrite
EXPLAIN LOGICAL SELECT ...;
-- Look for "SCAN [mv_name]" in output — if the base table name appears instead, rewrite did not fire
 
-- Trace why rewrite was not used (v3.2+)
TRACE LOGS MV SELECT ...;
TRACE REASON MV SELECT ...;
 
-- Sync MV: check via EXPLAIN
EXPLAIN SELECT ... FROM <table>;
-- Look for "Rollup: <mv_name>" and "PREAGGREGATION: ON"

-- Overall MV health
SHOW MATERIALIZED VIEWS;
 
-- Refresh history
SELECT * FROM information_schema.task_runs
WHERE task_name = 'mv-<mv_id>' \G
 
-- Resource usage during refresh
SHOW PROC '/current_queries' \G

# Refresh start/end events
grep "Start refresh materialized view\|End refresh materialized view" fe.log | tail -30
 
# Refresh failures
grep "refresh.*failed\|MV.*inactive\|refresh.*timeout" fe.log | tail -30
 
# Scheduler timing issues
grep "initialDelay\|scheduleRefresh\|task_runs" fe.log | tail -30

-- Run on FE (any node, but Leader gives authoritative state)
SHOW MATERIALIZED VIEWS;

SELECT task_name, state, error_message,
       start_time, finish_time,
       TIMESTAMPDIFF(SECOND, start_time, finish_time) AS duration_sec
FROM information_schema.task_runs
WHERE task_name LIKE 'mv-%'
ORDER BY start_time DESC LIMIT 10;

# Check FE GC log for OOM evidence
grep "Full GC\|OutOfMemory" fe.gc.log | tail -20
 
# Check FE heap usage around refresh time
grep "heap" fe.log | grep -A2 "Start refresh" | tail -20

-- Profile the last failed refresh (if profile_id is available)
ANALYZE PROFILE FROM '<profile_id>';
-- Look for: which operator consumed the most memory
-- Look for: which stage timed out

SHOW MATERIALIZED VIEWS;
-- Check: inactive_reason column
-- Common values: "base table schema changed", "manually set inactive"

-- Confirm MV is active and fresh
SHOW MATERIALIZED VIEWS;
-- is_active = true AND last_refresh_time is recent
 
-- Trace the rewrite decision
TRACE LOGS MV SELECT ...;
-- Look for MV_REWRITE_FAIL_REASON in output

# Check if FE was recently restarted
grep "FE started\|system start" fe.log | tail -5
 
# Check initialDelay calculation in scheduler
grep "initialDelay\|scheduleRefresh" fe.log | tail -20

-- Check next_refresh_time vs now
SELECT mv_name, last_refresh_time, next_refresh_time,
       TIMESTAMPDIFF(MINUTE, now(), next_refresh_time) AS minutes_until_refresh
FROM information_schema.materialized_views;
-- If minutes_until_refresh is very large (e.g., > 1440 min for a daily MV) → schedule broken

ALTER MATERIALIZED VIEW <mv_name> SET ('session.enable_spill' = 'true');

# In fe.conf or JVM args
-Xmx16g   # increase from default (e.g., 8g)

-- Partition-by-partition refresh (incremental)
ALTER MATERIALIZED VIEW <mv_name>
REFRESH ASYNC START('2024-01-01 00:00:00') EVERY (INTERVAL 1 HOUR);
 
-- Or set partition batch size
ALTER MATERIALIZED VIEW <mv_name>
SET ('mv_refresh_partition_batch_num' = '1');

CANCEL REFRESH MATERIALIZED VIEW <mv_name>;
-- Or deactivate until fixed:
ALTER MATERIALIZED VIEW <mv_name> INACTIVE;

-- v3.2+: default 1 hour; pre-v3.2: default 5 min
ALTER MATERIALIZED VIEW <mv_name>
SET ('session.insert_timeout' = '7200');   -- seconds

-- Get the refresh SQL from MV definition
SHOW CREATE MATERIALIZED VIEW <mv_name>;
 
-- Run the SQL manually with profiling to find the bottleneck
SET enable_profile = true;
-- Execute the refresh SQL
-- Then: SHOW PROC '/current_queries' for profile_id
ANALYZE PROFILE FROM '<profile_id>';

-- Create a dedicated resource group
CREATE RESOURCE GROUP rg_mv
TO (role='root')
WITH (
  'cpu_core_limit' = '4',
  'mem_limit' = '0.3',
  'big_query_cpu_second_limit' = '0',
  'big_query_scan_rows_limit' = '0',
  'big_query_mem_limit' = '0',
  'type' = 'normal'
);
 
-- Assign to MV
ALTER MATERIALIZED VIEW <mv_name>
SET ('resource_group' = 'rg_mv');

ALTER MATERIALIZED VIEW <mv_name> ACTIVE;

-- Check what changed in the base table
SHOW CREATE TABLE <base_table>;
SHOW CREATE MATERIALIZED VIEW <mv_name>;
 
-- Drop and recreate the MV with updated definition
DROP MATERIALIZED VIEW <mv_name>;
CREATE MATERIALIZED VIEW <mv_name>
REFRESH ASYNC
AS <updated_select>;

SHOW MATERIALIZED VIEWS;
-- Check is_active; reactivate if needed

-- Allow rewrite even when MV is slightly stale
ALTER MATERIALIZED VIEW <mv_name>
SET ('query_rewrite_consistency' = 'LOOSE');
 
-- Or set allowed staleness window in seconds
ALTER MATERIALIZED VIEW <mv_name>
SET ('mv_rewrite_staleness_second' = '300');

-- Ensure rewrite is enabled at session level
SET enable_materialized_view_rewrite = true;
SET materialized_view_rewrite_mode = 'default';  -- or 'force'

TRACE LOGS MV SELECT ...;
-- Read MV_REWRITE_FAIL_REASON in output

EXPLAIN SELECT ... FROM <table>;
-- Must see: "Rollup: <mv_name>" and "PREAGGREGATION: ON"
-- If see base table name → sync MV not hitting