Database Troubleshooting and Migration: Performance Insights, Slow Queries, Connections, DMS and Schema Conversion

AWS SysOps Associate 50 min min read Updated: Nov 17, 2026 Advanced
Database Troubleshooting and Migration: Performance Insights, Slow Queries, Connections, DMS and Schema Conversion
Advanced Topic 3 of 3

A Troubleshooting Method

  1. What changed? Deployment, traffic, parameter, maintenance, failover (check RDS events and CloudTrail)
  2. Which resource is saturated? CPU, memory, IOPS, network, connections, locks
  3. Which queries? Performance Insights top SQL and wait events
  4. Fix at the right level: query or index first, then configuration, then bigger hardware, then architecture (replicas, cache)
  5. Verify and document with before/after metrics

Performance Insights

  • Shows DB load as average active sessions (AAS) compared with the vCPU count. Load above vCPU line means sessions are waiting
  • Slice by SQL, wait events, hosts, users and databases to see who and what causes load
  • Wait events tell why sessions wait: CPU, io/… (disk reads), lock/… (contention), Client, Commit
  • Keep 7 days free, up to 24 months paid. Combine with CloudWatch Database Insights for fleet views
Wait patternTypical meaningFix
Mostly CPUHeavy queries, missing indexes, too many connectionsIndex and rewrite queries, scale up, add replicas, cache
io/table/sql/handler, IO:DataFileReadFull table scans or cold cacheAdd indexes, enlarge buffer cache (instance memory), faster storage
lock/*, Lock:transactionidRow or table locks, long transactionsShorten transactions, commit sooner, fix ordering
Commit, WALWrite, log file syncWrite latencyProvision more IOPS, batch commits
Client:ClientReadApp is slow to read resultsFix app or network

Slow Query Logs and Plans

sql -- MySQL: parameter group sets slow_query_log=1, long_query_time=1, log_output=FILE EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'open'; SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS; -- last deadlock, lock waits -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42; SELECT pid, state, wait_event, now() - query_start AS runtime, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC; SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; -- vacuum health
  • Publish slow query and error logs to CloudWatch Logs and query with Logs Insights
  • Add an index that matches WHERE and JOIN columns, avoid SELECT *, avoid functions on indexed columns, use pagination
  • PostgreSQL: watch autovacuum, bloat and transaction ID wraparound. MySQL: watch the history list length and long transactions

Resource Bottlenecks

SymptomInvestigate
High CPUUtilizationTop SQL, connection count, parameter group, burst credits for t classes
Low FreeableMemory, swappingInstance too small, too many connections (each uses memory), large work memory
High DiskQueueDepth / latencyIOPS limit of volume or instance, change to gp3 with more IOPS or io2, add cache
FreeStorageSpace fallingLogs, temp tables, binlog retention, growth. Enable storage autoscaling
Connections spikePooling, leaked connections, RDS Proxy, retries storming after failover
Replica lagWrite volume, long queries on replica, replica size

Locks and Deadlocks

  • Lock wait: one transaction waits for another. Find the blocker (pg_stat_activity with pg_blocking_pids, or information_schema.innodb_trx) and fix the long transaction
  • Deadlock: two transactions wait for each other. The DB kills one. Fix by accessing tables in the same order, keeping transactions short, and retrying on error

AWS DMS (Database Migration Service)

ComponentMeaning
Replication instance (or Serverless)EC2-based engine that runs the tasks. Size it for the load and place it in the same VPC/Region as the target when possible
Source / target endpointsConnection details, with TLS and secrets
Migration taskFull load, CDC only (change data capture) or full load + CDC for minimal downtime
Table mappingsSelection and transformation rules (rename, filter)
ValidationCompares source and target data
Premigration assessmentFinds problems before running
  • Homogeneous (MySQL to Aurora MySQL): DMS alone, or native tools. Heterogeneous (Oracle to PostgreSQL): use AWS Schema Conversion Tool (SCT) or DMS Schema Conversion first to convert schema and code, then DMS for data
  • Cutover: run full load + CDC, wait until lag is near zero, stop the app, let the last changes apply, validate, switch the app endpoint
  • Source prerequisites: binary logging (MySQL), logical replication/wal_level (PostgreSQL), supplemental logging (Oracle), enough retention of logs
  • Monitor CDCLatencySource, CDCLatencyTarget, FullLoadThroughputRowsTarget and task logs in CloudWatch
DMS problemFix
Cannot connect to endpointSecurity groups, route, DNS, credentials, TLS, source reachable from replication instance
CDC not startingBinary logs or WAL settings, user privileges, log retention too short
LOB columns slow or truncatedUse limited or full LOB mode with proper size settings
High CDC latencyLarger replication instance, parallel apply, check target indexes and locks
Foreign key or trigger errors on targetDisable triggers and FKs during full load, create secondary objects after
Validation failuresCheck data type mapping and ongoing writes

Migration Tools Summary

NeedTool
Move DB with minimal downtimeDMS full load + CDC
Convert schema between enginesSCT / DMS Schema Conversion
Same engine, simpleNative dump/restore, snapshot restore, read replica promotion
Move serversApplication Migration Service
Move filesDataSync

Practice Tasks

  1. Turn on Performance Insights and run a load test, then find the top SQL
  2. Create a missing-index scenario and fix it, comparing EXPLAIN before and after
  3. Enable slow query log export to CloudWatch Logs and query it
  4. Create a deadlock on purpose and read the log
  5. Run a DMS full load + CDC between two RDS instances and validate

Conclusion

Troubleshoot databases in order: what changed, which resource, which query, then fix. Performance Insights and wait events point to the cause, slow query logs and EXPLAIN guide indexes, locks need shorter transactions, and DMS with CDC (plus SCT for heterogeneous moves) delivers low-downtime migrations.

Quick Summary: method; Performance Insights; slow queries; bottlenecks; locks; DMS; tools.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators