Re: Set log_lock_waits=on by default

Re: Robert Haas
> On Thu, Dec 21, 2023 at 8:29 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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