-- 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

SELECT mv_name, last_refresh_time, next_refresh_time
FROM information_schema.materialized_views;
-- next_refresh_time far in the future despite short refresh interval → broken

-- Manual refresh to unblock immediately
REFRESH MATERIALIZED VIEW <mv_name>;
 
-- Reset the schedule definition
ALTER MATERIALIZED VIEW <mv_name>
REFRESH ASYNC START(NOW()) EVERY (INTERVAL 1 HOUR);

ADMIN SET FRONTEND CONFIG ("task_runs_concurrency" = "8");

-- Primary signal: MV active and refreshing successfully
SHOW MATERIALIZED VIEWS;
-- is_active = true
-- last_refresh_state = SUCCESS
-- last_refresh_time is recent (within expected refresh interval)
 
-- Confirm query rewrite is working
EXPLAIN LOGICAL SELECT ...;
-- Must show "SCAN [mv_name]", not the base table name
 
-- Confirm refresh completing within timeout
SELECT task_name, state, 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 5;
-- state = SUCCESS and duration_sec within expected range
 
-- Confirm no OOM or timeout errors in recent runs
SELECT task_name, state, error_message
FROM information_schema.task_runs
WHERE task_name LIKE 'mv-%'
  AND state = 'FAILED'
ORDER BY start_time DESC LIMIT 5;
-- Should return 0 rows

-- Check via EXPLAIN
EXPLAIN SELECT ... FROM table;
-- Look for "Rollup: <mv_name>" (not the base table name)
-- Check PREAGGREGATION: ON = storage layer returns pre-aggregated data

MV refresh task starts and acquires DB-level write lock
  ↓ observable: FE log "[MV] Start refresh materialized view <mv_name>" timestamp; jstack shows MVRefreshTask holding Database.rwLock write lock
Concurrent read queries attempt to acquire read lock on same DB
  ↓ observable: jstack shows query threads BLOCKED on Database.rwLock.readLock()
Query execution delayed until write lock released
  ↓ observable: SHOW PROC '/current_queries' shows queries in PENDING state during refresh window
Query P99 latency spikes during refresh window

MV refresh executes complex aggregation SQL; FE heap exhausted during intermediate result materialization
  ↓ observable: fe.gc.log shows consecutive Full GC events; heap near -Xmx limit
Refresh task killed by OOM; job marked failed
  ↓ observable: SHOW MATERIALIZED VIEWS shows last_refresh_state = FAILED; last_refresh_error_message contains OOM detail
MV data timestamp frozen at last successful refresh
  ↓ observable: last_refresh_time diverges from current time; MV data growing stale
Query rewrite uses stale MV data without explicit error
  ↓ observable: query results return outdated aggregates; EXPLAIN shows OlapScanNode on MV table

MV refresh tasks triggered at high frequency (e.g., every few minutes) across many MVs
  ↓ observable: FE log shows rapid succession of "Start refresh" / "End refresh" log lines
Each refresh creates metadata objects accumulating in FE heap
  ↓ observable: FE memory profile shows MV-related heap objects not GC'd between cycles
FE Full GC triggered repeatedly
  ↓ observable: fe.gc.log shows Full GC pauses >5s; FE unresponsive during GC
In-flight refresh task misses heartbeat during GC pause; task fails
  ↓ observable: FE log "refresh task timeout" or "refresh job failed: heartbeat lost"
Failed refresh queues more retries; each creating more metadata objects → feedback loop
  ↓ observable: SHOW MATERIALIZED VIEWS shows multiple MVs with FAILED state accumulating