Re: Service outage: each postgres process use the exact amount of the configured work_mem

Melvin Davidson <melvin6925@xxxxxxxxx> · Fri, 14 Apr 2017 14:36:57 -0400

On Fri, Apr 14, 2017 at 2:28 PM, Moreno Andreo <moreno.andreo@xxxxxxxxxx> wrote:

    Melvin,

          Sorry for top-posting.    

      I'm going ahead in troubleshooting. As Jeff said, there's probably
      nothing wrong with my values (at the end of the message you can
      find minimal info you requested).

      I tried running some queries against psql server and response
      times are good, so I'm moving my attentions to Windows server,
      which hosts a WCF service, that is the one that actually server
      customers.

      Thanks for now

      Moreno

      Il 14/04/2017 20:01, Melvin Davidson ha scritto:

          On Fri, Apr 14, 2017 at 1:50 PM,
            Moreno Andreo <moreno.andreo@xxxxxxxxxx>
            wrote:

                Sorry,

                   my mistake (I'm a bit nervous...)

                  that's not work_mem, but shared_buffers

                  Thanks

                      Il 14/04/2017 19:33, Melvin Davidson ha scritto:

                          On Fri, Apr 14, 2017
                            at 1:12 PM, Moreno Andreo <moreno.andreo@xxxxxxxxxx>
                            wrote:

                               Hi all,

                                About 2 hours and half ago, suddenly
                                (and on the late afternoon of the Easter
                                Friday), customers reported failing
                                connections to our server, or even very
                                slow.

                                After a bit of checking (that also
                                involved server reboot) I noticed (using
                                top) that every process regarding
                                postgres is using exactly the amout I
                                configured as work_mem (3 GB). And AFAIK
                                it's not good.

                                30085 postgres 20 0 3370048
                                156656 153876 S 6.7 0.3 0:00.20 postgres

                                29833 postgres 20 0 3370000
                                65260 62416 S 1.7 0.1 0:00.17 postgres 

                                29632 postgres 20 0 3372468
                                11712 6028 S 0.7 0.0 0:00.60 postgres 

                                What can be happened?

                                Nothing has been touched....

                                postgresql 9.5.6 on debian 8 just
                                apt-get upgrade'd

                                Any help would be appreciated.

                                    Moreno.

                          >using exactly the amout I configured as
                            work_mem (3 GB).

                        You are right, that
                            is bad, but that is your own fault. 3GB of
                            work_mem is very bad, Try lowing in to
                            something more reasonable, like 20GB.

                        https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

                            "several running sessions could be doing
                            such operations concurrently. Therefore, the
                              total memory used could be many times the
                              value of work_mem;
                            it is necessary to keep this fact in mind
                            when choosing the value."

                          -- 

                            Melvin Davidson

                              I reserve
                                  the right to fantasize.  Whether or
                                  not you 

                                wish
                                  to share my fantasy is entirely up to
                                  you. 

              Moreno,

              we are working with minimal information here.

            Perhaps if you provided us with the following information it
            would be more useful,

          A. Total SYSTEM MEMORY

    52 GB

        B. shared_memory

    3 GB (was 13 GB)

        C. work_memory

    default

        D. max_connections

    1000

        E. How many users were connected when
          the problem occurred?

    About 350 connections

    Thanks

        -- 

            Melvin
                    Davidson

              I reserve the right to
                  fantasize.  Whether or not you 

                wish to share my
                  fantasy is entirely up to you. 

Moreno, 
I understand you will troubleshoot on your own, but based on total system memory, you should increase 
shared_memory to 40GB. General philosphy is to allocate 80% of system memory to shared_memory. Of course you will have to do a PostgreSQL restart 
for that to take effect.

Also, with 1000 max_connections, you would be much better off with a connection balancer like PgBouncer https://pgbouncer.github.io/downloads/ 

-- 
Melvin Davidson
I reserve the right to fantasize.  Whether or not you 
 wish to share my fantasy is entirely up to you. 