public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin <[email protected]>
To: Alec Cozens <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: LWLock SerializableFinishedList
Date: Tue, 9 Sep 2025 15:31:51 -0400
Message-ID: <CALL-XeO5e92GHJN6_krXLYLGAzWDv9qkgNp0uoio7Uqv6G0qdw@mail.gmail.com> (raw)
In-Reply-To: <LO2P123MB1711F0E3DF3BD9E9C092ACD4CD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
References: <LO2P123MB17113181847230A9D67C8602CD03A@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
<CALL-XeMUhHnqZx083rWx14xX2XjqBeq7h94TOfXBtoaSGzhxWg@mail.gmail.com>
<LO2P123MB1711F8A621404614ED676F7DCD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
<LO2P123MB1711F0E3DF3BD9E9C092ACD4CD0CA@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
On Mon, Sep 8, 2025 at 12:04 PM Alec Cozens <[email protected]> wrote:
> Looking at the postgresql code, the LW lock SerializableFinishedList
> appears to be acquired and then released, usually in the duration of a call
> to a procedure. Base on that (admittedly maybe faulty) view of the code, I
> am surprised to see the lock held open for over and hour and a half.
>
>
>
>
>
Hi Alec,
Clarification Lock_timeout and Statement_timeout work the same way, they
just differ in what triggers them. Lock timeout is triggered when a lock
can not be acquired in the specified time throwing an error.
Statement_Timeout is triggered when any specific command/query can not be
executed in the specific time.
the error message
could not truncate directory "pg_serial": apparent wraparound
This is concerning: do you have any replicas?? There is a known issue
when hot_standby_feedback = on and replica has long running transaction
running, SLRU will overflow causing PostgreSQL to go to a crawl. Only after
the replica releases whatever locks it has does performance return to
normal. This has been mitigated in PostgreSQL 17 with these configurable
settings
https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-MULTIXACT-MEMBER-BUFFERS
I have no idea if SLRU directly affects serialized transactions, I would
suspect that it does as SLRU affects all transactions and if it overflows
bad things start happening. The next time this happen look at the replica
to see if there are any long running transactions
All the conditions I can think of that can cause this is a transaction
sitting out there holding onto an XID causing the serialized transactions
to wait
Thank you
Justin
view thread (2+ messages)
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: LWLock SerializableFinishedList
In-Reply-To: <CALL-XeO5e92GHJN6_krXLYLGAzWDv9qkgNp0uoio7Uqv6G0qdw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox