Message-ID: <201d6db4e58df881eb480008b6856fd7c34ee73f.camel@cybertec.at>
Subject: Re: Memory issues with PostgreSQL 15
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Christian =?ISO-8859-1?Q?Schr=F6der?= <christian.schroeder@wsd.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Cc: Francisco Olarte <folarte@peoplecall.com>, Tom Lane <tgl@sss.pgh.pa.us>,
  Eric Wong <eric.wong@wsd.com>
Date: Thu, 25 Jul 2024 14:26:03 +0200
In-Reply-To: <LO0P265MB270002E8C8CC879FE082A824F9AB2@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
References: 
	<LO0P265MB2700E0D30CC9A427956FC121F9F02@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
	 <CA+bJJbyzFdr1i1Yarqt=JnsN0WUg+veRvYZC1+kaYnWFGjiYRw@mail.gmail.com>
	 <LO0P265MB2700F3C159F47D8F039D6B1FF9F12@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
	 <3332.1717019021@sss.pgh.pa.us>
	 <LO0P265MB270002E8C8CC879FE082A824F9AB2@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3 (3.52.3-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/201d6db4e58df881eb480008b6856fd7c34ee73f.camel%40cybertec.at>
Precedence: bulk

On Thu, 2024-07-25 at 10:58 +0000, Christian Schr=C3=B6der wrote:
> The current error messages are similar to what we have seen before:
>=20
> <2024-07-25 12:27:38 CEST - > LOG:=C2=A0 could not fork autovacuum worker=
 process: Cannot allocate memory
> <2024-07-25 12:27:38 CEST - mailprocessor> ERROR:=C2=A0 could not resize =
shared memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space le=
ft on device
>=20
> As far as I understand, it does not make much sense to look into SysV sha=
red
> memory (which is what ipcs does). Indeed, there is only the same small sh=
ared
> memory segment as we have seen back then:
>=20
> [...]
>
> Francisco and Tom both pointed at Posix shared memory instead; however, t=
his
> also does not seem to be used a lot:
>=20
> # df -h /dev/shm
> Filesystem=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Size=C2=A0 Used Avail Use% Mount=
ed on
> tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 7.8G=C2=
=A0 6.6M=C2=A0 7.8G=C2=A0=C2=A0 1% /dev/shm
>=20
> We also still see a lot of available memory:
>=20
> # free -m
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 total=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 used=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 free=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 shared=C2=A0=
 buff/cache=C2=A0=C2=A0 available
> Mem:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 15882=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 6966=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 191=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2109=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 8725=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 6477
> Swap:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1999=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 271=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0 1728
>=20
> Again, exactly the same situation as before.
>=20
> Tom suggested that we hit some kernel limits, but I could not find any re=
lated
> kernel setting. The only limit I am aware of is the size of the /dev/shm =
filesystem
> itself. This could be changed, but the default value of 8 GB (which is ha=
lf of
> the machine's memory) seems to be enough (given that it is not even used)=
.
>=20
> Is there anything else I can analyze? Sorry again for reviving this old t=
hread.

It could be dynamic shared memory segments created temporarily during paral=
lel
query execution.

Try setting "max_parallel_workers_per_gather =3D 0", that should make that =
problem
disappear.

Yours,
Laurenz Albe