AWS Rds Deep

AWS RDS Deep Skill

Deep analysis of AWS RDS instances with parallel execution and anti-hallucination guardrails.

Relationship to other AWS skills:

• aws-rds-deep/ → RDS deep-dive analysis (Performance Insights, proxies, global DBs)
• aws/ → "How to execute" (parallel patterns, throttling, output format)
• aws-rightsizing/ → RDS rightsizing recommendations

CRITICAL: Parallel Execution Requirement

ALL independent operations MUST run in parallel using background jobs (&) and wait.

#!/bin/bash
export AWS_PAGER=""

for db in $databases; do
  get_db_performance "$db" &
done
wait

Helper Functions

#!/bin/bash
export AWS_PAGER=""

# List RDS instances with Performance Insights status
list_rds_instances() {
  aws rds describe-db-instances \
    --output text \
    --query 'DBInstances[].[DBInstanceIdentifier,Engine,EngineVersion,DBInstanceClass,DBInstanceStatus,PerformanceInsightsEnabled,MultiAZ,StorageType,AllocatedStorage]'
}

# Get Performance Insights resource metrics
get_pi_metrics() {
  local resource_arn=$1 hours=${2:-1}
  local end_time start_time
  end_time=$(date +%s)
  start_time=$((end_time - hours * 3600))
  aws pi get-resource-metrics \
    --service-type RDS \
    --identifier "$resource_arn" \
    --metric-queries '[{"Metric":"db.load.avg"}]' \
    --start-time "$start_time" --end-time "$end_time" \
    --period-in-seconds 300 \
    --output text \
    --query 'MetricList[0].DataPoints[*].[Timestamp,Value]' | tail -10
}

# Get top wait events from PI
get_pi_wait_events() {
  local resource_arn=$1 hours=${2:-1}
  local end_time start_time
  end_time=$(date +%s)
  start_time=$((end_time - hours * 3600))
  aws pi get-resource-metrics \
    --service-type RDS \
    --identifier "$resource_arn" \
    --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.wait_event","Limit":10}}]' \
    --start-time "$start_time" --end-time "$end_time" \
    --period-in-seconds $((hours * 3600)) \
    --output text \
    --query 'MetricList[0].DataPoints[0].Value'
}

# List RDS event subscriptions
list_event_subscriptions() {
  aws rds describe-event-subscriptions \
    --output text \
    --query 'EventSubscriptionsList[].[CustSubscriptionId,Status,SnsTopicArn,SourceType,Enabled]'
}

# List RDS proxies
list_proxies() {
  aws rds describe-db-proxies \
    --output text \
    --query 'DBProxies[].[DBProxyName,Status,EngineFamily,VpcId,IdleClientTimeout,RequireTLS]' 2>/dev/null
}

# Get read replica lag
get_replica_lag() {
  local instance_id=$1
  local end_time start_time
  end_time=$(date -u +"%Y-%m-%dT%H:%M:%S")
  start_time=$(date -u -d "1 hour ago" +"%Y-%m-%dT%H:%M:%S" 2>/dev/null || date -u -v-1H +"%Y-%m-%dT%H:%M:%S")
  aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS --metric-name ReplicaLag \
    --dimensions Name=DBInstanceIdentifier,Value="$instance_id" \
    --start-time "$start_time" --end-time "$end_time" \
    --period 300 --statistics Average Maximum \
    --output text --query 'Datapoints[*].[Timestamp,Average,Maximum]' | sort -k1 | tail -5
}

Common Operations

1. Instance Inventory with PI Status

#!/bin/bash
export AWS_PAGER=""
aws rds describe-db-instances \
  --output text \
  --query 'DBInstances[].[DBInstanceIdentifier,Engine,DBInstanceClass,DBInstanceStatus,PerformanceInsightsEnabled,MultiAZ,ReadReplicaDBInstanceIdentifiers[0]]' | sort -k2

2. Performance Insights DB Load Analysis

#!/bin/bash
export AWS_PAGER=""
PI_INSTANCES=$(aws rds describe-db-instances \
  --output text \
  --query 'DBInstances[?PerformanceInsightsEnabled==`true`].[DbiResourceId,DBInstanceIdentifier]')
