Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.500.231\))
Subject: Re: Unexplained rapid growth in memory usage of idle backends
From: Alex Balashov <abalashov@evaristesys.com>
In-Reply-To: 
 <CACut7uQd11zrHaWD_d8yDUxv7U_=tUR5+6POFHKFbF17EfU-Uw@mail.gmail.com>
Date: Sat, 13 May 2023 19:57:56 -0400
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <215B6284-33E2-421F-B234-D42567DFE2F8@evaristesys.com>
References: <2D17E15D-8D5B-4DF7-A238-12455C2731D8@evaristesys.com>
 <CACut7uQd11zrHaWD_d8yDUxv7U_=tUR5+6POFHKFbF17EfU-Uw@mail.gmail.com>
To: J T <jorge.torralba@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/215B6284-33E2-421F-B234-D42567DFE2F8%40evaristesys.com>
Precedence: bulk

Hi,

> On May 13, 2023, at 11:09 AM, J T <jorge.torralba@gmail.com> wrote:
>=20
> What is old_snapshot_threshold set to ?

It is set to the default -1.

> We had runaway and stacked up sessions were occuring which resulted in =
the system becoming practically useless. Transactions were dropping and =
sessions were climbing. We used the system profiling tool "perf" with =
top to trace the bottle neck down to spin locks. The large number of =
spin locks were bringing the system to its knees.  The perf output also =
showed the "TransactionLimitedForOldSnapshot" function as a consumer of =
resource. Which brought to mind a known issue with the parameter =
old_snapshot_threshold which had a non default value. There have been =
reported issues in the Postgres community about old_snapshot_threshold =
and bringing a system down completely under load. Have a look at that.

The setting is default, as per above. But moreover, the base workload is =
pretty low, while the problem appears suddenly, acutely and without =
warning despite no change (that we know of) in workload. It makes me =
think locking is probably not the problem, simply because that would =
manifest more "across the board".

> Also, this is really old but worth mentioning anyway, What are =
Transparent Huge pages set to at the kernel? I disable that regardless =
of it being resolved or not since it was a DB killer at one point.

THP is enabled. This system is a relatively stock Debian buster install =
and isn't tuned in any special way. The workload simply doesn't require =
it; it's not that heavy. That's mostly why this issue is surprising; if =
the database were constantly under resource pressure, it would be more =
understandable.

-- Alex

--=20
Alex Balashov
Principal Consultant
Evariste Systems LLC
Web: https://evaristesys.com
Tel: +1-706-510-6800