Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

If all your queries are coming through pgBouncer, and only those hang (the
server itself responds if you connect directly to it), then it might be
this pgBouncer issue:

https://github.com/pgbouncer/pgbouncer/issues/1054

Although that issue is now "closed", because the invisible "debug" log
message was upgraded to a warning (and I don't think that change is in any
released version), the underlying problem still exists: pgbouncer hangs
completely (stops forwarding packets) for a while if the PAM
authentication queue becomes full.

If you have a relatively slow PAM service (such as pam_ldap) then you can
trigger it by opening ~100 connections to pgBouncer simultaneously (without
waiting for previous ones to authenticate), something like this:

for i in `seq 1 100`; do psql -h pgbouncer -p 6432 -U user db_name -c
"SELECT 1" & done

Thanks, Chris.

On Thu, 21 Aug 2025 at 19:17, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 8/21/25 09:51, hubert depesz lubaczewski wrote:
> > On Thu, Aug 21, 2025 at 08:59:03AM -0700, Adrian Klaver wrote:
> >> Getting to the bottom of the bag of ideas:
> >> Have you looked at the OS system log for the time period involved?
> >
> > Yes. Mostly dmesg. Nothing interesting logged around the time.
> >
> >> You mentioned this seemed to involve PREPARE and DISCARD ALL.
> >> Is this the same set of statements or is it all over the place?
> >
> > No. From what I can tell it's random sample.
> >
> >> Also it would be helpful to know what bouncer you are actually using and
> >> what mode you are running in?
> >
> > pgBouncer, version 1.23.1. As for more... mostly transaction pooling.
> > Applications go using transaction pooling, but people (dbas, ops) have
> > session pooling.
>
> Have you looked at?:
>
> https://www.pgbouncer.org/changelog.html#pgbouncer-124x
>
> To see if anything stands out.
>
> Then there is:
>
> https://www.pgbouncer.org/config.html#max_prepared_statements
>
> The below may also be worth looking at:
>
> https://github.com/pgbouncer/pgbouncer/pull/1144
>
> I can't help thinking that there is a caching issue at stake, though
> that is just a guess.
>
>
> >
> > Best regards,
> >
> > depesz
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>