Details here: https://github.com/LemmyNet/lemmy/issues/3165

This will VASTLY decrease the server load of I/O for PostgreSQL, as this mistaken code is doing writes of ~1700 rows (each known Lemmy instance in the database) on every single comment & post creation. This creates record-locking issues given it is writes, which are harsh on the system. Once this is fixed, some site operators will be able to downgrade their hardware! ;)

  • ShadowA
    link
    fedilink
    arrow-up
    31
    arrow-down
    1
    ·
    edit-2
    1 year ago

    It’s not on every comment, it’s mostly triggered on deletions and edits. The problem is actually infintesimally worse and 1700 rows are updated if you delete 3 comments. If you delete more it’s exponential and just straight up fails and locks your database.

    I’ll probably put a patch in there later tonight and then see about a PR unless someone else does.

    • RoundSparrow@lemmy.mlOPM
      link
      fedilink
      arrow-up
      28
      ·
      edit-2
      1 year ago

      It’s not on every comment,

      My testing with latest code is that it is indeed on every single comment INSERT, every new comment. I have the ability to view my live data while using Lemmy: https://lemmyadmin.bulletintree.com/query/raw_site_aggregates?output=table

      Every one of the 1486 rows on that table gets +1 on comment when I post a new comment on my instance.

      it’s mostly triggered on deletions and edits

      That is not correct. Edits do not change the count of comments column on site_aggregates - because the number isn’t changing. Deletes (of a comment or post) in Lemmy are also not SQL DELETE statements, they are just a delete data column in the table. That DELETE PostgreSQL trigger only gets run when a end-user cancels their Lemmy account in their profile.

      • ShadowA
        link
        fedilink
        arrow-up
        11
        ·
        1 year ago

        Ah Gotcha. That’s true, but the cascading issue that causes thousands of inserts happens on a delete.

        That table update you’re looking at is blazing fast due to the relatively low number of sites, until you run it thousands of times on a delete.