Re: Set log_lock_waits=on by default

On 12/21/23 6:58 AM, Nikolay Samokhvalov wrote:
> On Thu, Dec 21, 2023 at 05:29 Laurenz Albe <laurenz.albe@cybertec.at
> <mailto: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's a very good idea. On all heavily loaded systems I have
> observed so far, we always have turned it on. 1s (default
> deadlock_timeout) is quite large value for web/mobile apps, meaning that
> default frequency of logging is quite low, so any potential suffering
> from observer effect doesn't happen -- saturation related active session
> number happens much, much earlier, even if you have very slow disk IO
> for logging.

FWIW, enabling this setting has also been a long-time "happiness hint"
that I've passed along to people.

What would be the worst case amount of logging that we're going to
generate at scale? I think the worst case would largely scale according
to connection count? So if someone had a couple thousand backends on a
busy top-end system, then I guess they might generate up to a couple
thousand log messages every second or two under load after this
parameter became enabled with a 1 second threshold?

I'm not aware of any cases where enabling this parameter with a 1 second
threshold overwhelmed the logging collector (unlike, for example,
log_statement=all) but I wanted to pose the question in the interest of
being careful.


> At the same time, I like the idea by Robert to separate logging of log
> waits and deadlock_timeout logic -- the current implementation is a
> quite confusing for new users. I also had cases when people wanted to
> log lock waits earlier than deadlock detection. And also, most always
> lock wait logging lacks the information another the blocking session
> (its state, and last query, first of all), but is maybe an off topic
> worthing another effort of improvements.

I agree with this, though it's equally true that proliferation of new
GUCs is confusing for new users. I hope the project avoids too low of a
bar for adding new GUCs. But using the deadlock_timeout GUC for this
completely unrelated log threshold really doesn't make sense.

-Jeremy


-- 
http://about.me/jeremy_schneider