Lemmy Support

4650 readers

1 users here now

Support / questions about Lemmy.

Matrix Space: #lemmy-space

founded 5 years ago

MODERATORS

[email protected]

[SOLVED] Postgres using a lot of CPU since update (retrolemmy.com)

submitted 4 months ago* (last edited 4 months ago) by [email protected] to c/[email protected]

20 comments fedilink hide all child comments

EDIT: It seems to have been fixed thanks to @[email protected]. 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

[–] [email protected] 4 points 4 months ago (1 children)

You can use pg_stat_statements to find slow queries. Try sorting by top total_exec_time.

[–] [email protected] 2 points 4 months ago (1 children)

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'

[–] [email protected] 4 points 4 months ago (1 children)

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.

[–] [email protected] 2 points 4 months ago

Well that would do it. Thanks for pointing out!