Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Memory issues with PostgreSQL 15
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <LO0P265MB270002E8C8CC879FE082A824F9AB2@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
Date: Thu, 25 Jul 2024 14:34:49 +0200
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>,
 Francisco Olarte <folarte@peoplecall.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Eric Wong <eric.wong@wsd.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C9B636BD-1939-4975-939E-E5AEAD7296C9@gmail.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>
To: =?utf-8?Q?Christian_Schr=C3=B6der?= <christian.schroeder@wsd.com>
Archived-At: <https://www.postgresql.org/message-id/C9B636BD-1939-4975-939E-E5AEAD7296C9%40gmail.com>
Precedence: bulk


> On 25 Jul 2024, at 12:58, Christian Schr=C3=B6der =
<christian.schroeder@wsd.com> wrote:
>=20
> Hi all,
> I started this discussion in May and was then dragged into other =
topics, so I could never follow up. Sorry for that!
> Since then, the problem has resurfaced from time to time. Right now, =
we seem to have issues again, which gives me the opportunity to follow =
up on your various suggestions.
>=20
> The current error messages are similar to what we have seen before:
>=20
> <2024-07-25 12:27:38 CEST - > LOG:  could not fork autovacuum worker =
process: Cannot allocate memory
> <2024-07-25 12:27:38 CEST - mailprocessor> ERROR:  could not resize =
shared memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space =
left on device

We sometimes encounter a similar issue, but with disk space - on a 1TB =
virtual disk of which usually only about 1/4th is in use.
Our hypothesis is that sometimes some long-running transactions need to =
process a lot of data and put so much of it in temporary tables that =
they fill up the remaining space. We=E2=80=99ve seen the disk space =
climb and hit the =E2=80=99No space left on device=E2=80=99 mark - at =
which point the transactions get aborted and rolled back, putting us =
back at the 1/4th of space in use situation.

Have you been able to catch your shared memory shortage in the act? I =
suspect that the stats you showed in your message were those after =
rollback.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.