Recently I have noticed some of the queries are repeatedly being executed with new pids, this results in 100% CPU utilization.

So I queried my postgres to see what's going on, with:

SELECT pid, xact_start, datname, usename, query FROM pg_stat_activity ORDER BY xact_start ASC;

pid   |        xact_start       | datname    | usename     | query

4095  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4096  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4098  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4099  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4100  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4120  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....
4125  |  2017-06-27 with time.. | db_name    | role_name   | SELECT ....

Above queries are identical that are being executed at different time. there is a huge list.

Also checked what time it took for execution:

SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');

Which recorded more than 20 seconds.

 (1 row)


What I have done to resolve this is make a db dump and re-import it to a new db. This should have cleared some thing from the database, which I'm trying to get an understanding of.

Possibilities but unknown:

  1. pg_dump should have cleared some thing from database i.e; cached tables or cleared locked queries.
  2. While restored it should have cleared something.

But What Happened? can someone help me understand what changed when I created a new db and imported the old db into it? because now my CPU load is at 10% from 100%.

  • How many client connections did you have? – Laurenz Albe Jun 27 '19 at 22:50
  • A "new pid" means a new connection from your application. If you see a lot of them, apparently your application decided to open more connections, probably to deal with increased traffic. When you restored the dump, you probably had to shut down your application which in turn closed all those connections. And now you don't have that traffic any more so your application doesn't open that many connections. – a_horse_with_no_name Jun 28 '19 at 5:05
  • @LaurenzAlbe I saw like more than 700 client connections at a time. – Arun Jun 28 '19 at 13:14
  • @a_horse_with_no_name I did not shutdown my application, I made a dump from active application. – Arun Jun 28 '19 at 13:15
  • 1
    Ask the application, not the database. – Laurenz Albe Jun 28 '19 at 19:27

A dump and reload gets you new tables with no usunsed space, new incides, and no active queries, thise are all things that can be repaired on an existing database.

It could be slack space in tables (you can clean that up with VACUUM FULL ...) or bad indices (can be repaired by REINDEX ...) or bad statistics (can ve fixed with ANALYZE ...)

Or possibly could be some slow query did a table scan which caused the fast query to coat-tail on it with an overlapping table scan and the rest just followed (you can fix that by restarting the database, or just killing all the active sessions SELECT pg_terminate_backend(pid) FROM pg_stat_activity;)

Unless you have tables that are shrinking vacuum full should not be needed. usually autovacuum will keep the slack space under control. If you need to do a periodic vacuum full you will have to do it manually, or add a crontab (possibly under user postgres) to perform that task.

  • I saw a lot of pids running the same query. So, if I have to do a pg_terminate_backend it will a whole bunch of pids I'm talking about. I have also seen VACUUM is not a good option. Thanks for your answer on explaining what reload does. But, it would be helpful with any documents. Thanks again. – Arun Jun 28 '19 at 13:12
  • Vacuum full done on the whole database is about as expensive as a dump and reload, but you can target the sparse tables selectively. – Jasen Jun 30 '19 at 8:43
  • can we automate the vacuum on databases or is it a manual process? For instance if I have 10 dbs on one instance what should be the approach? – Arun Jul 1 '19 at 13:47
  • usually not needed so manual, you could use cron. – Jasen Jul 4 '19 at 10:22

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.