I manage an application that has what I would call a medium sized workload (20,000-30,000 records inserted per day with around 2GB of data total). We're on RDS Postgres 9.6 with a db.m4.xlarge (4 vCPU and 16 GB of RAM) instance (I'm way over provisioned, I know). Everything has been running smoothly for months with the average CPU utilization under 10%.
Yesterday I got an escalation from the application support that our CPU has spiked above 80% and since then I haven't gotten it to go back down under 50%.
This is the graph of the last 4 days of CPU Utilization
There's been no spike in usage over this same time.
I've looked into long running queries through out the day using
SELECT max(now() - xact_start) FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
The largest amount I've seen is 1.5 seconds
We have about 40 connections but most are inactive.
This same thing happened a few months ago and we moved to our now way overprovisioned server to hopefully not have it happen again. So now I'm trying to figure out the root cause.
Any help would be appreciated.
idle in transaction
is not doing anything. Those rows don't qualify as "long running queries"