The Lemmy dev team could use some help with their database issues - Lemmy.world
The Lemmy dev team could use some help with their database issues - Lemmy.world
The “Hot” score is causing database issues for Lemmy instances. - Every time a page of lemmy instance is viewed, the “Hot” score is recalculated. - Every hour, the entire database is reindexed. This means that just visitors to a lemmy instance can create problems. I’m sure this isn’t the only aspect...
Is there anyone out there with database expertise willing to help?
Question really is why is it developed that way? Why does it need to do a reindex every hour? I have not looked at Lemmy source code, but wondering if a trigger function could do the same thing.
I'm going to guess inexperience and a focus on developing other parts of Lemmy.
(Apparently the UI allows you to edit your own deleted comment, but saving it doesn't un-delete it, you just lose what you wrote... Bummer!)
In the conversation on Github, it looks like they considered a trigger and some other options based on DB query analyses, and arrived at a solution. It requires code refactoring, though, so I wouldn't expect it to be out in the wild right away.
There's a branch that likely fixes the issue with a pull request to main, but it failed some automated testing and needs to be tested in main before a new release. The timing of this is unfortunate. I suspect Monday and Tuesday to have many 50x codes on any instances that get attention.
In the conversation on Github it looks like they considered a trigger and looked at a ton of database query analyses and found a promising direction to go in.
The change requires code refactoring, though, so I wouldn't expect it to land in a stable version of Lemmy for a little bit.
I'm also curious why every page view (to be clear, do you mean every time a post is viewed?) triggers a recalc on the Hot score. I would think you could recalc that like every 5 minutes and be just fine. (If people are refreshing a feed more frequently than every 5 minutes, they should be expecting to see the same thing most of the time).
I’m also curious why every page view (to be clear, do you mean every time a post is viewed?) triggers a recalc on the Hot score.
This is the current design, yes. I believe the devs have already started a related issue to change this. I'm assuming the next version of Lemmy will have some major performance improvements.
Yeah, just thinking about things logically here, the more they can automate expensive queries on the back end on a more periodic basis and then cache the results regardless of what's happening with page views on the front end, the better things should be. It's a lot cheaper for a database to serve cached results in lookups. I'm not a database guy and not really qualified to help fix any of these issues, but I'm just drawing from what I remember from my databases course in college.