RDS Performance & Monitoring

RDS Performance Insights, Enhanced Monitoring, slow query logs, and CloudWatch metrics together give a complete picture of database health. Knowing which knobs to turn — from storage autoscaling to index recommendations — separates reactive from proactive operations.

Performance Insights & Enhanced Monitoring

# Performance Insights — visualize database load, identify top SQL
# Shows DB Load (in vCPUs) broken down by wait events, SQL, users, hosts
# Free tier: 7 days retention; paid: up to 2 years
# Enable on existing instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --apply-immediately

# Query Performance Insights API for top SQL (last 1 hour)
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-ABC123DEFGHIJKLMNOPQRST \
  --start-time $(date -u -d "1 hour ago" +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 60 \
  --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.sql","Limit":10}}]'

# Enhanced Monitoring — OS-level metrics at 1-60 second granularity
# Metrics: CPU steal, file system I/O, process list — not available in CloudWatch
# Requires an IAM role with the AmazonRDSEnhancedMonitoringRole managed policy
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --monitoring-interval 15 \
  --monitoring-role-arn arn:aws:iam::123:role/rds-enhanced-monitoring-role

# Metrics appear in CloudWatch under RDS → Per-Second Metrics
# View in Logs Insights: log group /aws/rds/instance/<id>/osmetrics

CloudWatch Metrics & Slow Query Log

# Key CloudWatch metrics for RDS (namespace: AWS/RDS)
# CPUUtilization          — % CPU; alert > 80% for sustained period
# DatabaseConnections     — current open connections; alert if approaching max_connections
# FreeStorageSpace        — bytes; alert < 10% of allocated storage
# FreeableMemory          — bytes; low memory causes paging and performance degradation
# ReadIOPS / WriteIOPS    — I/O operations per second
# ReadLatency/WriteLatency — avg per-operation latency in seconds; alert > 20ms
# ReplicaLag              — seconds behind primary (for read replicas)
# BurstBalance            — % of gp2 I/O burst credits remaining (gp3 has fixed baseline)

# Set CloudWatch alarm for low storage
aws cloudwatch put-metric-alarm \
  --alarm-name "rds-low-storage-myapp" \
  --alarm-description "Free storage below 10 GB" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --statistic Average \
  --dimensions "Name=DBInstanceIdentifier,Value=myapp-prod-db" \
  --period 300 \
  --threshold 10737418240 \
  --comparison-operator LessThanThreshold \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:us-east-1:123:ops-alerts

# Enable slow query log (PostgreSQL via parameter group)
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --parameters \
    "ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=none,ApplyMethod=immediate" \
    "ParameterName=log_lock_waits,ParameterValue=1,ApplyMethod=immediate"

# View slow query logs
aws rds describe-db-log-files \
  --db-instance-identifier myapp-prod-db \
  --filename-contains "postgresql"

aws rds download-db-log-file-portion \
  --db-instance-identifier myapp-prod-db \
  --log-file-name error/postgresql.log.2024-01-15.15 \
  --starting-token 0 \
  --output text

Storage Autoscaling & Parameter Tuning

# Storage autoscaling — automatically increases storage when free space is low
# Triggers when: free space < 10% of allocated AND < 5 GB AND low for 5 min
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --max-allocated-storage 1000 \
  --apply-immediately
# Set max-allocated-storage to cap runaway scaling costs

# Key PostgreSQL parameter tuning (rule of thumb values)
# shared_buffers        = 25% of instance RAM (cache frequently accessed data)
# effective_cache_size  = 75% of instance RAM (hint for query planner)
# work_mem              = RAM / (max_connections * 4) (per-sort/hash operation)
# maintenance_work_mem  = 10% of RAM (for VACUUM, CREATE INDEX)
# max_connections       = depends on app; use RDS Proxy to keep this low (100-200)
# wal_buffers           = 16MB (WAL write buffer)
# checkpoint_completion_target = 0.9 (spread checkpoint writes over 90% of checkpoint interval)
# random_page_cost      = 1.1 for SSDs (default 4.0 is for HDDs)

# Check current parameter values
aws rds describe-db-parameters \
  --db-parameter-group-name myapp-postgres16-params \
  --query "Parameters[?ParameterName=='max_connections' || ParameterName=='shared_buffers']"

# Index recommendations — use pg_stat_user_tables to find missing indexes
# (Run directly in PostgreSQL)
# SELECT schemaname, tablename, seq_scan, idx_scan,
#        seq_tup_read, idx_tup_fetch
# FROM pg_stat_user_tables
# WHERE seq_scan > idx_scan
# ORDER BY seq_tup_read DESC
# LIMIT 20;

# Find slow queries in pg_stat_statements
# SELECT query, calls, total_exec_time/calls AS avg_ms,
#        rows/calls AS avg_rows
# FROM pg_stat_statements
# ORDER BY avg_ms DESC
# LIMIT 20;

# VACUUM and ANALYZE (PostgreSQL maintenance)
# RDS runs autovacuum — check its status:
# SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
# FROM pg_stat_user_tables
# ORDER BY n_dead_tup DESC;