this post was submitted on 19 Jun 2023
17 points (100.0% liked)

Lemmy Server Performance

6 readers
1 users here now

Lemmy Server Performance

lemmy_server uses the Diesel ORM that automatically generates SQL statements. There are serious performance problems in June and July 2023 preventing Lemmy from scaling. Topics include caching, PostgreSQL extensions for troubleshooting, Client/Server Code/SQL Data/server operator apps/sever operator API (performance and storage monitoring), etc.

founded 1 year ago
MODERATORS
RoundSparrow@lemmy.ml
RoundSparrow@bulletintree.com
17
Lemmy server admins, please enable pg_stat_statements on your PostgreSQL server so the Lemmy community can better identify the performance problems on the more active sites (lemmy.ml)
submitted 1 year ago* (last edited 1 year ago) by RoundSparrow@lemmy.ml to c/lemmyperformance@lemmy.ml
11 comments fedilink hide all child comments
 

I have been working with pg_stat_statements extension to PG and it give us a way to see the actual SQL statements being executed by lemmy_server and the number of times they are being called.

This has less overhead than cranking up logging and several cloud computing services enable it by default (example) - so I don't believe it will have a significant slow down of the server.

A DATABASE RESTART WILL BE REQUIRED

It does require that PostgreSQL be restarted. Which can take 10 or 15 seconds, typically.

Debian / Ubuntu install steps

https://pganalyze.com/docs/install/self_managed/02_enable_pg_stat_statements_deb

Following the conventions of "Lemmy from Scratch" server install commands:

sudo -iu postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"

Followed by a restart of the PostgreSQL service.

you are viewing a single comment's thread
view the rest of the comments
[–] RoundSparrow@lemmy.ml 2 points 1 year ago* (last edited 1 year ago)

Update July 24, 2023

Some major stored procedure SQL problems were overlooked until 2 days ago. I submitted revised statements to fix a massive write operation on every single post and comment creation on a local site. The site_aggregates table... every row modified instead of a single row. https://github.com/LemmyNet/lemmy/pull/3704

I was curious why pg_stat_statements didn't draw more attention to the INSERT statements hitting so many rows, and I found out that by default it does not take into account stored procedure execution! https://stackoverflow.com/questions/56741860/pg-stat-activity-how-to-see-current-activity-inside-a-running-stored-procedure

pg_stat_statements.track = all

Now I'm seeing far more activity than I have been looking at for the past 2 months... the stored procedure statements are showing up!

Install steps: https://gist.github.com/rcanepa/535163dc249539912c25