8mo ago

Postgres using a lot of CPU since update

EDIT: It seems to have been fixed thanks to @graycube@lemmy.world. Running analyze verbose; in postgres.

After updating to 0.19.5 from 0.19.3 my postgres is often using up 500%+ of CPU according to docker stats and often going to 100% CPU on most cores according to htop. Also noticed in the uptime monitor:

htop shows one of the 5 postgres processes constantly on UPDATE. I think this might be part of the problem.

I'm not comfortable with postgres and am honestly completely in the dark how or where to mitigate or even pinpoint this issue.

Any help would be appreciated.

20 comments

Did you update postgres as well? 0.19.4 needs a newer version.
- I have. It's running postgres v16.3
  
  Sometimes after upgrades, even minor ones, I find it useful to run analyze on all of the tables. I usually do analyze verbose; so I can see which tables are getting analyzed. This will assess every table so the query planner can make better decisions about how to resolve queries. If the query planner is making bad decisions I/O and CPU will be high and query performance will be poor.

Oh man, i just remembered cve-2024-3094 lol
- commit "fixed stuff"
  2.8k blob of crypto mining code
  Would be hilarious

You can use pg_stat_statements to find slow queries. Try sorting by top total_exec_time.
- undefined
  
  ERROR: extension "pg_stats_statements" is not available
  Even though it's added in the customPostgresql.conf shared_preload_libraries = 'auto_explain,pg_stat_statements'
  
  I know you already found a solution, but fwiw, it seems you have a typo in calling the extension. You have "stats" plural instead of "stat" singular.

Do you have any tweaks of pg settings? Eg. shm mem, shared_buffers etc. ?
If not you migh want to: https://pgtune.leopard.in.ua/
- I have. And I tried to tweak it with no avail. But it was working within acceptable levels before the update.
  
  hmm, how many connections are used SELECT count(*) FROM pg_stat_activity; ?
  I am not a master postgres admin but my intuition has been that the amount of connections is a big factor in how pg behaves with cpu and mem.

20 comments