Date: Sat, 31 Dec 2022 08:32:03 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Maxim Boguk <maxim.boguk@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How to analyze of short but heavy intermittent slowdown on BIND
 on production database (or BIND vs log_lock_waits)
Message-ID: <20221231143203.GK1153@telsasoft.com>
References: 
 <CAK-MWwSfx7SmUQ=_rB4q61sE8uP6LqH-1wM0DSYyBadjBe9BDw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAK-MWwSfx7SmUQ=_rB4q61sE8uP6LqH-1wM0DSYyBadjBe9BDw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20221231143203.GK1153%40telsasoft.com>
Precedence: bulk

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