Horton

Re: huge SubtransSLRU and SubtransBuffer wait_event

Nikolay Samokhvalov <samokhvalov@gmail.com>

View thread View on postgr.es
From: Nikolay Samokhvalov <samokhvalov@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-performance@lists.postgresql.org
Date: 2024-02-02T10:04:02Z
Lists: pgsql-performance 
On Thu, Feb 1, 2024 at 04:42 Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> Today, the only feasible solution is not to create more than 64
> subtransactions
> (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.


Sometimes, a single subtransaction is enough to experience a bad
SubtransSLRU spike:
https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful#problem-4-subtrans-slru-overflow

I think 64+ nesting level is quite rare, but this kind of problem that hits
you when you have high XID growth (lots of writes) + long-running
transaction is quite easy to bump into. Or this case involving
MultiXactIDs:
https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/

Nik

>