From: Christoph Berg <myon@debian.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Set log_lock_waits=on by default
Date: Fri, 22 Dec 2023 11:33:52 +0100
Message-ID: <ZYVmEKKX_IlR72qk@msg.df7cb.de> (raw)
In-Reply-To: <CA+TgmobPaKeiXQ3q2Qb-ToLGPxy1kkOwi1AS16ZS0ryPKvPHTw@mail.gmail.com>
References: <b8b8502915e50f44deb111bc0b43a99e2733e117.camel@cybertec.at>
<CA+TgmobPaKeiXQ3q2Qb-ToLGPxy1kkOwi1AS16ZS0ryPKvPHTw@mail.gmail.com>
Re: Robert Haas
> On Thu, Dec 21, 2023 at 8:29 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Here is a patch to implement this.
> > Being stuck behind a lock for more than a second is almost
> > always a problem, so it is reasonable to turn this on by default.
>
> I think it depends somewhat on the lock type, and also on your
> threshold for what constitutes a problem. For example, you can wait
> for 1 second for a relation extension lock pretty easily, I think,
> just because the I/O system is busy. Or I think also a VXID lock held
> by some transaction that has a tuple locked could be not particularly
> exciting. A conflict on a relation lock seems more likely to represent
> a real issue, but I guess it's all kind of a judgement call. A second
> isn't really all that long on an overloaded system, and I see an awful
> lot of overloaded systems (because those are the people who call me).
If a system is so busy that it's waiting so long for the disk, I would
like PG to tell me about it. Likewise, if my transactions are slow
because they are waiting for each other, I'd also like PG to tell me.
Especially as the 2nd condition can't be seen by "it's slow because
CPU or IO is at 100%".
In any case, setting log_lock_waits=on by default helps.
In fact, everyone I talked to was wondering why log_checkpoints was
turned on by default, and not this parameter. The info provided by
log_lock_waits is much more actionable than the stream of
log_checkpoint messages.
> Just a random idea but what if we separated log_lock_waits from
> deadlock_timeout? Say, it becomes time-valued rather than
> Boolean-valued, but it has to be >= deadlock_timeout? Because I'd
> probably be more interested in hearing about a lock wait that was more
> than say 10 seconds, but I don't necessarily want to wait 10 seconds
> for the deadlock detector to trigger.
That's also a good point, but I'd like to see log_lock_waits default
to 'on' independently from having this extra change.
> In general, I do kind of like the idea of trying to log more problem
> situations by default, so that when someone has a major issue, you
> don't have to start by having them change all the logging settings and
> then wait until they get hosed a second time before you can
> troubleshoot anything. I'm just concerned that 1s might be too
> sensitive for a lot of users who aren't as, let's say, diligent about
> keeping the system healthy as you probably are.
I don't think 1s would be too sensitive by default.
Christoph
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: myon@debian.org, robertmhaas@gmail.com, laurenz.albe@cybertec.at
Subject: Re: Set log_lock_waits=on by default
In-Reply-To: <ZYVmEKKX_IlR72qk@msg.df7cb.de>
References: <b8b8502915e50f44deb111bc0b43a99e2733e117.camel@cybertec.at> <CA+TgmobPaKeiXQ3q2Qb-ToLGPxy1kkOwi1AS16ZS0ryPKvPHTw@mail.gmail.com> <ZYVmEKKX_IlR72qk@msg.df7cb.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox