Database Troubleshooting and Migration: Performance Insights, Slow Queries, Connections, DMS and Schema Conversion
A Troubleshooting Method
- What changed? Deployment, traffic, parameter, maintenance, failover (check RDS events and CloudTrail)
- Which resource is saturated? CPU, memory, IOPS, network, connections, locks
- Which queries? Performance Insights top SQL and wait events
- Fix at the right level: query or index first, then configuration, then bigger hardware, then architecture (replicas, cache)
- 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 pattern | Typical meaning | Fix |
Mostly CPU | Heavy queries, missing indexes, too many connections | Index and rewrite queries, scale up, add replicas, cache |
io/table/sql/handler, IO:DataFileRead | Full table scans or cold cache | Add indexes, enlarge buffer cache (instance memory), faster storage |
lock/*, Lock:transactionid | Row or table locks, long transactions | Shorten transactions, commit sooner, fix ordering |
Commit, WALWrite, log file sync | Write latency | Provision more IOPS, batch commits |
Client:ClientRead | App is slow to read results | Fix 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
| Symptom | Investigate |
High CPUUtilization | Top SQL, connection count, parameter group, burst credits for t classes |
Low FreeableMemory, swapping | Instance too small, too many connections (each uses memory), large work memory |
High DiskQueueDepth / latency | IOPS limit of volume or instance, change to gp3 with more IOPS or io2, add cache |
FreeStorageSpace falling | Logs, temp tables, binlog retention, growth. Enable storage autoscaling |
| Connections spike | Pooling, leaked connections, RDS Proxy, retries storming after failover |
| Replica lag | Write 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)
| Component | Meaning |
| 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 endpoints | Connection details, with TLS and secrets |
| Migration task | Full load, CDC only (change data capture) or full load + CDC for minimal downtime |
| Table mappings | Selection and transformation rules (rename, filter) |
| Validation | Compares source and target data |
| Premigration assessment | Finds 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 problem | Fix |
| Cannot connect to endpoint | Security groups, route, DNS, credentials, TLS, source reachable from replication instance |
| CDC not starting | Binary logs or WAL settings, user privileges, log retention too short |
| LOB columns slow or truncated | Use limited or full LOB mode with proper size settings |
| High CDC latency | Larger replication instance, parallel apply, check target indexes and locks |
| Foreign key or trigger errors on target | Disable triggers and FKs during full load, create secondary objects after |
| Validation failures | Check data type mapping and ongoing writes |
Migration Tools Summary
| Need | Tool |
| Move DB with minimal downtime | DMS full load + CDC |
| Convert schema between engines | SCT / DMS Schema Conversion |
| Same engine, simple | Native dump/restore, snapshot restore, read replica promotion |
| Move servers | Application Migration Service |
| Move files | DataSync |
Practice Tasks
- Turn on Performance Insights and run a load test, then find the top SQL
- Create a missing-index scenario and fix it, comparing
EXPLAIN before and after
- Enable slow query log export to CloudWatch Logs and query it
- Create a deadlock on purpose and read the log
- 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.