I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the
top command, I see
postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?
You can match a specific Postgres backend ID to a system process ID using the
pg_stat_activity system table.
SELECT pid, datname, usename, query FROM pg_stat_activity; can be a good starting point.
Once you know what queries are running you can investigate further (
EXPLAIN ANALYZE; check locks, etc.)
I was having the same issue. The postgresql is setup on AWS RDS and it was having 100% cpu utilisation even after increasing the instance. I debugged with the method shown here and one of the method worked for me.
I checked for the query running for the longest time and came to know that certain queries was stuck and was running since more than 3-4 hours. To check since how much time the query is running, run the following command:
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');
If this is more than an hour, than this is the issue. Kill the long running connection and limit the max age of the connection from application side.
If this is really the postmaster using all that CPU, then you likely have lock contention issues, probably due to very high
max_connections. Consider lowering
max_connections and using a connection pooler if this is the case.
Otherwise: Details, please. Full output of
top -b -n 1 for a start.