END=$(date +%s)
START=$((END - 3600))
echo "$PI_INSTANCES" | while read resource_id db_name; do
  {
    load=$(aws pi get-resource-metrics \
      --service-type RDS --identifier "db-$resource_id" \
      --metric-queries '[{"Metric":"db.load.avg"}]' \
      --start-time "$START" --end-time "$END" \
      --period-in-seconds 3600 \
      --output text \
      --query 'MetricList[0].DataPoints[0].Value')
    printf "%s\tDBLoad:%s\n" "$db_name" "${load:-N/A}"
  } &
done
wait

3. Read Replica Lag Monitoring

#!/bin/bash
export AWS_PAGER=""
REPLICAS=$(aws rds describe-db-instances \
  --output text \
  --query 'DBInstances[?ReadReplicaSourceDBInstanceIdentifier!=null].DBInstanceIdentifier')
END=$(date -u +"%Y-%m-%dT%H:%M:%S")
START=$(date -u -d "1 hour ago" +"%Y-%m-%dT%H:%M:%S" 2>/dev/null || date -u -v-1H +"%Y-%m-%dT%H:%M:%S")
for replica in $REPLICAS; do
  aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS --metric-name ReplicaLag \
    --dimensions Name=DBInstanceIdentifier,Value="$replica" \
    --start-time "$START" --end-time "$END" \
    --period 300 --statistics Average Maximum \
    --output text --query "Datapoints[-1].[\"$replica\",Average,Maximum]" &
done
wait

4. RDS Proxy Health

#!/bin/bash
export AWS_PAGER=""
PROXIES=$(aws rds describe-db-proxies --output text --query 'DBProxies[].DBProxyName' 2>/dev/null)
for proxy in $PROXIES; do
  {
    info=$(aws rds describe-db-proxies --db-proxy-name "$proxy" \
      --output text --query 'DBProxies[].[DBProxyName,Status,EngineFamily,IdleClientTimeout,RequireTLS]')
    targets=$(aws rds describe-db-proxy-targets --db-proxy-name "$proxy" \
      --output text --query 'Targets[].[RdsResourceId,TargetHealth.State,TargetHealth.Description,Type]' 2>/dev/null)
    printf "%s\nTargets:\n%s\n" "$info" "$targets"
  } &
done
wait

5. Event Subscription and Recent Events

#!/bin/bash
export AWS_PAGER=""
aws rds describe-event-subscriptions \
  --output text \
  --query 'EventSubscriptionsList[].[CustSubscriptionId,Status,SourceType,Enabled]' &

aws rds describe-events --duration 1440 \
  --output text \
  --query 'Events[].[Date,SourceIdentifier,SourceType,Message]' | tail -20 &
wait

Anti-Hallucination Rules

1. Performance Insights identifier format - PI uses DbiResourceId (e.g., db-ABCDEFGHIJKLMN), NOT DBInstanceIdentifier. Get it from describe-db-instances.
2. PI must be enabled - Performance Insights is not enabled by default on all instance types. Check PerformanceInsightsEnabled before querying PI APIs.
3. ReplicaLag units vary by engine - MySQL/MariaDB ReplicaLag is in seconds. Aurora replica lag is in milliseconds. PostgreSQL uses different replication metrics.
4. Storage autoscaling - MaxAllocatedStorage being set does not mean autoscaling is active. Check if MaxAllocatedStorage > AllocatedStorage.
5. Aurora vs RDS - Aurora instances appear in describe-db-instances but are managed differently. Aurora uses clusters (describe-db-clusters), reader/writer endpoints, and shared storage.

Output Format

Present results as a structured report:

Aws Rds Deep Report
═══════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Common Pitfalls

• PI data retention: Free tier retains PI data for 7 days. Paid tier (long-term retention) stores up to 2 years.
• Global databases: Aurora global databases span regions. The primary cluster is read-write; secondary clusters are read-only. Use describe-global-clusters.
• RDS Proxy connection limits: Proxy manages connection pooling but has its own limits. Check MaxConnectionsPercent and MaxIdleConnectionsPercent.
• CloudWatch statistics syntax: Use spaces not commas: --statistics Average Maximum.
• Multi-AZ failover: During failover, the CNAME endpoint switches to the standby. This causes a brief outage (usually 60-120 seconds).