this post was submitted on 24 Jun 2024
33 points (97.1% liked)

Lemmy Support

4651 readers
4 users here now

Support / questions about Lemmy.

Matrix Space: #lemmy-space

founded 5 years ago
MODERATORS
dessalines@lemmy.ml
33
[SOLVED] Postgres using a lot of CPU since update (retrolemmy.com)
submitted 4 months ago* (last edited 4 months ago) by DosDude@retrolemmy.com to c/lemmy_support@lemmy.ml
24 comments fedilink hide all child comments
 

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.

you are viewing a single comment's thread
view the rest of the comments
[–] DosDude@retrolemmy.com 3 points 4 months ago (2 children)

I have. It's running postgres v16.3

[–] graycube@lemmy.world 8 points 4 months ago (1 children)

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.

[–] DosDude@retrolemmy.com 5 points 4 months ago* (last edited 4 months ago)

Thanks. I ran it. Hopefully it'll make a difference.

Edit: It looks like this did the trick. I'll keep monitoring to see if it sticks. Thanks again!

[–] graycube@lemmy.world 2 points 4 months ago

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.