A few Redshift monitoring queries

monitoring.sql

-- check kds_landing stats
SELECT * FROM metrics.kds_landing_stats ORDER BY stats_update_time DESC;

-- get row counts for each date in metrics.metrics
SELECT TRUNC(interval_start) as interval_date, COUNT(*) as row_count
       FROM metrics.metrics
GROUP BY interval_date
ORDER BY interval_date;

-- get stats for refresh duration for each materialized view
SELECT mv_name,
       MIN(duration_ms) AS min_duration_ms,
       MAX(duration_ms) AS max_duration_ms,
       AVG(duration_ms) AS avg_duration_ms
FROM (SELECT mv_name, starttime, endtime, DATEDIFF(MILLISECONDS, starttime, endtime) AS duration_ms
      FROM svl_mv_refresh_status
      WHERE refresh_type = 'Auto'
        AND schema_name = 'metrics')
GROUP BY mv_name;

-- get stats for intervals between refreshes for each materialized view
SELECT mv_name,
       MIN(last_refresh_minutes) as min_last_refresh_minutes,
       MAX(last_refresh_minutes) as max_last_refresh_minutes,
       AVG(last_refresh_minutes) as avg_last_refresh_minutes,
       MEDIAN(last_refresh_minutes) as med_last_refresh_minutes
FROM (
SELECT mv_name,
       starttime,
       COALESCE(
           DATEDIFF(MINUTES,
               LAG(starttime) OVER
                   (PARTITION BY mv_name ORDER BY starttime),
               starttime),
           0)
           AS last_refresh_minutes
FROM svl_mv_refresh_status
      WHERE refresh_type = 'Auto'
        AND schema_name = 'metrics'
ORDER BY starttime)
WHERE last_refresh_minutes != 0
GROUP BY mv_name;