[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Timing out A Blocker Based on Time or Count of Waiters
From:       Merlin Moncure <mmoncure () gmail ! com>
Date:       2024-03-26 1:52:48
Message-ID: CAHyXU0x8qWKkfOrMep6rA3H1DnnxS4Hf9NnXfqCb7S1aZrt9Rg () mail ! gmail ! com
[Download RAW message or body]

On Fri, Mar 22, 2024 at 11:25 AM Fred Habash <fmhabash@gmail.com> wrote:

> Facing an issue where sometimes humans login to a database and run DDL
> statements causing a long locking tree of over 1000 waiters. As a
> workaround, we asked developers to always start their DDL sessions
> with 'SET lock_timeout = 'Xs'.
>
> I reviewed the native lock timeout parameter in Postgres and found 7. None
> seem to be related to blocker timeouts directly.
>
> idle_in_transaction_session_timeout
> idle_session_timeout
> lock_timeout: How long a session waits for a lock
> statement_timeout
> authentication_timeout
> deadlock_timeout
> log_lock_waits
>
> Instead, I put together a quick procedure that counts waiter sessions for
> a given blocker and terminates it if waiter count exceeds a threshold.
>
> Is there not a native way to ...
> 1. Automatically time out a blocker
> 2. A metric that shows how many waiters for a blocker?
>


I guess this probably does not belong in the native codebase because in
most real world scenarios with contention you would end up with priority
inversion or a situation where no work gets done.  With current locking
rules, theoretically the work queue would always clear (assuming the locker
doesn't hold the transaction indefinitely), where with your setting enabled
it might not always assume the locker retries.

In your case, a hand written 'unblocker' script might be the way to go, or
(probably better) encourage patterns where critical tables are not blocked,
say by building up a scratch table and swapping in on a separate
transaction.   Reducing contention rather than mitigating the symptoms of
it, is *always* a good thing.

merlin

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Fri, Mar 22, 2024 at 11:25 AM Fred Habash &lt;<a \
href="mailto:fmhabash@gmail.com">fmhabash@gmail.com</a>&gt; wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Facing \
an issue where sometimes humans login to a database and run DDL statements causing a \
long locking tree of over 1000 waiters. As a workaround, we asked developers to \
always start their DDL sessions with  &#39;SET lock_timeout = &#39;Xs&#39;.  \
<div><br></div><div>I reviewed the native lock timeout parameter in Postgres and \
found 7. None seem to be related to blocker timeouts \
directly.</div><div><br></div><div>idle_in_transaction_session_timeout \
<br>idle_session_timeout <br>lock_timeout: How long a session waits for a \
lock<br>statement_timeout<br>authentication_timeout<br>deadlock_timeout \
<br>log_lock_waits<br><div><br></div><div>Instead, I put together a quick procedure \
that counts waiter sessions for a given blocker and terminates it if waiter count \
exceeds a threshold.</div><div><br></div><div>Is there not a native way to \
...</div><div>1. Automatically time out a blocker</div><div>2. A metric that shows \
how many waiters for a \
blocker?</div></div></div></blockquote><div><br></div><div><br></div><div>I guess \
this probably does not belong in the native codebase because in most real world \
scenarios with contention you would end up with priority inversion or a situation \
where no work gets done.   With current locking rules, theoretically  the work queue \
would always clear (assuming the locker doesn&#39;t hold the transaction \
indefinitely), where with your setting enabled it might not always assume the locker \
retries.</div><div><br></div><div>In your case, a hand written &#39;unblocker&#39; \
script might be the way to go, or (probably better) encourage patterns where critical \
tables are not blocked, say by building up a scratch  table and swapping in on a \
separate transaction.     Reducing contention rather than mitigating the symptoms of \
it, is *always* a good thing.</div><div><br></div><div>merlin    </div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]