From: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
---|---|
To: | Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | RE: huge SubtransSLRU and SubtransBuffer wait_event |
Date: | 2024-02-06 06:59:11 |
Message-ID: | PH0PR11MB5191225F842C0D4C6EE23B54D6462@PH0PR11MB5191.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
We finally identified the cause, a pl/pgsql procedure proc1 (for 1…5000 loop call proc2()); proc2 (begin ..exception..end); at the same time, more than 200 sessions coming in milliseconds and do same query during the “call proc1 long running transaction”. The code change and cutdown the parallel sessions count doing same query at the same time help a lot.
Thanks all.
James
From: Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>
Sent: Friday, February 2, 2024 6:04 PM
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: huge SubtransSLRU and SubtransBuffer wait_event
On Thu, Feb 1, 2024 at 04:42 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | kimaidou | 2024-02-09 14:14:31 | Simple JOIN on heavy table not using expected index |
Previous Message | Laurenz Albe | 2024-02-02 10:31:19 | Re: huge SubtransSLRU and SubtransBuffer wait_event |