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?
Root cause analysis runbook for database query degradation after migrating an on-premises database to AWS RDS.
Want to master this scenario in a live sandbox? KodeKloud's PostgreSQL Database Administration & High Availability Course covers this exact problem with hands-on terminal drills.
🛠️ Production Runbook & Step-by-Step Resolution
Direct Answer: When a newly migrated database on RDS performs significantly worse than on-premises, the degradation is rarely caused by the AWS cloud itself. It is caused by architectural mismatches: ungathered post-migration optimizer statistics, undersized storage IOPS, missing parameter group tuning, network latency across AZs, or missing indexes that were omitted during dump/restore.
Analyze AWS Performance Insights & Database Load (AAS)
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.
Check Table Statistics & Execute VACUUM / ANALYZE
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;
Verify Storage IOPS & gp2/gp3 Volume Bottlenecks
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
Audit Network Placement (Multi-AZ & Application Colocation)
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.
- Use RDS Performance Insights to analyze Average Active Sessions and dominant wait events.
- Run ANALYZE immediately: newly restored databases lack optimizer statistics and default to table scans.
- Inspect storage read/write latency in CloudWatch to detect IOPS throttling.
- Verify that application servers and the primary database reside in the same Availability Zone.
Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:
- Applying exclusive-lock DDL table migrations on live production databases during peak traffic windows when configuring Performance Running Slowly DDL migrations that acquire an exclusive table lock block all concurrent queries, queuing connections until the database connection pool exhausts.
- Allowing high-frequency queries to trigger full table scans without appropriate composite indexing when configuring Performance Running Slowly Sequential table scans saturate database CPU and disk I/O, evicting frequently queried data from the buffer cache and causing cascading timeouts.
- Disabling referential integrity constraints during bulk ETL imports without post-import consistency validation when configuring Performance Running Slowly Skipping relational consistency checks risks introducing orphaned records and referential integrity violations into production tables.
- Relying on database snapshot backups that have never been verified through automated restoration drills when configuring Performance Running Slowly Backups that have never been tested with automated restore verification frequently fail during real disaster recovery scenarios.