Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCFny-004Gz3-20 for pgsql-general@arkaria.postgresql.org; Wed, 29 May 2024 09:41:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sCFnw-00ELOC-7V for pgsql-general@arkaria.postgresql.org; Wed, 29 May 2024 09:41:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCFnv-00ELO0-Lc for pgsql-general@lists.postgresql.org; Wed, 29 May 2024 09:41:07 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sCFno-001K2d-Kr for pgsql-general@lists.postgresql.org; Wed, 29 May 2024 09:41:06 +0000 Received: by mail-lj1-x22f.google.com with SMTP id 38308e7fff4ca-2e73359b8fbso21683851fa.2 for ; Wed, 29 May 2024 02:41:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1716975658; x=1717580458; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7E0cGnDGpvjo8BP7JAv7tyFwjKvs/+tO0dy+RUElPQU=; b=y/kYhZxiE/sUdMyCsfWKW82hU6cPjxboLO7Aj8l94ABD70LBML+ADnHKPt0pj3HWqa IZpLrxW9JDa3ErUKrs7uJ272f8TMbgduRkZAIpAZwDoIx5p7EcEB9Ql/XaDyRFk2qYcK EXczyP0Depwv/MjFGKp5OXWJj/fJWGLivyDwYKatovT6Q1yd8ZVxzxyHhBxc7ev7zvu9 CpOhbMNI4VtY76MvKeCZTWvSl/b8j+SHYieAvEygIIGGEGNHWe6wy8hSTvN5Jla8a4Zk YnSyuR29p8GduFpi5k6uNDv1mKOq63nzYsIbl+HmbCM0FvlZ18TDLZzAMRa14SpQb+26 KobQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716975658; x=1717580458; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=7E0cGnDGpvjo8BP7JAv7tyFwjKvs/+tO0dy+RUElPQU=; b=oxCy2BqUiZoC9gm03inI5Nf0IXUVn49PZy4M3J+wLCyA0aTuI0XBHwuOrtxRn6cOke ChdmfTeoKASfddpOeAC+ZYKp35AEGyiOdGg3O4Kq9fZzQKapLKutImq0Cd99Gmw9gulH 2L6M7VRfDhKvHnGdKMLoYQQ4+Nl6IJEf4X7K3PfLu+VM43jHnw1KGb5t/uubBcYku87g 1hRSIMVgeHk/ZQXkGaGBBzVyyauu+dZ7LYM05xOUxulXhJOUIwSvLi00Xpp9lWUoT+Ze TdC2IaDT95Z3Ey/vfZEsGeQ9Fw+1zs4nULgjAlYfuxRhPHLwfZ9P3964icphhyl3qvcN AaMw== X-Gm-Message-State: AOJu0Ywj+VMlcH85by1onMCQLIFI/eFJuNDM8sq9v8EfihsQ2iTOKHNu tU7NO1YOXMCeHKwVChTbuqXk5GtAd9bO3dECWcE5W8cC/sNQxZTLLThWxxdfh7rY0SgvK2nc1Hu TBQJ4QwU9XqDTMwkM8MdrV2jOUKiqU/bAwU8kvQ== X-Google-Smtp-Source: AGHT+IFi7ZfWkUGI1XsNT5CeAZiFa0rkbIn5Bts+KQy904VLen5W7slZga6KvpcH5BCR41ByrdawD7vEXI8YWwmbVDI= X-Received: by 2002:a2e:8e8c:0:b0:2dd:3803:e1da with SMTP id 38308e7fff4ca-2e95b0975b0mr134470701fa.12.1716975658037; Wed, 29 May 2024 02:40:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Salahuddin Manzoor Date: Wed, 29 May 2024 14:40:45 +0500 Message-ID: Subject: Re: Memory issues with PostgreSQL 15 To: =?UTF-8?Q?Christian_Schr=C3=B6der?= Cc: pgsql-general , Eric Wong Content-Type: multipart/alternative; boundary="0000000000002f91b60619948c92" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002f91b60619948c92 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greetings, The error message you encountered, "could not fork autovacuum worker process: Cannot allocate memory," indicates that your PostgreSQL server attempted to start an autovacuum worker process but failed because the system ran out of memory. Steps to verify. 1 Check system available memory with commands. free -m top 2. Check PG configurations. shared_buffers --Typically 25% of total mem. work_mem maintenance_work_mem--For maintenance op like autovaccume create index etc. Increase it to 64MB or appropriate to your requirement. max_connections Monitor /var/log/messages file for errors. 2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device Check share memory limits. /etc/sysctl.conf kernel.shmmax =3D 68719476736 # Example value, adjust as needed kernel.shmall =3D 16777216 # Example value, adjust as needed Restart system and db Ensure you have enough disk space available check and monitor disk space with command df -h Reduce max_parallel_workers_per_gather =3D 2; If it is set to high value. I think setting up OS parameter. Increasing maintenance mem value and reducing max paralell workers xan help in solution. Regards, Salahuddin. On Tue, 28 May 2024, 21:40 Christian Schr=C3=B6der, wrote: > Hi all, > We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, > we have a lot of memory issues in our QA environment (which is a bit tens= e > in resources). We did not have these problems before the migration, and w= e > do not have them in our production environment, which has a lot more > memory. So, it is not super critical for us, but I would still like to > understand better how we can improve our configuration. > > Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The > database server is a dedicated server with 15 GB RAM (and 4 cores, if thi= s > matters). > We used the following settings: > shared_buffers =3D 4GB > work_mem =3D 4MB > > After a while, we saw the following error in the logs: > > <2024-05-20 12:01:03 CEST - > LOG: could not fork autovacuum worker > process: Cannot allocate memory > > However, according to "free", a lot of memory was available: > > # free -m > total used free shared buff/cache > available > Mem: 15882 4992 463 4195 10427 > 6365 > Swap: 1999 271 1728 > > Our Grafana charts showed a slow increase in memory consumption until it > plateaus at 4.66 GB. > We also found the following error: > > <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize share= d > memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on > device > > I thought this could all be related to our "shared_buffers" setting, so I > increased it to 8 GB. This almost immediately (after a few minutes) gave = me > these errors: > > <2024-05-27 11:45:59 CEST - > ERROR: out of memory > <2024-05-27 11:45:59 CEST - > DETAIL: Failed on request of size 20108857= 4 > in memory context "TopTransactionContext". > ... > <2024-05-27 11:58:02 CEST - > ERROR: out of memory > <2024-05-27 11:58:02 CEST - > DETAIL: Failed while creating memory > context "dynahash". > <2024-05-27 11:58:02 CEST - > LOG: background worker "parallel worker" > (PID 21480) exited with exit code 1 > ... > <2024-05-27 12:01:02 CEST - > LOG: could not fork new process for > connection: Cannot allocate memory > <2024-05-27 12:01:03 CEST - > LOG: could not fork autovacuum worker > process: Cannot allocate memory > <2024-05-27 12:02:02 CEST - > LOG: could not fork new process for > connection: Cannot allocate memory > > Since this seemed worse than before, I changed the setting back to 4 GB. = I > noticed that "free" now reports even more available memory: > > # free -m > total used free shared buff/cache > available > Mem: 15882 621 320 2256 14940 > 12674 > Swap: 1999 199 1800 > > So, does the "shared_buffers" setting have the opposite effect than I > though? If I correctly remember similar discussions years ago, the databa= se > needs both "normal" and shared memory. By increasing the "shared_buffers" > to 8 GB, I may have deprived it of "normal" memory. On the other hand, I > would have expected the remaining 7 GB to still be enough. > > At this point, I am out of ideas. I clearly seem to misunderstand how the > database manages its memory. This may have changed between 9.4 and 15, so > my prior knowledge may be useless. I definitely need some help. =E2=98=B9 > > Thanks in advance, > Christian > > > ---------------------------------------------- > SUPPORT: > For any issues, inquiries, or assistance, please contact our support team > at support@wsd.com. Our dedicated team is available to help you and > provide prompt assistance. > > CONFIDENTIALITY NOTICE: > This email and any attachments are confidential and intended solely for > the use of the individual or entity to whom it is addressed. If you have > received this email in error, please notify the sender immediately and > delete it from your system. > --0000000000002f91b60619948c92 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Greetings,
=
The error message you encountered, &qu= ot;could not fork autovacuum worker process: Cannot allocate memory," = indicates that your PostgreSQL server attempted to start an autovacuum work= er process but failed because the system ran out of memory.

St= eps to verify.
1 Check system availa= ble memory with commands.
free -m
top
2. Check PG configurations.
shared_buffers --Typically 25% of total mem.
work_mem
maintenance_work_mem--For maintenanc= e op like autovaccume create index etc. Increase it to 64MB or appropriate = to your requirement.
max_co= nnections

Mon= itor /var/log/messages file for errors.

2024-05-21 11:34:46 CEST -= mailprocessor> ERROR:=C2=A0 could not resize shared memory segment &quo= t;/PostgreSQL.2448337832" to 182656 bytes: No space left on device

Check=C2=A0 shar= e memory limits.
/etc/sysct= l.conf
kernel.shmmax = =3D 68719476736=C2=A0 # Example value, adjust as needed
kernel.shmall =3D 16777216=C2=A0 =C2=A0 =C2=A0# Example value, adjust a= s needed

Restart system = and db

Ensure you have e= nough disk space available check and monitor disk space with command
<= div dir=3D"auto">df -h

Reduce=C2=A0 max_parallel_workers_per_gather =3D 2;<= /div>
If it is set to high value.
<= br>
I think setting up OS parameter.
Increasing maintenance mem value and reducing max paralell worker= s xan help in solution.

= Regards,
Sa= lahuddin.

On Tue, 28 May 2024, 21:40 Christian Schr=C3=B6der, &l= t;christian.schroeder@wsd.co= m> wrote:
Hi all,
We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, w= e have a lot of memory issues in our QA environment (which is a bit tense i= n resources). We did not have these problems before the migration, and we d= o not have them in our production environment, which has a lot more memory.= So, it is not super critical for us, but I would still like to understand = better how we can improve our configuration.

Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, com= piled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The dat= abase server is a dedicated server with 15 GB RAM (and 4 cores, if this mat= ters).
We used the following settings:
=C2=A0 =C2=A0 shared_buffers =3D 4GB
=C2=A0 =C2=A0 work_mem =3D 4MB

After a while, we saw the following error in the logs:

<2024-05-20 12:01:03 CEST - > LOG:=C2=A0 could not fork autovacuum wo= rker process: Cannot allocate memory

However, according to "free", a lot of memory was available:

# free -m
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 total=C2=A0 =C2=A0 =C2=A0 = =C2=A0 used=C2=A0 =C2=A0 =C2=A0 =C2=A0 free=C2=A0 =C2=A0 =C2=A0 shared=C2= =A0 buff/cache=C2=A0 =C2=A0available
Mem:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 15882=C2=A0 =C2=A0 =C2=A0 =C2=A0 499= 2=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0463=C2=A0 =C2=A0 =C2=A0 =C2=A0 4195=C2= =A0 =C2=A0 =C2=A0 =C2=A010427=C2=A0 =C2=A0 =C2=A0 =C2=A0 6365
Swap:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1999=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0271=C2=A0 =C2=A0 =C2=A0 =C2=A0 1728

Our Grafana charts showed a slow increase in memory consumption until it pl= ateaus at 4.66 GB.
We also found the following error:

<2024-05-21 11:34:46 CEST - mailprocessor> ERROR:=C2=A0 could not res= ize shared memory segment "/PostgreSQL.2448337832" to 182656 byte= s: No space left on device

I thought this could all be related to our "shared_buffers" setti= ng, so I increased it to 8 GB. This almost immediately (after a few minutes= ) gave me these errors:

<2024-05-27 11:45:59 CEST - > ERROR:=C2=A0 out of memory
<2024-05-27 11:45:59 CEST - > DETAIL:=C2=A0 Failed on request of size= 201088574 in memory context "TopTransactionContext".
...
<2024-05-27 11:58:02 CEST - > ERROR:=C2=A0 out of memory
<2024-05-27 11:58:02 CEST - > DETAIL:=C2=A0 Failed while creating mem= ory context "dynahash".
<2024-05-27 11:58:02 CEST - > LOG:=C2=A0 background worker "para= llel worker" (PID 21480) exited with exit code 1
...
<2024-05-27 12:01:02 CEST - > LOG:=C2=A0 could not fork new process f= or connection: Cannot allocate memory
<2024-05-27 12:01:03 CEST - > LOG:=C2=A0 could not fork autovacuum wo= rker process: Cannot allocate memory
<2024-05-27 12:02:02 CEST - > LOG:=C2=A0 could not fork new process f= or connection: Cannot allocate memory

Since this seemed worse than before, I changed the setting back to 4 GB. I = noticed that "free" now reports even more available memory:

# free -m
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 total=C2=A0 =C2=A0 =C2=A0 = =C2=A0 used=C2=A0 =C2=A0 =C2=A0 =C2=A0 free=C2=A0 =C2=A0 =C2=A0 shared=C2= =A0 buff/cache=C2=A0 =C2=A0available
Mem:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 15882=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0621=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0320=C2=A0 =C2=A0 =C2=A0 =C2=A0 2256= =C2=A0 =C2=A0 =C2=A0 =C2=A014940=C2=A0 =C2=A0 =C2=A0 =C2=A012674
Swap:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1999=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0199=C2=A0 =C2=A0 =C2=A0 =C2=A0 1800

So, does the "shared_buffers" setting have the opposite effect th= an I though? If I correctly remember similar discussions years ago, the dat= abase needs both "normal" and shared memory. By increasing the &q= uot;shared_buffers" to 8 GB, I may have deprived it of "normal&qu= ot; memory. On the other hand, I would have expected the remaining 7 GB to = still be enough.

At this point, I am out of ideas. I clearly seem to misunderstand how the d= atabase manages its memory. This may have changed between 9.4 and 15, so my= prior knowledge may be useless. I definitely need some help. =E2=98=B9

Thanks in advance,
Christian


----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team a= t s= upport@wsd.com. Our dedicated team is available to help you and provide= prompt assistance.

CONFIDENTIALITY NOTICE:
This email and any attachments are confidential and intended solely for the= use of the individual or entity to whom it is addressed. If you have recei= ved this email in error, please notify the sender immediately and delete it= from your system.
--0000000000002f91b60619948c92--