From: Justin Pryzby <pryzby@telsasoft.com>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Duncan Sands <duncan.sands@deepbluecap.com>
Cc: pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Subject: Re: Unlimited memory consumption with long-lived connection
Date: Wed, 22 Feb 2023 08:53:41 -0600
Message-ID: <20230222145341.GT1653@telsasoft.com> (raw)
In-Reply-To: <7484b4bd-a808-ed9e-892c-933f7f24120f@iki.fi>
References: <a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com>
<7484b4bd-a808-ed9e-892c-933f7f24120f@iki.fi>
On Tue, Feb 21, 2023 at 04:32:02PM +0200, Heikki Linnakangas wrote:
> On 21/02/2023 14:57, Duncan Sands wrote:
> > If I execute the attached python script against a postgresql 15.2 (Ubuntu
> > 15.2-1.pgdg22.10+1) server, with the default configuration (eg shared_buffers =
> > 128M), then the server memory usage goes up and up, apparently endlessly. After
> > about 10 minutes (on my laptop) pg_top shows RES memory usage for the back-end
> > handling the connection as greater than 1 gigabyte, which seems far too high
> > given the server configuration. The script just performs the same SELECT
> > endlessly in a loop. The memory is released when the script is killed.
> >
> > Platform: Ubuntu 22.10; Linux version 5.19.0-31-generic; x86-64.
> >
> > PS: The testcase was reduced from a script that kept a connection open for a
> > long time in order to LISTEN, and would execute a query using the same
> > connection every time there was a notification on the channel. It consumed ever
> > more memory to the point of crashing the postgresql server. Changing the script
> > to perform the query using a new short-lived connection was an effective workaround.
It sounds like the same as the issue here:
https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items
There are patches proposed here, which fixed the issue for me.
But I've always been suspicious that there may be a 2nd, undiagnosed
issue lurking behind this one...
https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: pryzby@telsasoft.com, hlinnaka@iki.fi, duncan.sands@deepbluecap.com, andres@anarazel.de
Subject: Re: Unlimited memory consumption with long-lived connection
In-Reply-To: <20230222145341.GT1653@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox