Troubleshooting RDS: Common Issues and Solutions

Introduction to RDS Troubleshooting

Amazon Relational Database Service (RDS) is a managed database service that simplifies the setup, operation, and scaling of relational databases in the cloud. While AWS handles many operational tasks like backups, patching, and scaling, developers and database administrators still encounter issues that require hands-on troubleshooting. Understanding how to diagnose and resolve common RDS problems is essential for maintaining application availability and performance.

This tutorial covers the most frequent RDS issues you will face, including connectivity problems, performance bottlenecks, storage limitations, replication lag, and snapshot failures. Each section provides practical diagnostic commands and proven solutions you can apply immediately.

Why RDS Troubleshooting Matters

When your RDS instance experiences problems, the impact cascades through your entire application stack. Slow queries frustrate users, connectivity failures cause downtime, and storage exhaustion can bring your database to a complete halt. Because RDS is a managed service, you do not have root access to the underlying operating system, which means traditional database administration techniques must be adapted to work within AWS constraints.

Effective troubleshooting reduces mean time to resolution (MTTR), prevents data loss, and helps you make informed decisions about scaling and architecture. It also helps you avoid unnecessary costs by identifying whether an issue requires a larger instance or simply an optimized query.

Common RDS Issues and How to Diagnose Them

1. Connectivity Problems

Connectivity issues are among the most common RDS problems. They typically manifest as application errors indicating the database cannot be reached, connection timeouts, or authentication failures. The root causes usually fall into three categories: security group misconfigurations, VPC or subnet issues, and incorrect endpoint usage.

To diagnose connectivity, start by verifying the endpoint and port from the RDS console or CLI:

aws rds describe-db-instances \
  --db-instance-identifier my-database \
  --query 'DBInstances[0].Endpoint.[Address,Port]' \
  --output table

Next, check the security group attached to your RDS instance to ensure it allows inbound traffic on the correct port from your application's IP range or security group:

aws ec2 describe-security-groups \
  --group-ids sg-0123456789abcdef0 \
  --query 'SecurityGroups[0].IpPermissions' \
  --output table

If the security group looks correct, test network connectivity from your application server using the telnet or nc command:

# Test TCP connectivity to the RDS endpoint
nc -zv my-database.abc123xyz.us-east-1.rds.amazonaws.com 5432

# Alternative using telnet
telnet my-database.abc123xyz.us-east-1.rds.amazonaws.com 5432

If the connection fails, verify that your RDS instance is in a subnet that has a route to the internet (if accessing from outside the VPC) or that your application is in the same VPC or a peered VPC with proper routing. For instances in private subnets, ensure a NAT gateway or VPN/Direct Connect is configured for external access.

A common gotcha is the publicly accessible flag. If your instance is in a public subnet but this flag is set to false, external connections will fail:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --publicly-accessible \
  --apply-immediately

2. High CPU Utilization

High CPU usage on an RDS instance can degrade query performance and cause application timeouts. The first step is to identify whether the issue is caused by a specific query, a workload spike, or insufficient instance resources. Enable Enhanced Monitoring to get operating system-level metrics at one-second granularity:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --monitoring-interval 1 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role \
  --apply-immediately

For PostgreSQL instances, query the pg_stat_activity view to identify long-running queries consuming CPU:

SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    usename
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
ORDER BY duration DESC;

For MySQL instances, use the SHOW PROCESSLIST command or query the performance schema:

-- Identify long-running queries
SELECT
    id,
    user,
    host,
    time,
    state,
    LEFT(query, 100) AS query_preview
FROM information_schema.processlist
WHERE time > 60
ORDER BY time DESC;

-- Check queries by CPU consumption using performance schema
SELECT
    digest_text,
    count_star,
    avg_timer_wait/1000000000 AS avg_ms,
    sum_timer_wait/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Once you identify the problematic query, use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to analyze the execution plan and add appropriate indexes:

-- PostgreSQL: Analyze query execution
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 12345
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC;

-- Add a composite index to optimize the query
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

If queries are already optimized and CPU remains high, consider scaling up to a larger instance class:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --db-instance-class db.r6g.2xlarge \
  --apply-immediately

3. Storage Full Errors

RDS instances have allocated storage that can fill up over time. When storage reaches capacity, the database stops accepting writes, causing application failures. AWS provides the FreeStorageSpace CloudWatch metric to monitor available storage. Set an alarm to alert you before storage becomes critical:

aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-Low-Free-Storage" \
  --alarm-description "Alert when RDS free storage drops below 10GB" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --statistic Average \
  --period 300 \
  --threshold 10000 \
  --comparison-operator LessThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=my-database \
  --evaluation-periods 1 \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

To identify what is consuming storage, connect to your PostgreSQL database and check table sizes:

SELECT
    schemaname AS schema,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

For MySQL, check table sizes with:

SELECT
    table_schema AS database_name,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Common storage consumers include large log tables, temporary tables, and bloated indexes. For PostgreSQL, run VACUUM FULL to reclaim space from dead tuples:

-- Standard vacuum (does not lock the table)
VACUUM ANALYZE large_table;

-- Full vacuum (locks the table but reclaims disk space)
VACUUM FULL large_table;

-- Check for dead tuples before vacuuming
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

If cleanup is not sufficient, increase the allocated storage. RDS allows you to increase storage without downtime:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --allocated-storage 500 \
  --apply-immediately

Note that you cannot decrease allocated storage after increasing it. Consider enabling storage autoscaling as a preventive measure:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --max-allocated-storage 1000 \
  --apply-immediately

4. Read Replica Lag

Read replicas are used to offload read traffic from the primary instance, but replication lag can cause stale reads and inconsistent application behavior. For PostgreSQL, check replication lag using the pg_stat_replication view on the primary instance:

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    now() - replay_timestamp AS lag_interval
FROM pg_stat_replication;

For MySQL, check replica lag using the SHOW REPLICA STATUS command (or SHOW SLAVE STATUS on older versions):

SHOW REPLICA STATUS\G

-- Key fields to examine:
-- Seconds_Behind_Master
-- Replica_IO_Running
-- Replica_SQL_Running
-- Last_IO_Error
-- Last_SQL_Error

Common causes of replication lag include:

• Long-running write transactions on the primary that create large WAL/binlog entries
• Insufficient compute resources on the replica
• Different index structures between primary and replica
• Heavy read queries on the replica that compete with replication threads
• Network bottlenecks between the primary and replica

To resolve lag, first identify whether the replica is under-provisioned. Check the replica's CPU and IOPS metrics in CloudWatch. If the replica instance class is smaller than the primary, scale it up:

aws rds modify-db-instance \
  --db-instance-identifier my-replica \
  --db-instance-class db.r6g.2xlarge \
  --apply-immediately

For PostgreSQL, if a single large transaction is causing lag, consider breaking it into smaller batches. You can also tune the max_wal_senders and wal_sender_timeout parameters:

-- Modify parameter group (via AWS CLI or console)
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-custom-params \
  --parameters "ParameterName=max_wal_senders,ParameterValue=20,ApplyMethod=immediate" \
               "ParameterName=wal_sender_timeout,ParameterValue=60s,ApplyMethod=immediate"

For MySQL replicas, ensure the replica has sufficient IOPS and consider tuning the innodb_flush_log_at_trx_commit parameter for better write performance on the replica:

aws rds modify-db-parameter-group \
  --db-parameter-group-name my-mysql-params \
  --parameters "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=2,ApplyMethod=pending-reboot"

5. Slow Query Performance

Slow queries are a frequent complaint even when CPU and storage are healthy. The first step is to enable and review the slow query log. For MySQL, enable the slow query log in a custom parameter group:

aws rds modify-db-parameter-group \
  --db-parameter-group-name my-mysql-params \
  --parameters "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \
               "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \
               "ParameterName=log_queries_not_using_indexes,ParameterValue=1,ApplyMethod=immediate"

Publish the slow query log to CloudWatch Logs for easier analysis:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --enable-cloudwatch-logs-exports '["slowquery"]' \
  --apply-immediately

For PostgreSQL, use the pg_stat_statements extension to track query performance:

-- Enable the extension (requires a custom parameter group)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Query the slowest statements
SELECT
    queryid,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    rows,
    LEFT(query, 150) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Once you identify slow queries, common optimization strategies include:

• Adding missing indexes based on query patterns
• Rewriting queries to avoid full table scans
• Using ANALYZE to update table statistics for the query planner
• Partitioning large tables to reduce scan sizes
• Using materialized views for expensive aggregate queries

Here is an example of updating statistics and analyzing a query plan:

-- Update statistics for the query planner
ANALYZE orders;
ANALYZE order_items;

-- Review the execution plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.customer_id, SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= '2024-01-01'
GROUP BY o.id, o.customer_id
ORDER BY total DESC
LIMIT 100;

If the execution plan shows a sequential scan on a large table, add an index:

CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at);

CREATE INDEX CONCURRENTLY idx_order_items_order_id
ON order_items (order_id);

6. Snapshot and Backup Failures

Automated backups and manual snapshots are critical for disaster recovery, but they can fail for several reasons. Common causes include insufficient storage for snapshot operations, IAM permission issues, and instance states that prevent snapshot creation. Check the status of recent snapshots:

aws rds describe-db-snapshots \
  --db-instance-identifier my-database \
  --snapshot-type automated \
  --query 'DBSnapshots[0:5].[DBSnapshotIdentifier,Status,SnapshotCreateTime]' \
  --output table

If automated backups are failing, verify that the automated backup retention period is set correctly. A value of 0 disables automated backups:

aws rds describe-db-instances \
  --db-instance-identifier my-database \
  --query 'DBInstances[0].BackupRetentionPeriod'

-- Set a retention period of 7 days
aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --backup-retention-period 7 \
  --apply-immediately

To create a manual snapshot and verify its completion:

# Create a manual snapshot
aws rds create-db-snapshot \
  --db-instance-identifier my-database \
  --db-snapshot-identifier my-database-snapshot-20240115

# Wait for the snapshot to complete
aws rds wait db-snapshot-available \
  --db-snapshot-identifier my-database-snapshot-20240115

# Verify the snapshot status
aws rds describe-db-snapshots \
  --db-snapshot-identifier my-database-snapshot-20240115 \
  --query 'DBSnapshots[0].[Status,SnapshotCreateTime,AllocatedStorage]' \
  --output table

If snapshot creation is consistently slow, check whether your instance has sufficient IOPS. Snapshots consume I/O resources, and instances with low provisioned IOPS may experience degraded performance during backup windows. Consider moving the backup window to a low-traffic period:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --preferred-backup-window "03:00-04:00" \
  --apply-immediately

7. Connection Exhaustion

Each RDS instance has a maximum number of concurrent connections determined by the instance class and database engine. When connections are exhausted, new connections fail and applications return errors. Check current connection counts:

-- PostgreSQL
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active_connections,
       count(*) FILTER (WHERE state = 'idle') AS idle_connections
FROM pg_stat_activity;

-- Check max connections setting
SHOW max_connections;

-- MySQL
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

If connections are consistently near the limit, the most common solution is to implement connection pooling. For PostgreSQL, use RDS Proxy or PgBouncer:

# Create an RDS Proxy with connection pooling
aws rds create-db-proxy \
  --db-proxy-name my-db-proxy \
  --engine-family POSTGRESQL \
  --auth "AuthScheme=SECRETS,SecretArn=arn:aws:secretsmanager:us-east-1:123456789012:secret:db-creds-abc123,IAMAuth=DISABLED" \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-abc123 subnet-def456 \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --require-tls \
  --connection-pool-config "MaxConnectionsPercent=90,MaxIdleConnectionsPercent=50,ConnectionBorrowTimeout=120"

For application-level connection pooling in Node.js, use a pool with proper configuration:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'my-database.abc123xyz.us-east-1.rds.amazonaws.com',
  port: 5432,
  database: 'myapp',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,              // Maximum connections in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// Always release connections back to the pool
async function getUserById(id) {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
  } finally {
    client.release();
  }
}

You can also increase the max_connections parameter in a custom parameter group, but be cautious because too many connections can exhaust memory:

aws rds modify-db-parameter-group \
  --db-parameter-group-name my-pg-params \
  --parameters "ParameterName=max_connections,ParameterValue=200,ApplyMethod=immediate"

8. Multi-AZ Failover Issues

Multi-AZ deployments provide high availability, but failover events can cause unexpected downtime if your application is not configured to handle them. Monitor failover events using EventBridge (formerly CloudWatch Events):

aws events put-rule \
  --name "RDS-Failover-Events" \
  --event-pattern '{
    "source": ["aws.rds"],
    "detail-type": ["RDS DB Instance Event"],
    "detail": {
      "EventCategories": ["failover"]
    }
  }'

To test a failover manually and verify your application recovers properly:

aws rds reboot-db-instance \
  --db-instance-identifier my-database \
  --force-failover

After a failover, verify the new standby is synchronized:

aws rds describe-db-instances \
  --db-instance-identifier my-database \
  --query 'DBInstances[0].MultiAZ,
           DBInstances[0].StatusInfos,
           DBInstances[0].SecondaryAvailabilityZone' \
  --output table

