Re: High COMMIT times

How far apart are the min/max connection settings on your application
connection pool? We had a similar issue with connection storms in the past
on Oracle. One thing we did to minimize the storms was make sure there was
not a wide gap between the min/max, say no more than a 5-10 connection
difference between min/max.

Regards,

Craig

On Sun, Jan 10, 2021 at 5:42 PM Don Seiler <don(at)seiler(dot)us> wrote:

> On Sat, Jan 9, 2021 at 2:07 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>>
>> How are you monitoring the COMMIT times? What do you generally see in
>> pg_stat_activity.wait_event during the spikes/stalls?
>>
>
> Right now we just observe the COMMIT duration posted in the postgresql log
> (we log anything over 100ms).
>
> One other thing that I shamefully forgot to mention. When we see these
> slow COMMITs in the log, they coincide with a connection storm (Cat 5
> hurricane) from our apps where connections will go from ~200 to ~1200. This
> will probably disgust many, but our PG server's max_connections is set to
> 2000. We have a set of pgbouncers in front of this with a total
> max_db_connections of 1600. I know many of you think this defeats the whole
> purpose of having pgbouncer and I agree. I've been trying to explain as
> much and that even with 32 CPUs on this DB host, we probably shouldn't
> expect to be able to support more than 100-200 active connections, let
> alone 1600. I'm still pushing to have our app server instances (which also
> use their own JDBC (Hikari) connection pool and *then* go through
> pgbouncer) to lower their min/max connection settings but obviously it's
> sort of counterintuitive at first glance but hopefully everyone sees the
> bigger picture.
>
> One nagging question I have is if the slow COMMIT is triggering the
> connection storm (eg app sees slow response or timeout from a current
> connection and fires off a new connection in its place), or vice-versa.
> We're planning to deploy new performant cloud storage (Azure Ultra disk)
> just for WAL logs but I'm hesitant to say it'll be a silver bullet when we
> still have this insane connection management strategy in place.
>
> Curious to know what others think (please pull no punches) and if others
> have been in a similar scenario with anecdotes to share.
>
> Thanks,
> Don.
>
> --
> Don Seiler
> www.seiler.us
>

--
Craig

--
This electronic communication and the information and any files transmitted
with it, or attached to it, are confidential and are intended solely for
the use of the individual or entity to whom it is addressed and may contain
information that is confidential, legally privileged, protected by privacy
laws, or otherwise restricted from disclosure to anyone else. If you are
not the intended recipient or the person responsible for delivering the
e-mail to the intended recipient, you are hereby notified that any use,
copying, distributing, dissemination, forwarding, printing, or copying of
this e-mail is strictly prohibited. If you received this e-mail in error,
please return the e-mail to the sender, delete it from your computer, and
destroy any printed copy of it.