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)
Date: Sat, 31 Dec 2022 08:32:03 -0600
Message-ID: <20221231143203.GK1153@telsasoft.com> (raw)
In-Reply-To: <CAK-MWwSfx7SmUQ=_rB4q61sE8uP6LqH-1wM0DSYyBadjBe9BDw@mail.gmail.com>
References: <CAK-MWwSfx7SmUQ=_rB4q61sE8uP6LqH-1wM0DSYyBadjBe9BDw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, maxim.boguk@gmail.com, 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)
In-Reply-To: <20221231143203.GK1153@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox