Re: Unexplained rapid growth in memory usage of idle backends

Hi,

> On May 13, 2023, at 11:09 AM, J T <jorge(dot)torralba(at)gmail(dot)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