Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKzhX-0007Lc-BU for pgsql-admin@arkaria.postgresql.org; Wed, 23 Sep 2020 08:00:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kKzhV-0000Ce-FG for pgsql-admin@arkaria.postgresql.org; Wed, 23 Sep 2020 08:00:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKzhV-00008m-2y for pgsql-admin@lists.postgresql.org; Wed, 23 Sep 2020 08:00:29 +0000 Received: from mail-pf1-x441.google.com ([2607:f8b0:4864:20::441]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kKzhS-0003X2-3Y for pgsql-admin@lists.postgresql.org; Wed, 23 Sep 2020 08:00:28 +0000 Received: by mail-pf1-x441.google.com with SMTP id x123so14544732pfc.7 for ; Wed, 23 Sep 2020 01:00:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=ydObDGoLUtp4VtSl4GSdrFrmivjYTxribJIpFcYltIo=; b=jd+WJ58x0ml4BiuvMUfmmasx3q8biFKToRZLLwMI/CW9OyMFOHOr2MFbd1lVWZ187s yYiu6H+Jqbo0nhX4ghEql+awthsagV6CpIy1KLhiD39bIF2o8VTQUynQKXBV1bnU0cr0 zi0yBcceB3TvkTwWhsYCidcINlUSICm0g/GHGOyXyRdosTKhtMBgMO0Ef8vC/wQtcfpS yPYwGw92cW6aVcNSaPgeVm2QU3mizoz23v/SkBGgEAu3lxcKRi8YPtZFBAeLQNV8j9Jm L93HjQU5I90eF0oAbltCSffOppcyhSC+BMnQHKz5aIOWHX4FGEMfxuap2p7BdGfc7bCV 223Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=ydObDGoLUtp4VtSl4GSdrFrmivjYTxribJIpFcYltIo=; b=NoBMOnjK6a71ZHwlDwBju5bTVcqXOVuOC2hrbDpL2EIrL0+x0qXu4r2MCgC5F1aAHg SY7XCB+NV8Pt0Jyi60u+7KeZBNWxmsKkjw891QXy7TD/Xlbg9p73Oq7ZzkKPt5z8qq6q Gb8GTryG85IEWEqV4BWWt3HB3tyBTVuH8QdusFn0E05XHQ5cltt5DKPKqYSS4Rcmmfdn L203bDbHm8LKkEsPBoSJXODRHbVRoa7Y5IBgZo0cUsqj77xhAfE2TqooZIffHqCxFA+W Zo/SGOHV511A6BZRPrnS1esnpGVlWJ09cF2HkvY5EOnaEDOYm9KSpHcGhGU5tHZX7BTI Dnjw== X-Gm-Message-State: AOAM533/r1liZQoujVINglabQPKeZvVo9sbw1IJaINAMmK5bpY7Xq4w6 sPPrOOaLE9RrJeqSEW13iJclm9IFGKY= X-Google-Smtp-Source: ABdhPJw0+oP4P4rh1RHsHdT5NzH2iO9bxQXp5rvBNaadE4tKQhrcxfeMZZDV2G7xZ4KLEFVtd7evjQ== X-Received: by 2002:a63:547:: with SMTP id 68mr3896021pgf.177.1600848023630; Wed, 23 Sep 2020 01:00:23 -0700 (PDT) Received: from 192-168-1-114.tpgi.com.au ([203.63.94.51]) by smtp.gmail.com with ESMTPSA id k4sm18204045pfp.189.2020.09.23.01.00.21 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 23 Sep 2020 01:00:23 -0700 (PDT) From: Hannah Huang Message-Id: <3924CB72-367B-45A2-BBBC-419C46C8C0B9@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_4B2B7C00-8775-437E-9F5C-5E02B3B98CF0" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: Cannot allocate memory Date: Wed, 23 Sep 2020 18:00:16 +1000 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: mjoigny@neteven.com References: <2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com> <6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_4B2B7C00-8775-437E-9F5C-5E02B3B98CF0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 23 Sep 2020, at 2:14 pm, Hannah Huang = wrote: >=20 >=20 >=20 >> On 23 Sep 2020, at 12:59 am, JOIGNY Michael @Neteven = > wrote: >>=20 >> Hi Hannah, >>=20 >> Thanks for your feedback.=20 >>=20 >> Here are the CPU / IO graphs over the same period.=20 >>=20 >> You will find on the graphs the annotation which specifies the moment = of the migration. >>=20 >>=20 >> >>=20 >> >>=20 >> >>=20 >> >>=20 >>=20 >>=20 >> Le 22/09/2020 =C3=A0 15:10, Hannah Huang a =C3=A9crit : >>>=20 >>>=20 >>>> On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven = > wrote: >>>>=20 >>>> Hi, >>>>=20 >>>> I would like to come back to you about my memory problem on = postgres 12. >>>>=20 >>>> We had the same configuration under postgres 11.8, we disabled JIT = (enabled by default under postgres 12) for segfault problems.=20 >>>>=20 >>>> To illustrate the change in memory behavior, here is a screenshot = before / after migration :=20 >>>>=20 >>>> >>>>=20 >>>>=20 >>>> Do you have any idea what could change the behavior in this way? = another parameter enabled by default under postgres 12 like JIT? >>>>=20 >>>> Regards. >>>>=20 >>>> Le 18/09/2020 =C3=A0 12:03, JOIGNY Michael @Neteven a =C3=A9crit : >>>>> Hi Community,=20 >>>>>=20 >>>>> I'm asking for your lights because i'm having memory problems with = postgres. >>>>>=20 >>>>> Examples of logs : >>>>>=20 >>>>> 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". >>>>>=20 >>>>>=20 >>>>> We use postgresql (primary/standby) with pgbouncer as a pooler, = and repmgr as replication manager. >>>>>=20 >>>>> 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). >>>>>=20 >>>>> Here is my configuration :=20 >>>>>=20 >>>>> system :=20 >>>>>=20 >>>>> Debian : 9.13 >>>>> Memory : 380 Go >>>>> Postgres : 12.4-1.pgdg90+1 >>>>> Pgbouncer : 1.14 >>>>> kernel.shmmax =3D 202591600640 >>>>> kernel.shmall =3D 49460840 >>>>> postgres :=20 >>>>>=20 >>>>> dynamic_shared_memory_type =3D posix # the default is the first = option >>>>> max_connections =3D 2600 # (change requires restart) >>>>> work_mem =3D 96MB # min 64kB >>>>> maintenance_work_mem =3D 8GB # min 1MB >>>>> shared_buffers =3D 64GB # min 128kB >>>>> temp_buffers =3D 32MB # min 800kB >>>>> wal_buffers =3D 16MB # min 32kB, -1 sets based on = shared_buffers >>>>> effective_cache_size =3D 270GB >>>>>=20 >>>>> pgbouncer :=20 >>>>>=20 >>>>> max_client_conn =3D 6000 >>>>> default_pool_size =3D 2590 >>>>> reserve_pool_size =3D 5 >>>>> pool_mode =3D session >>>>>=20 >>>>> Do you think that our parameters are not correct compared to our = configuration? Do you have an idea ? >>>>>=20 >>>>> Best regards. >>>>>=20 >>>>> Michael. >>>>>=20 >>> System settings:=20 >>> After 9.2, PostgreSQL switched to POSIX shared memory. So now it = requires fewer bytes of System V shared memory.=20 >>>=20 >>> You don=E2=80=99t need to configure SHMMAX to that high value which = is ~188GB, same as SHMALL, I would set them back to default value.=20 >>>=20 >>> But, from the second email you sent it seems like an issue related = to the version upgrade (from 11 to 12). So I don=E2=80=99t 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.=20= >>>=20 >>> Thanks, Hannah >=20 > I can see: >=20 > CPU stats: CPU it spends more time on user job and the wait time on IO = decreases, time spent on kernal space is about same as before.=20 > IO stats: read drops dramatically, write slightly increases. This = explains why CPU wait on IO decreases. >=20 > I would like to know how is the performance of application queries. = For example, the before and after execution time of the most time = consuming queries. Are they still about the same? Do you have any = database performance analytics tools installed? I used to use pgbadger = to analyze database logs which would show me the past performance = statistics of the database. We now have an understanding about how the = OS performance looks like. It would be good to have a general picture = of the database performance from the db layer. >=20 > Thanks,=20 > Hannah >=20 >=20 >=20 >=20 Continue with my question regards application performance - Does = database experience performance degradation? Or just simply more failed = SQL with the particular memory error thrown out?=20 Further question: How often does the memory errors occur? Are they all caused by one = particular SQL or a certain type of SQL?=20 Would you mind sending out the log within a particular time range? Thanks, Suya --Apple-Mail=_4B2B7C00-8775-437E-9F5C-5E02B3B98CF0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 23 Sep 2020, at 2:14 pm, Hannah Huang <hannah.huang.y@gmail.com> wrote:



On 23 Sep 2020, at 12:59 am, JOIGNY Michael @Neteven <mjoigny@neteven.com>= wrote:

=20 =20

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.


<cpu.png>

<io_requests.png>

<io_bytes.png>

<io_time.png>


Le 22/09/2020 =C3=A0 15:10, Hannah = Huang a =C3=A9crit :


On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven <mjoigny@neteven.com> 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 =C3=A0 12:03, = JOIGNY Michael @Neteven a =C3=A9crit :

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 =3D 202591600640
kernel.shmall =3D 49460840

postgres :

dynamic_shared_memory_type =3D = posix    # the default is the first option
max_connections =3D 2600    =         # (change requires restart)
work_mem =3D 96MB        =         # min 64kB
maintenance_work_mem =3D 8GB    =     # min 1MB
shared_buffers =3D 64GB    =         # min 128kB
temp_buffers =3D 32MB    =         # min 800kB
wal_buffers =3D 16MB    =         # min 32kB, -1 sets based on shared_buffers
effective_cache_size =3D 270GB

pgbouncer :

max_client_conn =3D 6000
default_pool_size =3D 2590
reserve_pool_size =3D 5
pool_mode =3D 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=E2=80=99t 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=E2=80=99t = 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

  




I can =
see:

CPU =
stats: CPU it spends more time on user job and the wait time on IO =
decreases, time spent on kernal space is about same as =
before. 
IO stats: read drops dramatically, =
write slightly increases. This explains why CPU wait on IO =
decreases.

I =
would like to know how is the performance of application queries. For =
example, the before and after execution time of the most time consuming =
queries. Are they still about the same? Do you have any database =
performance analytics tools installed?  I used to use pgbadger to =
analyze  database logs which would show me the past performance =
statistics of the database. We now have an understanding about how the =
OS performance looks like.  It would be good to have a general =
picture of the database performance from the db layer.

Thanks, 
Hannah





Continue with my question regards application =
performance - Does database experience performance degradation? Or just =
simply more failed SQL with the particular memory error thrown =
out? 

Further =
question:

How often does the memory =
errors occur? Are they all caused by one particular SQL or a certain =
type of SQL? 

Would you mind =
sending out the log within a particular time range?




Thanks,
Suya

=

--Apple-Mail=_4B2B7C00-8775-437E-9F5C-5E02B3B98CF0--