RDS Performance Insights for MCP Servers — DB Load, Wait Events, Top SQL Analysis

Enabling Performance Insights

# Enable Performance Insights on an Aurora cluster (applied to each instance)
aws rds modify-db-instance \
  --db-instance-identifier mcp-probe-db-writer \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \   # 7 days = free; 93/185/372 days = paid
  --apply-immediately

# For new clusters via CDK:
const writer = rds.ClusterInstance.serverlessV2("writer", {
  performanceInsightRetention: rds.PerformanceInsightRetention.DEFAULT,  // 7 days, free
  // or: rds.PerformanceInsightRetention.MONTHS_1  (31 days, $0.02/vCPU-hr)
  enablePerformanceInsights: true,
  // Optional: encrypt PI data with a customer-managed KMS key
  // performanceInsightEncryptionKey: myKey,
});

Supported instance classes: Performance Insights requires db.t3.medium or larger for RDS, and any Serverless v2 instance for Aurora. It is not available on db.t2 instances or Aurora Serverless v1. If your instance class is incompatible, Performance Insights is silently disabled with no error message — verify in the console that the PI tab appears.

Understanding DB Load and AAS

Average Active Sessions (AAS) counts the number of database sessions that are in one of two states: executing (actively using CPU) or waiting (blocked on I/O, a lock, or a network roundtrip). A session that is idle (connected but not executing a query) does not contribute to AAS.

# Query Performance Insights API for recent DBLoad
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-EXAMPLE123EXAMPLEABCDEFG \
  --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.wait_event","Dimensions":["db.wait_event.name","db.wait_event.type"],"Limit":5}}]' \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 60

Wait event taxonomy for Aurora PostgreSQL

Performance Insights groups wait events by type. Each type points to a different root cause:

Top SQL analysis

The Top SQL tab in Performance Insights ranks queries by their contribution to DBLoad (AAS). Sort by "DB load per call" to find the slowest individual queries, or by "Total load" to find the queries contributing most to overall saturation:

# Via PI API: get top 10 SQL statements by DB load over the past hour
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-EXAMPLE123EXAMPLEABCDEFG \
  --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.sql","Dimensions":["db.sql.id","db.sql.db_id","db.sql.statement"],"Limit":10}}]' \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 300

Performance Insights captures query text automatically for Aurora PostgreSQL (via pg_stat_activity) and Aurora MySQL (via performance_schema). For RDS MySQL, you must enable performance_schema = 1 in the parameter group — if it is disabled, PI captures query digest hashes but not the full query text, making Top SQL less useful.

-- In Aurora PostgreSQL, cross-reference PI findings with pg_stat_statements
-- (extension must be enabled):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries by total execution time
SELECT
  substring(query, 1, 100) AS query_snippet,
  calls,
  total_exec_time / 1000 AS total_sec,
  mean_exec_time AS avg_ms,
  stddev_exec_time AS stddev_ms,
  rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Enhanced Monitoring vs Performance Insights

These two observability features are complementary and capture different layers:

# Enable Enhanced Monitoring alongside Performance Insights
aws rds modify-db-instance \
  --db-instance-identifier mcp-probe-db-writer \
  --monitoring-interval 15 \                 # seconds between OS metric samples
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role \
  --apply-immediately

# The IAM role must have AmazonRDSEnhancedMonitoringRole policy attached

When diagnosing a DBLoad spike: use PI to confirm which queries and wait events are contributing; use Enhanced Monitoring to confirm whether the OS CPU is also spiked (CPU wait event + OS CPU spike = compute bottleneck) or whether the OS shows high disk I/O while DB shows IO:DataFileRead (I/O bottleneck, not compute).

Counter metrics for I/O analysis

Performance Insights exposes engine-specific counter metrics alongside AAS. For Aurora PostgreSQL these include:

# Useful PI counter metrics for probe database analysis
# db.SQL.tup_fetched.avg  — rows fetched per second (high = large result sets or full scans)
# db.SQL.tup_inserted.avg — rows inserted per second (probe write throughput)
# db.Checkpoint.buffers_checkpoint.avg — buffers written at checkpoint (high = dirty buffer pressure)
# db.Cache.blks_hit.avg — buffer cache hits (high = good; declining = working set exceeding buffer pool)
# db.Cache.blks_read.avg — disk reads per second (high relative to blks_hit = cache miss rate problem)

aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-EXAMPLE123EXAMPLEABCDEFG \
  --metric-queries \
    '[{"Metric":"db.SQL.tup_inserted.avg"},{"Metric":"db.Cache.blks_hit.avg"},{"Metric":"db.Cache.blks_read.avg"}]' \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 60

AliveMCP probe-database observability pattern

AliveMCP enables Performance Insights with DEFAULT retention (7 days) on the Aurora writer instance. During a registry re-scan event (once every 6 hours, bulk-inserts ~12,000 status rows), DBLoad spikes from a baseline of 0.8 to approximately 3.5 on a db.r7g.large (2 vCPUs). The dominant wait event during this spike is Lock:tuple — the bulk insert contends for row locks with the real-time probe worker that is concurrently updating the same server_slug rows. The fix applied: partition probe workers by server_slug hash prefix (0–7 and 8–f), so the bulk re-scan and the real-time probe worker never compete for the same row range. Post-fix, DBLoad during re-scans stays below 2 (within vCPU capacity) and Lock:tuple disappears from the Top Wait Events chart. The PI data was exported via the get-resource-metrics API into a CloudWatch custom dashboard alongside the ServerlessDatabaseCapacity ACU metric for a single-pane-of-glass database health view.

Common failure modes