239
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 23 Jul 2023
239 points (99.2% liked)
Lemmy Server Performance
413 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
It's not on every comment, it's mostly triggered on deletions and edits. The problem is actually infintesimally worse and 1700 rows are updated if you delete 3 comments. If you delete more it's exponential and just straight up fails and locks your database.
I'll probably put a patch in there later tonight and then see about a PR unless someone else does.
My testing with latest code is that it is indeed on every single comment INSERT, every new comment. I have the ability to view my live data while using Lemmy: https://lemmyadmin.bulletintree.com/query/raw_site_aggregates?output=table
Every one of the 1486 rows on that table gets +1 on comment when I post a new comment on my instance.
That is not correct. Edits do not change the count of comments column on site_aggregates - because the number isn't changing. Deletes (of a comment or post) in Lemmy are also not SQL DELETE statements, they are just a delete data column in the table. That DELETE PostgreSQL trigger only gets run when a end-user cancels their Lemmy account in their profile.
Ah Gotcha. That's true, but the cascading issue that causes thousands of inserts happens on a delete.
That table update you're looking at is blazing fast due to the relatively low number of sites, until you run it thousands of times on a delete.