Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

On Sat, Dec 31, 2022 at 02:26:08PM +0200, Maxim Boguk wrote:
> Hi,
>
> When performing post-mortem analysis of some short latency spikes on a
> heavily loaded database, I found that the reason for (less than 10 second
> latency spike) wasn't on the EXECUTE stage but on the BIND stage.
> At the same time graphical monitoring shows that during this few second
> period there were some queries waiting in the BIND stage.
>
> Logging setup:
> log_min_duration_statement=200ms
> log_lock_waits=on
> deadlock_timeout=100ms
> So I expected that every lock waiting over 100ms (>deadlock_timeout) should
> be in the log.
> But in the log I see only spikes on slow BIND but not lock waits logged.

What version postgres? What settings have non-default values ?
What OS/version? What environment/hardware? VM/image/provider/...
What are the queries that are running BIND ? What parameter types ?
Are the slow BINDs failing? Are their paramters being logged ?
What else is running besides postgres ? Are the DB clients local or
remote ? It shouldn't matter, but what client library?

https://wiki.postgresql.org/wiki/Slow_Query_Questions

--
Justin