MIME-Version: 1.0
References: 
 <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
In-Reply-To: 
 <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Sun, 7 Dec 2025 19:53:40 -0500
Message-ID: 
 <CAMkU=1yak3bAWyTkhdtko5FCpT9i=Rzv8u3djafKT=eNpT1KPg@mail.gmail.com>
Subject: Re: Seeking guidance on extremely slow pg_restore despite strong I/O
 performance
To: MentionTheElephant <MentionTheElephant@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a8d2ba0645663a53"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1yak3bAWyTkhdtko5FCpT9i%3DRzv8u3djafKT%3DeNpT1KPg%40mail.gmail.com>
Precedence: bulk

--000000000000a8d2ba0645663a53
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Dec 5, 2025 at 5:30=E2=80=AFAM MentionTheElephant <
MentionTheElephant@gmail.com> wrote:


> The central question is: What should I examine further to understand
> why checkpoint processing becomes the dominant bottleneck during
> restore, despite fsync=3Doff, synchronous_commit=3Doff, and excellent

random write latency?
>

I don't see any evidence that checkpoint is the dominant bottleneck.
Checkpoints are not (usually) meant to be fast.  They pace themselves to
finish in the allotted time, while causing the least amount of drama while
they are underway.  The whole system shouldn't freeze up while waiting for
a checkpoint to run.

> Checkpoints appear to

> stall the entire restore process.
>

What makes it appear that way? Is it just that they take a long time to
finish, or is there some other evidence you haven't described?


> Example log excerpts show checkpoint write times consistently in the
> range of 600=E2=80=931100 seconds, with large numbers of buffers written =
(from
> hundreds of thousands to over 1.6 million).


You told it to shoot for checkpoint_timeout * checkpoint_completion_target
=3D 3600 * 0.9 =3D 3240 seconds.  If anything, it is weird that it takes so
little time, compared to what you authorized it to take.  As for the number
of buffers, it writes as many as it needs to write to get the job done.
Are the checkpoints driven by time, or by WAL volume?

The time reported for the write phase includes not only time spent doing
the writes, but also time spent sleeping so as to pace the checkpoint to
finish on schedule.  Perhaps those times should be reported separately.

I think that you have misinterpreted the evidence you have on hand, and are
now looking for an explanation for something which isn't true in the first
place.

What is the percent IO wait (called "wa") reported by `top` or `vmstat`
while the restore is running?

If you run with --verbose, you can see each step as it starts them. Which
steps take the most time?

Cheers,

Jeff

--000000000000a8d2ba0645663a53
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Dec 5, 2025 at 5:30=E2=80=AFAM Me=
ntionTheElephant &lt;<a href=3D"mailto:MentionTheElephant@gmail.com">Mentio=
nTheElephant@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_=
quote_container"><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">The central question is: What should I examine further to unders=
tand<br>
why checkpoint processing becomes the dominant bottleneck during<br>
restore, despite fsync=3Doff, synchronous_commit=3Doff, and excellent=C2=A0=
</blockquote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">random write=
 latency?<br></blockquote><div><br></div><div>I don&#39;t see any evidence =
that checkpoint is the dominant bottleneck. Checkpoints are not (usually) m=
eant to be fast.=C2=A0 They pace themselves to finish in the allotted=C2=A0=
time, while=C2=A0causing the least amount of drama while they are underway.=
=C2=A0 The whole system shouldn&#39;t freeze up while waiting for a checkpo=
int to run.</div><div>=C2=A0</div><div>&gt; Checkpoints appear to</div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">
stall the entire restore process.<br></blockquote><div><br></div><div>What =
makes it appear that way? Is it just that they take a long time to finish,=
=C2=A0or is there some other evidence you=C2=A0haven&#39;t described?</div>=
<div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Example log excerpts show checkpoint write times consistently in the<br>
range of 600=E2=80=931100 seconds, with large numbers of buffers written (f=
rom<br>
hundreds of thousands to over 1.6 million). </blockquote><div><br></div><di=
v>You told it to shoot for=C2=A0checkpoint_timeout * checkpoint_completion_=
target =3D 3600 * 0.9 =3D 3240 seconds.=C2=A0 If anything, it is weird that=
 it takes so little time,=C2=A0compared to what you authorized it=C2=A0to t=
ake.=C2=A0 As for the number of buffers, it writes as many as it needs to w=
rite to get the job done.=C2=A0 Are the checkpoints driven by time, or by W=
AL volume?</div><div><br></div><div>The time reported for the write phase i=
ncludes not only time spent doing the writes, but also time spent sleeping =
so as to pace the checkpoint to finish on schedule.=C2=A0 Perhaps those tim=
es should be reported separately.</div><div><br></div><div>I think that you=
 have misinterpreted=C2=A0the evidence you have on hand, and are now lookin=
g for an explanation for something which isn&#39;t true in=C2=A0the first p=
lace.</div><div><br></div><div>What is the percent IO wait (called &quot;wa=
&quot;) reported by `top` or `vmstat` while the restore is running?</div><d=
iv><br></div><div>If you run with --verbose, you can see each step as it st=
arts them. Which steps take the most time?</div><div><br></div><div>Cheers,=
</div><div><br></div><div>Jeff</div></div></div>

--000000000000a8d2ba0645663a53--