Lemmy Server Performance

420 readers

1 users here now

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

[email protected]

PostgreSQL for Lemmy instance installers/operators/upgraders (lemmy.ml)

submitted 1 year ago by [email protected] to c/[email protected]

7 comments fedilink hide all child comments

Diving in, I haven't worked with PostgreSQL for 15 years, but sharing random notes and obsrervations

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 0 points 1 year ago* (last edited 1 year ago) (1 children)

why would community joins be pending?

edit: Ah I see. A better query might be:

SELECT p.name, c.name, i.domain, p.local, * FROM community_follower
         inner join person p on p.id = community_follower.person_id
         inner join community c on c.id = community_follower.community_id
         inner join instance i on c.instance_id = i.id
         WHERE pending='t';

which will show you the user, the community they're trying to join, and the instance they're trying to join on. Example:

(redacted my users in case they don't want it known what they're trying to join.

[–] [email protected] 0 points 1 year ago* (last edited 1 year ago) (1 children)

Thank you for sharing the query.

why would community joins be pending?

I believe it is a glaring symptom of federation replication failure. Data isn't making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517

You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)

What software are you using to view the queries?

[–] [email protected] 1 points 1 year ago

What software are you using to view the queries?

I use DataGrip. It's fantastic.

I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517

You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)

Yeah sorry, I didn't realize you meant remote community joins. I have had that problem in fact I still can't join several communities due to that issue.