• notfromhere
    link
    fedilink
    arrow-up
    3
    arrow-down
    3
    ·
    1 year ago

    What is a SQL trigger and why is it taking down servers? Do you know how to fix it?

    • RoundSparrow
      link
      fedilink
      arrow-up
      2
      arrow-down
      1
      ·
      1 year ago

      I think 0.18.3 fixed some of it, but there are likely some more performance issues related to PostgreSQL lurking in Lemmy.

      A TRIGGER in SQL is a logic that executes based on other activity.

      Lemmy uses them so that when you create a new comment or post, it executes code to insert tracking record for votes and comments on a post. One of the things Lemmy does is called site_aggregates, and there was a bug where it was updating the counts for 1500 servers instead of just the one server. That got fixed in 0.18.3

      Deleting accounts in lemmy was causes crashes. I’m not sure if that has been entirely resolved. These things are all kind of hidden in the background of the code, so a lot of developers overlooked that there were problems in them.

      • notfromhere
        link
        fedilink
        arrow-up
        2
        arrow-down
        1
        ·
        1 year ago

        Isn’t there a logstream they could tap into to have a separate async tally going on instead of doing it synchronously? Probably a lot of things could be delegated to an async job performed when server load allows?

        • RoundSparrow
          link
          fedilink
          arrow-up
          5
          arrow-down
          2
          ·
          edit-2
          1 year ago

          I’ve had to really adjust my thinking with this project. They want to do things a very particular way and it goes back 4 years, and a lot of the mistakes are just now getting noticed/attention. For example, comments were not deleting on all the servers, I was testing that after comparing server copies of the same communities and found they were not the same. It just didn’t seem to have a lot of people spot-checking it for mistakes. I am learn to just “go with the flow” and face that it’s more like how musicians would approach design and running a project. Media-focused systems can be that way.

          • notfromhere
            link
            fedilink
            arrow-up
            8
            ·
            1 year ago

            Going with the flow is both ways. It’s a community project so everyone’s opinion matters, especially those who contribute PRs.