From: Alex Balashov <abalashov@evaristesys.com>
To: J T <jorge.torralba@gmail.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Unexplained rapid growth in memory usage of idle backends
Date: Sat, 13 May 2023 19:57:56 -0400
Message-ID: <215B6284-33E2-421F-B234-D42567DFE2F8@evaristesys.com> (raw)
In-Reply-To: <CACut7uQd11zrHaWD_d8yDUxv7U_=tUR5+6POFHKFbF17EfU-Uw@mail.gmail.com>
References: <2D17E15D-8D5B-4DF7-A238-12455C2731D8@evaristesys.com>
<CACut7uQd11zrHaWD_d8yDUxv7U_=tUR5+6POFHKFbF17EfU-Uw@mail.gmail.com>
Hi,
> On May 13, 2023, at 11:09 AM, J T <jorge.torralba@gmail.com> wrote:
>
> 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
--
Alex Balashov
Principal Consultant
Evariste Systems LLC
Web: https://evaristesys.com
Tel: +1-706-510-6800
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-admin@postgresql.org
Cc: abalashov@evaristesys.com, jorge.torralba@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Unexplained rapid growth in memory usage of idle backends
In-Reply-To: <215B6284-33E2-421F-B234-D42567DFE2F8@evaristesys.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