EDIT: It seems to have been fixed thanks to @graycube@lemmy.world. Running analyze verbose; in postgres.

After updating to 0.19.5 from 0.19.3 my postgres is often using up 500%+ of CPU according to docker stats and often going to 100% CPU on most cores according to htop. Also noticed in the uptime monitor:

htop shows one of the 5 postgres processes constantly on UPDATE. I think this might be part of the problem.

I’m not comfortable with postgres and am honestly completely in the dark how or where to mitigate or even pinpoint this issue.

Any help would be appreciated.

    • taaz@biglemmowski.win
      link
      fedilink
      English
      arrow-up
      2
      ·
      edit-2
      4 months ago

      hmm, how many connections are used SELECT count(*) FROM pg_stat_activity; ?
      I am not a master postgres admin but my intuition has been that the amount of connections is a big factor in how pg behaves with cpu and mem.

      • DosDude👾@retrolemmy.comOP
        link
        fedilink
        arrow-up
        1
        ·
        4 months ago
        ERROR:  extension "pg_stats_statements" is not available
        

        Even though it’s added in the customPostgresql.conf shared_preload_libraries = 'auto_explain,pg_stat_statements'

          • DosDude👾@retrolemmy.comOP
            link
            fedilink
            arrow-up
            2
            ·
            4 months ago

            I added pg_stat_statements, and ran it. This was the result:

            # SELECT count(*) FROM pg_stat_activity;
             count
            -------
                11
            (1 row)
            
            
            • taaz@biglemmowski.win
              link
              fedilink
              English
              arrow-up
              3
              ·
              4 months ago

              do you also have pict-rs connected to this postgres instance? that is surprisingly low number to me, I would have expected anywhere between 20-50 active connections (I use 50 for lemmy and 20 for pict-rs, configured in their respective conf files)

                • taaz@biglemmowski.win
                  link
                  fedilink
                  English
                  arrow-up
                  1
                  ·
                  edit-2
                  4 months ago

                  Ah, you are using pretty different deployment then, even the used postgres image is different then the usual deployment ( pgautoupgrade/pgautoupgrade:16-alpine instead of postgres:16-alpine) this might or might not cause differences.

                  I would try increasing POSTGRES_POOL_SIZE to 10-20, but I am guessing here, the idea being that lemmy is hammering postgres through the default 5 conns which increases CPU but that is a bit of stretch

                  • DosDude👾@retrolemmy.comOP
                    link
                    fedilink
                    arrow-up
                    2
                    ·
                    4 months ago

                    It thankfully seems to have been fixed thanks to @graycube@lemmy.world. Running analyze verbose; in postgres.

                    The pgautoupgrade was added for the new version because this deployment is an all-in-one solution for running lemmy. And upgrading the databases turned out to be quite the effort until some user pointed the maintainer towards pgautoupgrade here.

                    I tried running lemmy before I found out about this, but this just makes it so much more convenient to run.