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 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”

Is there anyone out there with database expertise willing to help?

  • pitninjaEnglish
    4·
    1 year ago

    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).

    • ericjmorey@beehaw.orgOPEnglish
      5·
      1 year ago

      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.

      • pitninjaEnglish
        6·
        1 year ago

        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.