Slow SQL queries · Issue #2877 · LemmyNet/lemmy
github.com
There are some problems with database lockups which seem to be caused by slow queries. I set log_min_duration_statement=3000 and collecting any slow queries in this issue. These should be optimized...

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”

  • RuudMAEnglish
    6•1 year ago

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

  • pitninjaEnglish
    5•1 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.

    • @ericjmoreyOPEnglish
      2•1 year ago

      I do believe you are correct.