Feedback-ID: i76984098:Fastmail
Message-ID: <f32fb472-c5f1-4cd6-a4c3-bc7c160e735b@aklaver.com>
Date: Thu, 21 Aug 2025 08:59:03 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Streaming replica hangs periodically for ~ 1 second - how to
 diagnose/debug
To: depesz@depesz.com
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <aKSyq74zIuLS-2B5@depesz.com>
 <d1d40328-ae3a-4089-9ee0-beade92d7c45@aklaver.com>
 <aKWyUpvXqXjJLtVx@depesz.com>
 <05969854-0d19-4726-ae1b-586659dd443b@aklaver.com>
 <aKXy64uaOasow3eK@depesz.com>
 <cbee6b61-1747-45de-9da1-39ce7022d036@aklaver.com>
 <aKbvygjgBWG1WExx@depesz.com>
 <25334887-f1c3-40a1-94b0-753c7d67ae2b@aklaver.com>
 <aKc3pidKUSzq-1-8@depesz.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <aKc3pidKUSzq-1-8@depesz.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/f32fb472-c5f1-4cd6-a4c3-bc7c160e735b%40aklaver.com>
Precedence: bulk

On 8/21/25 08:13, hubert depesz lubaczewski wrote:
> On Thu, Aug 21, 2025 at 08:04:25AM -0700, Adrian Klaver wrote:

>> By bouncer I assume you mean something like pgBouncer, a connection pooler.
>> Is it possible to determine what bouncer the queries in question are coming
>> from?
> 
>  From the POV of db, all queries are coming from one of N localhost
> bouncers. N is usually 2…6.
>  From the POV of the local bouncers, the queries come from range of
> remote bouncers.
> 
> Generally we haven't seen any correlation between queries coming from
> specific ranges of ips. Logged queries, the ones that we see with
> runtime of 1s, have comments that indicate source, and they some from
> "all-around". Specifically, "DISCARD ALL" queries are generated by
> bouncers themselves (both layers).

Well so much for that theory:)

> 
> Just so that it will be clear, I don't expect anyone to be able to
> diagnose the problem based on description. I'm looking more into idea
> what to look for. The issue is that with the situation being pretty
> short, and happening on servers with non-trivial query load, I can't do
> stuff, like, for example, strace, stuff.

Getting to the bottom of the bag of ideas:

Have you looked at the OS system log for the time period involved?

You mentioned this seemed to involve PREPARE and DISCARD ALL.
Is this the same set of statements or is it all over the place?

Also it would be helpful to know what bouncer you are actually using and 
what mode you are running in?


> 
> Best regards,
> 
> depesz
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com