32

I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?

  • 2
    "RHCE 6.2"? Do you mean "RHEL 6.2" ? I assume postgress is postgres and you've just copied it by hand. – Craig Ringer Jun 8 '13 at 5:34
32

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/EXPLAIN ANALYZE; check locks, etc.)

| improve this answer | |
  • is this the exact query, I'm not much familiar with db as im the sec guy working on siem, your select statement, do i have to feed it pid from top command ? – asadz Jun 7 '13 at 21:02
  • 1
    @asadz no, it was truncated (fixed now) -- If you have specific PIDs and want to see what they're running you can isolate them with a WHERE clause, but if you don't have a huge number of PIDs it's just as easy to search through the full output. The Postgres manual has additional detail on what you can get out of pg_stat_activity, as well as the other statistics-collector tables (which can help you if your problem isn't a user query). – voretaq7 Jun 7 '13 at 21:11
  • when I execute this query, there is no PID that I have to suspect – Fendi Tri Cahyono Mar 14 '19 at 23:28
  • Thanks for the clue, recently I met similar issue and figured out the reason by using SELECT * FROM pg_stat_activity; – Yao Aug 26 '19 at 9:51
15

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.

| improve this answer | |
8

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.

| improve this answer | |
  • this make sense ; since the siem is used by analyst to query lot of data back and forth; is there a way i can check on the lock status; or conditions attributed to it; ? – asadz Jun 9 '13 at 7:01
1

You can use pg_stat_statements,
     https://www.postgresql.org/docs/current/pgstatstatements.html.

First, pg_stat_activity (from the other answers) is good advice — and I think sometimes it's not enough? It shows the current activity only, but what if there are many many quick queries, which don't last long enough for them to look interesting in pg_stat_activity? However when there're many many of them, they might still cause high CPU usage?

Then you can use pg_stat_statements:

pg_stat_statements records queries that are run against your database, strips out a number of variables from them, and then saves data about the query, such as how long it took, as well as what happened to underlying reads/writes.

(From this blog post: The most useful Postgres extension: pg_stat_statements )

Here I use it to find out why my own CPU usage is unexpectedly high:

\x

select 
  (total_time / 1000 / 3600) as total_hours,
  (total_time / 1000) as total_seconds,
  (total_time / calls) as avg_millis, 
  calls num_calls,
  query 
from pg_stat_statements 
order by 1 desc limit 10;


-[ RECORD 1 ]-+-------------------------
total_hours   | 0.128210291016666
total_seconds | 461.557047659998
avg_millis    | 9.06506889111474
num_calls     | 50916
query         | select p.site_id, p.page_id, p.version current_version, h.page_version cached_version from  ...
-[ RECORD 2 ]-+------------------------
total_hours   | 0.0586912504452778
total_seconds | 211.288501603
avg_millis    | 4.14966517279101
num_calls     | 50917
query         | select p.site_id, p.page_id ... from ...
:
:

Did you notice something, above? — There top queries are quick: 0.009 and 0.004 seconds. But they get called many many times.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.