Re: Unexplained rapid growth in memory usage of idle backends

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.
Transactionswere dropping and sessions were climbing. We used the system profiling tool "perf" with top to trace the
bottleneck down to spin locks. The large number of spin locks were bringing the system to its knees.  The perf output
alsoshowed the "TransactionLimitedForOldSnapshot" function as a consumer of resource. Which brought to mind a known
issuewith the parameter old_snapshot_threshold which had a non default value. There have been reported issues in the
Postgrescommunity 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
probablynot 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
thatregardless 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
workloadsimply doesn't require it; it's not that heavy. That's mostly why this issue is surprising; if the database
wereconstantly 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