Details here: https://github.com/LemmyNet/lemmy/issues/3165
This will VASTLY decrease the server load of I/O for PostgreSQL, as this mistaken code is doing writes of ~1700 rows (each known Lemmy instance in the database) on every single comment & post creation. This creates record-locking issues given it is writes, which are harsh on the system. Once this is fixed, some site operators will be able to downgrade their hardware! ;)
Holy hell. Post this to one of the programming-related communities. That is interesting.
Not that interesting when it turns out it was an ORM artifact.
True. I have experienced quite a few of those… But, still interesting nonetheless.
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.
It’s not on every comment,
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.
it’s mostly triggered on deletions and edits
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.
Holy shit
Indeed
Man that is some bug, no wonder lemmy had such a rough start performance wise during the reddit migration!
Get some DBA’s on the job and Lemmy will be blazing fast.
We have had DBA’s, the problem is the Rust code uses ORM and an auto JSON framework that makes tracing the code time-consuming to learn.
- AnarchoYeasty ( @AnarchoYeasty@beehaw.org ) 3•1 year ago
Honestly, ORMs are a waste of time. Why not use sqlx and just hand write the SQL to avoid issues like this.
In this one case, it was hand-written SQL inside a PostgreSQL FUNCTION that the ORM knows nothing about. But there is a approach in the entire application to have live-data from PostgreSQL for every little thing.
Okay so you may need to refactor here and there to get more performance.
Wonderful. Time to start running my own instance.