Re: debugging intermittent slow updates under higher load

On 05/12/2018 15:40, Alexey Bashtanov wrote:
>
>>
> One of the reasons could be the row already locked by another backend,
> doing the same kind of an update or something different.
> Are these updates performed in a longer transactions?

Nope, the transaction will just be updating one row at a time.

> Can they hit the same row from two clients at the same time?

I've looked for evidence of this, but can't find any. Certainly nothing
running for 2-10s, queries against this table are normally a few hundred ms.

> Is there any other write or select-for-update/share load on the table?

Not that I'm aware of. How would I go about getting metrics on problems
like these?

> Have you tried periodical logging of the non-granted locks?
> Try querying pg_stat_activity and pg_locks (possibly joined and maybe
> repeatedly self-joined, google for it)
> to get the backends that wait one for another while competing for to
> lock the same row or object.

Is there any existing tooling that does this? I'm loath to start hacking
something up when I'd hope others have done a better job already...

Chris