Rds Mysql Diagnostics

RDS MySQL Diagnostics

When to use

Any RDS MySQL investigation where the console alone is insufficient — instance launch failures, slow queries, InnoDB buffer pool pressure, lock waits/deadlocks, connectivity issues, parameter group tuning, replication lag, Multi-AZ failover, backup/recovery, storage performance, RDS Proxy, encryption, or upgrade troubleshooting.

Investigation workflow

Step 1 — Collect and triage

aws rds describe-db-instances --db-instance-identifier <instance-id>
aws rds describe-events --source-identifier <instance-id> --source-type db-instance --duration 1440
aws rds describe-db-log-files --db-instance-identifier <instance-id>
aws rds download-db-log-file-portion --db-instance-identifier <instance-id> --log-file-name error/mysql-error-running.log
aws rds describe-db-parameters --db-parameter-group-name <param-group>

Step 2 — Performance deep dive

aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name CPUUtilization \
  --dimensions Name=DBInstanceIdentifier,Value=<instance-id> \
  --start-time <start> --end-time <end> --period 300 --statistics Average
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name FreeableMemory ...
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReadIOPS ...
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name WriteIOPS ...
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReadLatency ...
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name DatabaseConnections ...
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name FreeStorageSpace ...
aws pi get-resource-metrics --service-type RDS \
  --identifier db-<resource-id> \
  --metric-queries '[{"Metric":"db.load.avg"}]' \
  --start-time <start> --end-time <end> --period-in-seconds 300

Step 3 — MySQL-specific diagnostics (via SQL)

-- Active threads and lock waits
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM performance_schema.data_lock_waits;

-- InnoDB buffer pool hit ratio
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') a,
     (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') b;

-- Slow query analysis
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20;
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Replication status (on read replica)
SHOW REPLICA STATUS\G

Read references/guardrails.md before concluding on any RDS MySQL issue.

Tool quick reference

Gotchas: RDS MySQL

• InnoDB is the only supported storage engine for production. MyISAM tables are not crash-safe and not replicated reliably. RDS defaults to InnoDB.
• innodb_buffer_pool_size defaults to 75% of instance memory via {DBInstanceClassMemory*3/4}. Over-allocating causes OOM kills.
• No SUPER privilege on RDS MySQL. Use rds_superuser_role or stored procedures like mysql.rds_kill, mysql.rds_set_configuration.
• Read replicas use MySQL native async replication. Lag is expected under write-heavy workloads. Monitor ReplicaLag CloudWatch metric.
• Multi-AZ uses synchronous replication to a standby (not a read replica). Failover is automatic, typically 60-120 seconds. DNS endpoint stays the same.
• RDS Proxy pools connections at the proxy level. Application connection strings must point to the proxy endpoint, not the DB endpoint.
• Storage autoscaling: set --max-allocated-storage to enable. Scaling happens when free space < 10% and lasts > 5 minutes. Cooldown is 6 hours.
• Binary logging (binlog_format) must be ROW for replication. MIXED or STATEMENT can cause replication inconsistencies.
• max_connections is derived from instance memory: {DBInstanceClassMemory/12582880}. Override carefully — too many connections cause OOM.
• PITR restores to a new instance. You cannot restore in-place. The new instance gets a new endpoint.
• Major version upgrades (e.g., 5.7→8.0) require pre-upgrade checks. Use mysql-shell upgrade checker or RDS pre-upgrade validation.

Anti-hallucination rules

1. Always cite specific AWS CLI output, CloudWatch metrics, Performance Insights data, or MySQL query results as evidence.
2. No SUPER privilege on RDS. Never suggest SET GLOBAL for restricted variables or CHANGE MASTER TO directly.
3. MyISAM is not recommended on RDS. Never suggest MyISAM for production tables.
4. PITR creates a new instance. Never suggest in-place point-in-time restore.
5. Multi-AZ standby is not readable. Never suggest querying the Multi-AZ standby directly.
6. Spend no more than 2 minutes on any single hypothesis. Pivot if inconclusive.

Runbooks