Ensure your application uses the RDS endpoint rather than a direct IP address, as the endpoint automatically redirects to the new primary after failover. Also set appropriate connection timeouts and retry logic in your application:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'my-database.abc123xyz.us-east-1.rds.amazonaws.com',
  port: 5432,
  database: 'myapp',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
  connectionTimeoutMillis: 10000,
  idleTimeoutMillis: 30000,
});

// Implement retry logic for transient failures
async function queryWithRetry(queryFn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await queryFn();
    } catch (err) {
      if (attempt === maxRetries) throw err;
      const delay = Math.pow(2, attempt) * 1000;
      console.warn(`Attempt ${attempt} failed, retrying in ${delay}ms...`, err.message);
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }
}

// Usage
const user = await queryWithRetry(() => getUserById(123));

Best Practices for RDS Troubleshooting

Proactive Monitoring

The best troubleshooting strategy is to prevent issues before they impact users. Set up comprehensive CloudWatch alarms for key metrics:

# Create alarms for critical RDS metrics
aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-High-CPU" \
  --metric-name CPUUtilization \
  --namespace AWS/RDS \
  --statistic Average \
  --period 300 \
  --threshold 80 \
  --comparison-operator GreaterThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=my-database \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-High-Memory" \
  --metric-name FreeableMemory \
  --namespace AWS/RDS \
  --statistic Average \
  --period 300 \
  --threshold 524288000 \
  --comparison-operator LessThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=my-database \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-Disk-Queue-Depth" \
  --metric-name DiskQueueDepth \
  --namespace AWS/RDS \
  --statistic Average \
  --period 300 \
  --threshold 10 \
  --comparison-operator GreaterThanThreshold \
  --dimensions Name=DBInstanceIdentifier,Value=my-database \
  --evaluation-periods 3 \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

Use Performance Insights

Performance Insights is a built-in RDS feature that provides a visual dashboard of database performance. Enable it for all production instances:

aws rds modify-db-instance \
  --db-instance-identifier my-database \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --apply-immediately

You can also query Performance Insights data programmatically:

aws pi describe-dimension-keys \
  --service-type RDS \
  --identifier db-mydatabase \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --metric db.load.avg \
  --group-by '{"Group":"db.sql","Dimensions":["db.sql.token"]}' \
  --max-results 10 \
  --output table

Maintain a Runbook

Document common issues and their resolutions in a runbook that your team can reference during incidents. Include the specific CLI commands, SQL queries, and AWS console steps for each scenario. A well-maintained runbook dramatically reduces resolution time during high-pressure situations.

Regular Maintenance

Schedule regular maintenance tasks to prevent common issues:

• Run VACUUM ANALYZE (PostgreSQL) or OPTIMIZE TABLE (MySQL) regularly to maintain table health
• Review and archive old log data to prevent storage bloat
• Test snapshot restores quarterly to verify backup integrity
• Review slow query logs weekly and optimize the top offenders
• Apply database engine patches during maintenance windows to stay current
• Review parameter group settings periodically to ensure they match your workload

Use Infrastructure as Code

Define your RDS configurations using CloudFormation or Terraform so you can reproduce environments and track changes:

resource "aws_db_instance" "main" {
  identifier             = "my-database"
  engine                 = "postgres"
  engine_version         = "15.4"
  instance_class         = "db.r6g.2xlarge"
  allocated_storage      = 200
  max_allocated_storage  = 500
  storage_type           = "gp3"
  multi_az               = true
  publicly_accessible    = false
  backup_retention_period = 7
  preferred_backup_window = "03:00-04:00"
  maintenance_window     = "sun:04:00-sun:05:00"
  monitoring_interval    = 1
  monitoring_role_arn    = aws_iam_role.rds_monitoring.arn
  performance_insights_enabled = true
  performance_insights_retention_period = 7
  copy_tags_to_snapshot  = true

  tags = {
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

Conclusion

Troubleshooting RDS requires a systematic approach that combines AWS-specific tools like CloudWatch, Performance Insights, and the RDS CLI with traditional database diagnostic techniques. By understanding the common issues covered in this tutorial — connectivity problems, CPU spikes, storage exhaustion, replication lag, slow queries, backup failures, connection exhaustion, and failover challenges — you can quickly identify root causes and apply targeted solutions. The key to minimizing downtime is proactive monitoring, well-documented runbooks, and regular maintenance. Invest time in setting up alerts, enabling Performance Insights, and implementing connection pooling before issues arise, and your RDS infrastructure will remain resilient and performant even as your workload grows.