Re: Cannot allocate memory

"JOIGNY Michael @Neteven" <mjoigny@xxxxxxxxxxx> · Tue, 22 Sep 2020 16:59:15 +0200



    Hi Hannah,
    Thanks for your feedback. 

    
    Here are the CPU / IO graphs over the same period. 

      
      You will find on the graphs the annotation which specifies the
      moment of the migration.

      
    Le 22/09/2020 à 15:10, Hannah Huang a
      écrit :

    
          On 22 Sep 2020, at 6:20 pm, JOIGNY Michael
            @Neteven <mjoigny@xxxxxxxxxxx> wrote:
          

              Hi,
              I would like to come back to you about my
                memory problem on postgres 12.

                
                We had the same configuration under postgres 11.8, we
                disabled JIT (enabled by default under postgres 12) for
                segfault problems. 

                
                To illustrate the change in memory behavior, here is a
                screenshot before / after migration : 

              
              <memory.png>
              

              Do you have any idea what could change the
                behavior in this way? another parameter enabled by
                default under postgres 12 like JIT?
              Regards.

              
              Le 18/09/2020 à 12:03, JOIGNY
                Michael @Neteven a écrit :

              
                Hi Community, 

                
                I'm asking for your lights because i'm
                  having memory problems with postgres.
                Examples of logs :

                
                FATAL: could not fork new
                    process for connection: Cannot allocate memory could
                    not fork new process for connection: Cannot allocate
                    memory

                    out of memory  DETAIL:  Failed on request of size
                    32800 in memory context "HashBatchContext".

                    out of memory DETAIL Failed on request of size 288
                    in memory context "CacheMemoryContext".

                  
                  We use postgresql (primary/standby) with pgbouncer as
                  a pooler, and repmgr as replication manager.
                We have ~ 2000 connections at the same time
                  with ~ 20/30 are active. (we need to set a high number
                  of connexion on postgres, because our app uses a lot
                  of different users, and each user on each app server
                  needs multiple and constant connexions).
                Here is my configuration : 

                
                system : 

                
                Debian : 9.13

                Memory : 380 Go

                Postgres : 12.4-1.pgdg90+1

                Pgbouncer : 1.14

                kernel.shmmax = 202591600640

                kernel.shmall = 49460840
                postgres : 

                  
                dynamic_shared_memory_type = posix    # the
                  default is the first option

                  max_connections = 2600            # (change requires
                  restart)

                  work_mem = 96MB                # min 64kB

                  maintenance_work_mem = 8GB        # min 1MB

                  shared_buffers = 64GB            # min 128kB

                  temp_buffers = 32MB            # min 800kB

                  wal_buffers = 16MB            # min 32kB, -1 sets
                  based on shared_buffers

                  effective_cache_size = 270GB

                
                pgbouncer : 

                
                max_client_conn = 6000

                  default_pool_size = 2590

                  reserve_pool_size = 5

                  pool_mode = session

                  
                  Do you think that our parameters are not correct
                  compared to our configuration? Do you have an idea ?

                
                Best regards.

                  
                  Michael.

                
      System settings: 
      After 9.2,
          PostgreSQL switched to POSIX shared memory. So now it requires
          fewer bytes of System V shared memory. 
      

        You don’t
            need to configure SHMMAX to that high value which is ~188GB,
            same as SHMALL, I would set them back to default value. 
        

        But, from
            the second email you sent it seems like an issue related to
            the version upgrade (from 11 to 12). So I don’t think you
            should change OS parameters at this moment. I would suggest
            you provide more stats on CPU (system, user, wa,ni) and disk
            IO - before and after. 
        

        Thanks,
            Hannah