Redshift Diagnostics

Redshift Diagnostics

When to use

Any Redshift investigation where the console alone is insufficient — query performance, WLM tuning, storage management, data loading, cluster operations, Spectrum, data sharing, Serverless configuration, or security issues.

Investigation workflow

Step 1 — Collect and triage

# List provisioned clusters
aws redshift describe-clusters --query 'Clusters[*].{id:ClusterIdentifier,status:ClusterStatus,nodes:NumberOfNodes,type:NodeType}'

# List Serverless workgroups
aws redshift-serverless list-workgroups --query 'workgroups[*].{name:workgroupName,status:status,baseRPU:baseCapacity}'
version: "1.0.0"
last_updated: "2025-04-12"

# Check cluster health via CloudWatch
aws cloudwatch get-metric-statistics --namespace AWS/Redshift \
  --metric-name HealthStatus --dimensions Name=ClusterIdentifier,Value=<cluster-id> \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 300 --statistics Average

# Check disk space usage
aws cloudwatch get-metric-statistics --namespace AWS/Redshift \
  --metric-name PercentageDiskSpaceUsed --dimensions Name=ClusterIdentifier,Value=<cluster-id> \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 300 --statistics Maximum

# Check CPU utilization
aws cloudwatch get-metric-statistics --namespace AWS/Redshift \
  --metric-name CPUUtilization --dimensions Name=ClusterIdentifier,Value=<cluster-id> \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 300 --statistics Average

# Run a query via Redshift Data API
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT query, elapsed, substring FROM svl_qlog ORDER BY starttime DESC LIMIT 20"

Triage returns:

• Cluster/workgroup state, node count, node type
• Disk space percentage, CPU utilization, health status
• Recent query execution times and patterns

If the cluster status is not "available", that IS the root cause domain. Don't chase query or storage symptoms.

Step 2 — Domain deep dive (only if needed)

# Check WLM queue configuration
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM stv_wlm_service_class_config WHERE service_class >= 6"

# Check running and queued queries
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM stv_wlm_query_state"

# Check lock contention
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM svv_transactions WHERE lockable_object_type IS NOT NULL AND granted = 'f'"

# Check table skew and distribution
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT schema, \"table\", diststyle, skew_rows FROM svv_table_info WHERE skew_rows > 4.0 ORDER BY skew_rows DESC"

# Check COPY errors
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM stl_load_errors ORDER BY starttime DESC LIMIT 20"

# CloudTrail for Redshift API events
aws cloudtrail lookup-events --lookup-attributes AttributeKey=EventSource,AttributeValue=redshift.amazonaws.com --max-results 20

Read references/redshift-guardrails.md before concluding on any Redshift issue.

Step 3 — Detailed investigation (low-confidence cases only)

# Query execution plan analysis
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM svl_query_summary WHERE query = <query-id> ORDER BY stm, seg, step"

# Check VACUUM status
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM svv_table_info WHERE unsorted > 5 OR empty > 20 ORDER BY unsorted DESC"

# Check Spectrum query details
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM svl_s3query_summary ORDER BY starttime DESC LIMIT 10"

# Check concurrency scaling activity
aws redshift-data execute-statement --cluster-identifier <cluster-id> --database <db> --db-user <user> \
  --sql "SELECT * FROM stl_concurrency_scaling_usage ORDER BY starttime DESC LIMIT 20"

# Data sharing status
aws redshift describe-data-shares --data-share-arn <share-arn>

# Snapshot status
aws redshift describe-cluster-snapshots --cluster-identifier <cluster-id> --query 'Snapshots[*].{id:SnapshotIdentifier,status:Status,type:SnapshotType,time:SnapshotCreateTime}'

Tool quick reference

Gotchas: Redshift

These are the mistakes commonly made during Redshift troubleshooting.

• Leader node vs compute nodes — the leader node handles SQL parsing, query planning, and final result aggregation. Compute nodes store data and execute query fragments. Queries that run entirely on the leader (e.g., SELECT 1, functions with no table reference) don't use compute nodes at all. System tables like SVL_QLOG are on the leader.
• WLM queues control concurrency — the default WLM configuration allows 5 concurrent queries per queue. Queries beyond the concurrency limit wait in the queue. Auto WLM is recommended for most workloads as it dynamically adjusts concurrency and memory allocation.
• COPY is the fastest way to load data — not INSERT. COPY loads from S3, DynamoDB, or remote hosts in parallel across all compute nodes. Single-row INSERTs are extremely slow because each one is a separate transaction. Always use COPY for bulk loading.
• Distribution keys determine data placement across nodes — choosing the wrong distribution key causes data skew (one node has much more data than others) and excessive network redistribution during joins. Check svv_table_info for skew_rows > 4.0.
• Sort keys affect scan performance — compound sort keys are useful for queries that filter on leading columns. Interleaved sort keys help when queries filter on different columns but have higher VACUUM overhead. Unsorted data degrades query performance.
• VACUUM is needed after deletes and updates — Redshift does not automatically reclaim space from deleted rows for all operations. Auto-VACUUM handles DELETE reclamation but manual VACUUM SORT may still be needed for sort key maintenance. Check the "unsorted" percentage in svv_table_info.
• Redshift Spectrum queries S3 directly via external tables — Spectrum uses compute nodes to scan S3 data. It does NOT load data into Redshift storage. Performance depends on S3 file format (Parquet/ORC best), file size (100MB-1GB optimal), and partition pruning.
• Concurrency scaling adds transient clusters for burst read workloads — it only handles read queries, not writes. Concurrency scaling clusters are billed per-second. It activates automatically when WLM queues have waiting queries.
• Serverless has RPU-based pricing — Redshift Serverless uses Redshift Processing Units (RPUs) instead of fixed node types. Base capacity is configurable (min 8 RPU). You pay for compute time in RPU-seconds. There are no nodes to manage.
• Cross-database queries have limitations — you can query across databases in the same cluster using three-part notation (database.schema.table), but you cannot write across databases. External schemas (Spectrum) cannot be queried cross-database.
• Maintenance windows can cause brief unavailability — during maintenance, the cluster may be restarted. Schedule maintenance windows during low-traffic periods. Deferred maintenance is available but has limits.
• Resize operations — classic resize creates a new cluster and migrates data (hours). Elastic resize adds/removes nodes in minutes but has node type constraints. RA3 nodes with managed storage make elastic resize much faster.

Anti-hallucination rules

1. Always cite specific system table output (STL_, SVL_, SVV_, STV_), CloudWatch metrics, or AWS CLI output as evidence.
2. Never suggest SSH access to Redshift nodes — Redshift is fully managed with no OS-level access.
3. Never recommend INSERT for bulk loading — always use COPY from S3.
4. Never claim VACUUM is unnecessary — it is required for sort key maintenance and space reclamation after deletes.
5. Never suggest changing distribution style without analyzing join patterns and data skew first.
6. Spend no more than 2 minutes on any single hypothesis. Pivot if inconclusive.

30 runbooks

Runbooks are organized by failure domain. Use the appropriate runbook based on the symptom category.