Amazon Redshift Troubleshooting Checklist – DBA Guide

Amazon Redshift Troubleshooting Checklist (Production DBA Guide)

This guide provides a structured, real-world troubleshooting checklist for Amazon Redshift performance and stability issues.

1. Cluster Health Check (Always First)

☐ Cluster status = Available
☐ No resize operation running
☐ No maintenance in progress
☐ No node replacement events

Check Recent System Alerts:


SELECT event_time, event, solution
FROM stl_alert_event_log
ORDER BY event_time DESC
LIMIT 20;

2. Running & Long Queries

Check Running Queries:


SELECT pid,
       user_name,
       starttime,
       datediff(seconds, starttime, getdate()) AS runtime_sec,
       substring(querytxt,1,200)
FROM stv_recents
WHERE status = 'Running'
ORDER BY runtime_sec DESC;

Check Historical Slow Queries:


SELECT query,
       total_exec_time/1000000 AS exec_time_sec,
       rows
FROM stl_query
ORDER BY exec_time_sec DESC
LIMIT 10;

3. WLM (Workload Management) Check


SELECT service_class,
       total_queue_time/1000000 AS queue_time_sec,
       total_exec_time/1000000 AS exec_time_sec
FROM stl_wlm_query
ORDER BY queue_time_sec DESC;

☐ Queue time high?
☐ Concurrency too high?
☐ Using Auto WLM?
☐ Query Monitoring Rules triggered?

4. CPU & CloudWatch Metrics

☐ CPUUtilization > 85%
☐ WLMQueueLength increasing
☐ CommitQueueLength high
☐ DatabaseConnections near limit
☐ PercentageDiskSpaceUsed > 80%

5. Table Health Check

Skew Check:


SELECT "table", skew_rows, skew_sortkey1
FROM svv_table_info
ORDER BY skew_rows DESC;

Unsorted & Statistics:


SELECT "table", unsorted, stats_off
FROM svv_table_info
WHERE unsorted > 20 OR stats_off > 20;

☐ Run VACUUM if unsorted > 20%
☐ Run ANALYZE if stats_off > 20%

6. Disk Usage Check


SELECT node, SUM(used) AS used_mb
FROM stv_partitions
GROUP BY node;

☐ Disk usage > 80%
☐ Data skew between nodes
☐ Old data cleanup required

7. Locks & Blocking Sessions


SELECT *
FROM svv_transactions
WHERE lockable_object_type IS NOT NULL;

☐ Identify blocking PID
☐ Validate before terminating

8. Error Check


SELECT query, trim(error) AS error_message, starttime
FROM stl_error
ORDER BY starttime DESC
LIMIT 20;

9. Query Plan Analysis


EXPLAIN <your_query>;

☐ DS_BCAST_INNER
☐ Nested Loop Join
☐ Large Sequential Scan
☐ Data Redistribution

10. Large Table Scan Check


SELECT query,
       sum(rows) AS total_rows_scanned
FROM stl_scan
GROUP BY query
ORDER BY total_rows_scanned DESC
LIMIT 10;

Production Troubleshooting Flow

Check cluster health
Check running queries
Check WLM queue time
Check CPU & disk usage
Check skew & statistics
Check locks
Review execution plan
Apply corrective action

Most Common Root Causes

Missing ANALYZE
High WLM queue wait
Data skew
Too many concurrent queries
Disk > 85% full
Large DELETE without VACUUM
Nested loop joins
Wrong distribution key

Author: Production DBA Guide

Search This Blog

CHARAN-REDSHIFT

Redshift troubleshooting