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 database problems.
I’m sure this isn’t the only aspect of the database that could use some optimization. Any one with database expertise out there want to help?
I’ve linked to an illuminating comment on a lemmy’s github: “Slow SQL queries · Issue #2877 · LemmyNet/lemmy”
I can have a look, although I’m not really a database developer.
I’m by no means a database expert, but based off what you’re describing for point #1, it sounds like Lemmy could really use more caching of query results, especially for expensive queries (like, presumably, calculating the Hot score). I feel like this should be done on a periodic basis, if possible, rather than with every page view. I’m also not sure the entire database re-indexing every hour makes sense, but again, I’m by no means an expert.
I do believe you are correct.
@[email protected], since you’re a database admin, maybe you can offer the Lemmy dev team some assistance.
