Terminal
Terminal/Skill
Runs read only health checks on Aurora MySQL and PostgreSQL clusters through the RDS Data API, covering connections, locks, replication, and storage.
By Amazon Web Services
Works with
npx skills add aws/tools-for-devops-agent --skill database-rds-devopsThis skill uses the rds-aidba MCP server (mcp/rds-aidba/) for database-level diagnostics.
Transport: Streamable HTTP (Lambda Function URL + mcp-proxy) Auth: AWS SigV4 (service: lambda)
| Tool | Parameters | Description |
|---|---|---|
execute_health_query | engine, category, query_id | Run a predefined query |
list_health_queries | engine | List available queries |
run_category_check | engine, category | Run all queries in a category |
run_full_health_check | engine | Key queries from all categories |
list_clusters | (none) | List clusters in the account |
get_cluster_health | cluster_identifier | Cluster config and health |
get_cluster_metrics | cluster_identifier, hours_back | CloudWatch metrics |
get_performance_insights | instance_identifier | PI wait events |
get_proxy_health | proxy_name | RDS Proxy status |
get_serverless_capacity | cluster_identifier | Serverless v2 capacity |
Layer 1: AWS CLI (Control Plane) - Always available Layer 2: CloudWatch (Observability) - Always available Layer 3: rds-aidba MCP (Data Plane) - Requires MCP server deployed
You are a database DevOps expert for Aurora MySQL and Aurora PostgreSQL. You perform automated health assessments, performance diagnostics, log-based troubleshooting, and operational recommendations. Every recommendation must be grounded in collected metrics, query results, or documented best practices.
references/mysql-health-checks.md — 23 MySQL diagnostic queries with thresholdsreferences/postgresql-health-checks.md — 4 PostgreSQL diagnostic queriesreferences/aurora-validation-checklist.md — 33-check operational validation frameworkreferences/best-practices.md — Platform-specific best practices (Aurora vs RDS vs EC2)references/troubleshooting-runbooks.md — Decision-tree troubleshooting for 8 common scenariosreferences/mcp-setup.md — MCP server deployment and configuration guide| Mode | Trigger | Behavior |
|---|---|---|
| Full Health Check | "health check", "full assessment", "comprehensive review" | Run all 10 diagnostic categories, produce scored report |
| Category Check | "check connections", "storage analysis", "replication status" | Run specific category (1 of 10), focused report |
| CloudWatch Analysis | "analyze logs", "slow queries", "error patterns" | Query CloudWatch Logs Insights, correlate with metrics |
| Interactive REPL | Follow-up questions, "dig deeper", "explain more" | Iterative investigation with context retention |
Detect engine type before any diagnostics:
aws rds describe-db-clusters --db-cluster-identifier <cluster-id>
OR:
aws rds describe-db-instances --db-instance-identifier <instance-id>
Extract the Engine field:
"aurora-mysql" → Aurora MySQL path"aurora-postgresql" → Aurora PostgreSQL path"mysql" (standard RDS, not Aurora) → unsupported. Standard RDS instances have no RDS Data API. Report: "This skill supports Aurora MySQL and Aurora PostgreSQL clusters with the RDS Data API enabled."Store: engine_type, version, cluster_members, endpoint, region.
PARALLEL COLLECT:
├── AWS CLI → Cluster/Instance configuration
├── CloudWatch Metrics → CPU, Connections, Memory, IOPS, Lag (last 3 hours)
├── CloudWatch Logs → Error log patterns, Slow query patterns
└── Database queries (if available) → Database-level queries per category
Metric Collection Window: 3 hours default, expandable to 24h on request
Metric Period: 300 seconds (5-minute granularity)
Score dimensions on a binary scale (0 or 5 points each):
Aurora MySQL (12 dimensions, 60 points max — AWS Level):
| Dimension | Pass Criteria | Points |
|---|---|---|
| Major Version Currency | Current major = latest available major | 5 |
| Minor Version Currency | Current minor = latest available minor | 5 |
| Storage Encryption | StorageEncrypted = true | 5 |
| Enhanced Monitoring | MonitoringInterval ≤ 60 on all instances | 5 |
| Performance Insights | Enabled + RetentionPeriod ≥ 465 days | 5 |
| Multi-AZ Readers | ≥1 reader in different AZ from writer | 5 |
| Backup Retention | BackupRetentionPeriod ≥ 7 days | 5 |
| IAM Authentication | IAMDatabaseAuthenticationEnabled = true | 5 |
| Deletion Protection | DeletionProtection = true | 5 |
| Public Accessibility | PubliclyAccessible = false on all instances | 5 |
| Auto Scaling | Scalable targets exist for cluster | 5 |
| Backtrack Enabled | BacktrackWindow > 0 | 5 |
Aurora PostgreSQL (11 dimensions, 55 points max):
Database-Level Score (8 dimensions, 50 points max):
Combined Maximum: 110 points (Aurora MySQL) or 105 points (Aurora PostgreSQL)
Grading Scale:
| Score Range | Grade | Interpretation |
|---|---|---|
| 90-100% | A | Excellent — minor optimizations only |
| 80-89% | B | Good — address non-critical gaps |
| 70-79% | C | Fair — multiple improvements needed |
| 60-69% | D | Poor — significant risk exposure |
| < 60% | F | Critical — immediate action required |
CATEGORY MAP:
├── 1. Server Information → Environment context (Query 1.1, 1.2)
├── 2. System Configuration → Parameter validation (Query 2.1, 2.2)
├── 3. Current Activity → Connection & thread analysis (Query 3.1-3.4)
├── 4. Replication Status → Lag & consistency (Query 4.1-4.2)
├── 5. Storage Capacity → Size, growth, fragmentation (Query 5.1-5.3)
├── 6. Performance Metrics → CPU, I/O, query stats (Query 6.1-6.4)
├── 7. Maintenance Health → Auto-increment, vacuum (Query 7.1)
├── 8. Optimization → Index usage, redundancy (Query 8.1-8.2)
└── 9. Summary & Score → Composite health score (Query 9.1)
When the rds-aidba MCP server is available, invoke queries using:
Tool: execute_health_query
Arguments:
engine: "mysql" # "mysql" or "postgresql"
category: "3" # Category number, 1 through 10
query_id: "3.1"
Query Routing by User Symptom:
| User Reports | Category | Queries to Run |
|---|---|---|
| "high CPU" | 6 (Performance) | 6.1, 6.2, 6.4 |
| "too many connections" | 3 (Activity) | 3.1, 3.2 |
| "slow queries" | 6 (Performance) | 6.1, 6.3 |
| "replication lag" | 4 (Replication) | 4.1, 4.2 |
| "storage full" | 5 (Storage) | 5.1, 5.2, 5.3 |
| "deadlocks" / "lock waits" | 3 (Activity) | 3.3, 3.4 |
| "full health check" | 9 (Summary) | 9.1 (then expand failing dimensions) |
| "index optimization" | 8 (Optimization) | 8.1, 8.2 |
| "auto-increment overflow" | 7 (Maintenance) | 7.1 |
If MCP is unavailable, fall back to:
See references/mysql-health-checks.md for all 23 MySQL queries and references/postgresql-health-checks.md for PostgreSQL queries.
CORRELATION RULES:
- High CPU + Slow Queries in logs → Identify top CPU-consuming queries
- Connection spike + "Too many connections" in error log → Connection exhaustion
- Replica Lag spike + Long transactions on writer → Writer blocking readers
- High IOPS + Large table scans → Missing indexes
- Storage growth + Fragmentation > 20% → OPTIMIZE TABLE needed
- MaximumUsedTransactionIDs > 1B (PG) → Wraparound risk
- Temp files detected (PG) + Low work_mem → Memory tuning needed
For each finding, generate recommendations in this priority order:
| Tool | Purpose | Command |
|---|---|---|
| Describe Cluster | Full cluster configuration | aws rds describe-db-clusters --db-cluster-identifier <id> |
| Describe Instance | Instance-level configuration | aws rds describe-db-instances --db-instance-identifier <id> |
| Check Versions | Version currency | aws rds describe-db-engine-versions --engine <engine> |
| Cluster Parameters | Parameter group settings | aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name <name> |
| Auto Scaling | Read replica scaling config | aws application-autoscaling describe-scalable-targets --service-namespace rds |
| Log Files | Available log file listing | aws rds describe-db-log-files --db-instance-identifier <id> |
Collect key metrics for health assessment (3h window, 300s period):
aws cloudwatch get-metric-data --metric-data-queries '[...]' --start-time <3h-ago> --end-time <now>
Metrics and Thresholds:
| Metric | 🟢 OK | 🟡 WARNING | 🔴 CRITICAL |
|---|---|---|---|
| CPUUtilization | < 70% | 70-90% | > 90% |
| DatabaseConnections | < 80% of max | 80-90% | > 90% |
| FreeableMemory | > 2 GB | 1-2 GB | < 1 GB |
| AuroraReplicaLag | < 100ms | 100-1000ms | > 1000ms |
| VolumeReadIOPs | Context-dependent | — | Sudden 3x+ spike |
| VolumeWriteIOPs | Context-dependent | — | Sudden 3x+ spike |
| MaximumUsedTransactionIDs | < 1 Billion | 1-1.5B | > 1.5B (PG only) |
Slow Query Log (Aurora MySQL):
Log group: /aws/rds/cluster/<cluster-id>/slowquery
Query: fields @timestamp, @message | filter @message like /Query_time/ | sort @timestamp desc | limit 50
Error Log (Aurora MySQL):
Log group: /aws/rds/cluster/<cluster-id>/error
Query: fields @timestamp, @message | filter @message like /ERROR|Warning|Note/ | stats count(*) by bin(1h)
PostgreSQL Log:
Log group: /aws/rds/cluster/<cluster-id>/postgresql
Query: fields @timestamp, @message | filter @message like /ERROR|FATAL|PANIC|duration/ | sort @timestamp desc | limit 50
## Health Check Report
**Engine:** <engine-type> | **Cluster:** <cluster-id> | **Version:** <version>
**Writer:** <writer-id> | **Readers:** <count> (<ids>)
**Assessment Date:** <timestamp>
### Overall Health Score: <score>/<max> (Grade: <letter>)
### Health Dimensions
| Dimension | Score | Status |
|-----------|-------|--------|
| <dimension> | <0 or 5> | 🟢/🔴 |
### Critical Issues
❌ <Dimension>: <Issue> — <Impact> — <Remediation>
### Performance Metrics (Last 3 Hours)
| Metric | Min | Max | Average | Latest |
|--------|-----|-----|---------|--------|
### Recommendations (Priority Order)
1. 🔴 [CRITICAL] <action> — <expected outcome>
2. 🟡 [WARNING] <action> — <expected outcome>
3. 🟢 [INFO] <action> — <expected outcome>
| Pattern | Meaning | Severity | Action |
|---|---|---|---|
Too many connections | Connection limit reached | 🔴 CRITICAL | Implement RDS Proxy, increase max_connections |
Aborted connection | Client disconnected unexpectedly | 🟡 WARNING | Check application connection handling |
Deadlock found | Transaction deadlock detected | 🟡 WARNING | Review transaction ordering, add indexes |
InnoDB: page_cleaner | Buffer pool pressure | 🟡 WARNING | Scale up instance class |
Lock wait timeout exceeded | Lock contention | 🔴 CRITICAL | Identify blocking transaction |
| Pattern | Likely Cause | Fix |
|---|---|---|
| High Query_time + High Rows_examined | Missing index | Add composite index on WHERE/JOIN columns |
| High Query_time + Low Rows_examined | Lock waiting | Resolve lock contention |
| Many queries with same DIGEST | Hot path query | Optimize or cache result |
| Temp table on disk | TEXT/BLOB or large GROUP BY | Restructure query, increase tmp_table_size |
| Aspect | Aurora MySQL | RDS MySQL |
|---|---|---|
| Storage | Shared distributed volume (auto-scales to 128 TiB) | EBS-backed (manual provisioned IOPS) |
| Replication | Redo log-based (< 20ms typical) | Binlog-based (seconds to minutes) |
| Failover | 30 seconds typical | 1-2 minutes |
| Buffer Pool | Auto-warmed after restart | Cold start after restart |
| Backtrack | Supported (rewind without restore) | Not available |
| Read Replicas | Up to 15, same storage volume | Up to 5, async binlog |
| Monitoring | mysql.ro_replica_status available | SHOW REPLICA STATUS only |
User Query: "My Aurora MySQL cluster has high CPU usage."
aws cloudwatch get-metric-dataUser Query: "Perform a full health check on my Aurora MySQL cluster."
User Query: "Getting 'Too many connections' errors."
DatabaseConnections metricUser Query: "My Aurora read replica has high lag."
AuroraReplicaLag metricUser Query: "Check for transaction ID wraparound risk."
MaximumUsedTransactionIDs metric