public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Janes <[email protected]>
To: MentionTheElephant <[email protected]>
Cc: [email protected]
Subject: Re: Seeking guidance on extremely slow pg_restore despite strong I/O performance
Date: Sun, 7 Dec 2025 19:53:40 -0500
Message-ID: <CAMkU=1yak3bAWyTkhdtko5FCpT9i=Rzv8u3djafKT=eNpT1KPg@mail.gmail.com> (raw)
In-Reply-To: <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
References: <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
On Fri, Dec 5, 2025 at 5:30 AM MentionTheElephant <
[email protected]> wrote:
> The central question is: What should I examine further to understand
> why checkpoint processing becomes the dominant bottleneck during
> restore, despite fsync=off, synchronous_commit=off, 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–1100 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
= 3600 * 0.9 = 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
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Seeking guidance on extremely slow pg_restore despite strong I/O performance
In-Reply-To: <CAMkU=1yak3bAWyTkhdtko5FCpT9i=Rzv8u3djafKT=eNpT1KPg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox