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

From: Adrian Klaver <[email-masked]>
Date: Tue, 19 Aug 2025 11:39:03 -0700

On 8/19/25 10:21, hubert depesz lubaczewski wrote:
> Hi,
> we have following situation: Pg 14.17 (yes, I know, but it can't be
> upgraded now/soon), on Ubuntu focal, in AWS cloud on EC2 server using
> arm64 architecture.
> 
> All works, is fine.
> 
> Every now and then (usually every 3-5 minutes, but not through the whole
> day), we see situations where every query suddently takes ~ 1 second.
> 

Given the subject line, what you are reporting is happening on the 
replica, correct?

If so where is the replica relative to the primary, in terms of network 
distance?

Also what are the 'hardware' specifications on the replica instance?

-- 
Adrian Klaver
[email-masked]

---

From: Scot Kreienkamp <[email-masked]>
Date: Tue, 19 Aug 2025 20:25:26 +0000

Synchronous commit?  Or asynchronous?

Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy Corporate
(734) 384-6403 | 1-734-915-1444 | [email-masked]
One La-Z-Boy Drive | Monroe, Michigan 48162 | la-z-boy.com<http://www.la-z-boy.com/>
 facebook.com/lazboy<http://facebook.com/lazboy>  | instagram.com/lazboy<https://instagram.com/lazboy> | youtube.com/lazboy<http://youtube.com/lazboy>

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

---

From: Alban Hertroys <[email-masked]>
Date: Wed, 20 Aug 2025 11:41:19 +0200

> On 19 Aug 2025, at 22:25, Scot Kreienkamp <[email-masked]> wrote:
> 
> 匀礀渀挀栀爀漀渀漀甀猀 挀漀洀洀椀琀㼀  伀爀 愀猀礀渀挀栀爀漀渀漀甀猀㼀ഀ਀ഀ਀ഀ਀匀挀漀琀 䬀爀攀椀攀渀欀愀洀瀀 簀 䄀瀀瀀氀椀挀愀琀椀漀渀猀 䤀渀昀爀愀猀琀爀甀挀琀甀爀攀 䄀爀挀栀椀琀攀挀琀 簀 䰀愀ⴀ娀ⴀ䈀漀礀 䌀漀爀瀀漀爀愀琀攀ഀ਀⠀㜀㌀㐀⤀ ㌀㠀㐀ⴀ㘀㐀　㌀ 簀 ㄀ⴀ㜀㌀㐀ⴀ㤀㄀㔀ⴀ㄀㐀㐀㐀 簀 匀挀漀琀⸀䬀

This looks like a bug in Outlook (at work) that I ran into over 3 years ago after enabling beta-support for UTF-8 encoding… The fun part was that the messages looked perfectly fine on my end, but were gibberish on the receiving ends only. Is that bug still present perhaps?

Or is it just my end that turns Scott's (and only Scott's) messages into gibberish Chinese?

Alban Hertroys
--
There is always an exception to always.

---

From: Ron Johnson <[email-masked]>
Date: Wed, 20 Aug 2025 06:53:09 -0400

Scot's email wasn't gibberish to me (reading from chrome).  Spaces between
every character, and 99.9689% signature block (I did the math!), but all
English (and a giant png file).

On Wed, Aug 20, 2025 at 5:41 AM Alban Hertroys <[email-masked]> wrote:

> > 匀礀渀挀栀爀漀渀漀甀猀 挀漀洀洀椀琀㼀 伀爀 愀猀礀渀挀栀爀漀渀漀甀猀㼀ഀ਀ഀ਀ഀ਀匀挀漀琀 䬀爀攀椀攀渀欀愀洀瀀 簀 䄀瀀瀀氀椀挀愀琀椀漀渀猀
> 䤀渀昀爀愀猀琀爀甀挀琀甀爀攀 䄀爀挀栀椀琀攀挀琀 簀 䰀愀ⴀ娀ⴀ䈀漀礀 䌀漀爀瀀漀爀愀琀攀ഀ਀⠀㜀㌀㐀⤀ ㌀㠀㐀ⴀ㘀㐀 ㌀ 簀
> ㄀ⴀ㜀㌀㐀ⴀ㤀㄀㔀ⴀ㄀㐀㐀㐀 簀 匀挀漀琀⸀䬀
>
> This looks like a bug in Outlook (at work) that I ran into over 3 years
> ago after enabling beta-support for UTF-8 encoding… The fun part was that
> the messages looked perfectly fine on my end, but were gibberish on the
> receiving ends only. Is that bug still present perhaps?
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Wed, 20 Aug 2025 13:32:34 +0200

On Tue, Aug 19, 2025 at 11:39:03AM -0700, Adrian Klaver wrote:
> Given the subject line, what you are reporting is happening on the replica,
> correct?

Yes.

> If so where is the replica relative to the primary, in terms of network

=$ ping -c 10 primary
reports:
10 packets transmitted, 10 received, 0% packet loss, time 9181ms
rtt min/avg/max/mdev = 0.942/0.956/0.991/0.012 ms

> Also what are the 'hardware' specifications on the replica instance?

c8g.48xlarge ec2 instance. It is arm64, 192 cores, with 384 gb of ram.

As for storage, this is relatitvely slow, because this db is rather
small:
gp3 500gb volume, with 6000 iops. At no point is IO in any way close to
limits, the whole db fits easily in ram.

Best regards,

depesz

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Wed, 20 Aug 2025 13:35:20 +0200

> Synchronous commit?  Or asynchronous?

Isn't this potential problem on primary, and not replica? Anyway,
synchronous commit is set to "on", and "synchronous_standby_names" is
empty.

Best regards,

depesz

---

From: Pawel Veselov <[email-masked]>
Date: Wed, 20 Aug 2025 13:42:32 +0200

> This looks like a bug in Outlook (at work) that I ran into over 3 years ago after enabling beta-support for UTF-8 encoding… The fun part was that the messages looked perfectly fine on my end, but w…

Interesting.
Your email client probably incorrectly interprets UTF-16 encoding.
That's what the content charset is in Scott's email.
The first text character in Scott's email is "S" (U0053), but when
sending(?) it translates to 匀 (U5300).

Gmail web also is struggling with it, judging by the spaces I see
between the characters in the Scott's original. Those spaces are
replaced '0x00' chars from the first byte of each UTF-16 char.

-- 
With best of best regards
Pawel S. Veselov

---

From: Tom Lane <[email-masked]>
Date: Wed, 20 Aug 2025 08:03:39 -0400

> Or is it just my end that turns Scott's (and only Scott's) messages into gibberish Chinese?

What it looks like from here is that the message body is in UTF16:
every other byte is zero.  That gives my email client indigestion,
too.

			regards, tom lane

---

From: Alban Hertroys <[email-masked]>
Date: Wed, 20 Aug 2025 22:03:52 +0200

> On 20 Aug 2025, at 14:03, Tom Lane <[email-masked]> wrote:
> 

Actually, I think it’s misreporting the message content as UTF-16, while it is in fact encoded as UTF-8. So the error appears to be on the sender side.

Apparently, Apple Mail.app is rather strict about following the reported encoding, while Gmail for example does some attempt to guess. That would explain the less terrible results with Gmail.

I can base64 decode the message just fine on the command line, which runs in a terminal emulator (Apple’s Terminal.app) that does handle UTF-8, but not UTF-16 by default.

Alban Hertroys
--
Illustratief voor het verschil tussen steek- en snijwapens:
Met mensen die een punthoofd hebben,
kun je goed de draak steken.
Met mensen die scherp zijn,
lukt dat juist slecht.