Home / AWS

AWSTroubleshootingIntermediate

RDS database at high CPU: finding the culprit with Performance Insights

Use database load, wait events and top SQL in Performance Insights (or Database Insights) to find what's driving CPU, then fix it or scale.

Deepak Bhardwaj2 min read

Applies toAmazon RDS and Aurora (MySQL, PostgreSQL), Performance Insights / CloudWatch Database Insights

Read the load chart correctly

DB load (average active sessions, AAS) is the main signal.

  • AAS below the vCPU line: the database is coping. Look at the app or network.
  • AAS above the vCPU line, mostly CPU: queries are CPU-bound, so find the top SQL.
  • AAS above the line, mostly waits (IO, locks): CPU isn't the real problem.

Top SQL

In Performance Insights, slice the load by SQL. Typically one to three statements account for most of it. For each one, note:

  • Calls per second, and the average latency.
  • Rows examined vs rows returned (MySQL), or shared blocks read (PostgreSQL). A large ratio means a scan.

You can get the same data from the engine:

-- PostgreSQL (pg_stat_statements is available on RDS)
SELECT queryid, calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, rows, left(query, 100)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- MySQL
SELECT digest_text, count_star, ROUND(sum_timer_wait/1e12, 1) AS total_s, sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Common causes and fixes

Finding Fix
A query scanning a large table Add the right index. Check with EXPLAIN (ANALYZE, BUFFERS) or EXPLAIN ANALYZE.
A query that became slow suddenly Plan change after a stats update or data growth. ANALYZE the tables and check the plan.
Very high calls/sec of a cheap query N+1 pattern in the app. Cache it or batch it.
Many connections churning Use RDS Proxy or app-side pooling
CPU spikes on a schedule A batch or report job. Move it to a read replica, or reschedule it.

Wait events that look like CPU problems

  • PostgreSQL LWLock:*, Lock:transactionid → contention, not compute.
  • MySQL wait/io/table/sql/handler → table IO; synch/* → mutex contention.

Scale as a stopgap

aws rds modify-db-instance --db-instance-identifier prod-db --db-instance-class db.r7g.2xlarge --apply-immediately

With Multi-AZ, the change is applied to the standby first and then fails over, so the outage is short. Without Multi-AZ, expect several minutes of downtime.

Note

On burstable (t-class) instances, check CPUCreditBalance. When credits run out, CPU is throttled to the baseline and everything slows down. Production databases shouldn't run on burstable classes.

Prevent it next time

  • Keep Performance Insights / Database Insights on for production, with at least 7 days of retention.
  • Alarm on DBLoad relative to the vCPU count, not just CPUUtilization.
  • Review the top SQL after every release.

Related fixes