Date: Thu, 21 Aug 2025 14:03:02 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Thom Brown <thom@linux.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
	PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Streaming replica hangs periodically for ~ 1 second - how to
 diagnose/debug
Message-ID: <aKcK9vxLVldxFDT2@depesz.com>
Reply-To: depesz@depesz.com
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>
 <CAA-aLv6Vh04awXunt8mbHp_1vtT0fFLJpaXvs38ToW8mxRB2WA@mail.gmail.com>
 <aKbu6NaV5M0ifPn4@depesz.com>
 <CAA-aLv441nwn05V4HUnEhR_QLregsnmahihpDbZ2JufNMzgY3w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAA-aLv441nwn05V4HUnEhR_QLregsnmahihpDbZ2JufNMzgY3w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aKcK9vxLVldxFDT2%40depesz.com>
Precedence: bulk

On Thu, Aug 21, 2025 at 12:41:44PM +0100, Thom Brown wrote:
> Ah, yeah I meant transparent hugepage:
> cat /sys/kernel/mm/transparent_hugepage/enabled
> This should show it being set as "never".

Ah. Sorry, couldn't decipher. Yes, it's "never".

> > # grep -oP '^2025-08-19 22:09:2\d\.\d+ UTC' postgresql-2025-08-19_220000.csv | uniq -c | grep -C3 -P '^\s*\d\d'
> >       2 2025-08-19 22:09:29.084 UTC
> >       1 2025-08-19 22:09:29.094 UTC
> >       2 2025-08-19 22:09:29.097 UTC
> >      70 2025-08-19 22:09:29.109 UTC
> >      90 2025-08-19 22:09:29.110 UTC
> >       6 2025-08-19 22:09:29.111 UTC
> >       1 2025-08-19 22:09:29.153 UTC
> >       1 2025-08-19 22:09:29.555 UTC
…
> > 22:10:54     all    2.41    0.00    0.28    0.22    0.00    0.10    0.00    0.00    0.00   96.99
> > 22:10:59     all    2.83    0.00    0.29    0.19    0.00    0.12    0.00    0.00    0.00   96.57
> 
> This output looks fine, so it doesn't show anything concerning, so
> suggests the issue is somehow on the Postgres side.
> 
> Did you happen to poll pg_stat_activity at the time to see whether you
> had lots of IPC waits? I'm wondering whether the storage layer is
> freezing up for a moment.

So, we get select * from pg_stat_activity, for client backends that are
not idle, every 29 seconds.
So, 1 second "freeze" is impossible to cathc.

Plus - I suspect that if I ran select * from pg_stat_activity while "in
freeze", it would also get frozen.

Anyway, I have data from 22:09:22 and 22:09:51. In both cases only
4 non-idle backend.

6 of them had NULL in wait_event*

one was Client/ClientRead and one was IPC/BgWorkerShutdown.

State_change for the IPC/BgWorkerShutdown backend was 2025-08-19
22:09:51.79504+00 so it was well past the moment when the problem
struck.

Best regards,

depesz