Q: After migrating an application to AWS, the RDS instance is running very slowly. How would you troubleshoot the performance issue and identify the root cause?

Open RDS **Performance Insights**. Inspect Average Active Sessions (AAS) against the Max vCPU line. Break down load by: - **Wait States**: Are sessions waiting on `io/table/scan`, `Lock:row`, `CPU`, or `Client:ClientRead`? - **Top SQL**: Identify the top 3 queries consuming 80% of database time.

During standard database migrations (pg_dump/restore or AWS DMS), table data is imported but query optimizer statistics are empty. Without updated histogram statistics, the query planner chooses catastrophic Sequential Scans instead of Index Scans.

-- For PostgreSQL: Immediately re-analyze all tables post-migration
ANALYZE VERBOSE;
-- For MySQL:
ANALYZE TABLE orders, customers, transactions;

Check CloudWatch metrics: `ReadIOPS`, `WriteIOPS`, `ReadLatency`, `WriteLatency`, and `DiskQueueDepth`. If average read latency exceeds 10ms, storage is bottlenecking. Upgrade storage to gp3 with provisioned 12,000 IOPS or io2.

# Check CloudWatch RDS Latency
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReadLatency \
  --dimensions Name=DBInstanceIdentifier,Value=prod-db \
  --start-time $(date -u -v-2h +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period 300 --statistics Average

Ensure the application EC2/EKS pods are located in the **same AWS Region and Availability Zone** as the primary RDS instance. If the app is in `us-east-1a` and primary RDS is in `us-east-1b`, every database round-trip incurs 1-2ms of cross-AZ network latency.