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”

  • RuudMA
    link
    English
    61 year ago

    I can have a look, although I’m not really a database developer.

  • pitninja
    link
    fedilink
    English
    51 year ago

    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.

    • @ericjmoreyOP
      link
      English
      21 year ago

      I do believe you are correct.