Re: Minimize checkpointer and walwriter io during pg_restore

On Tue, Jun 4, 2024 at 11:21 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2024-06-04 at 09:14 -0400, Ron Johnson wrote:
> > Currently, when doing a pg_restore with 24 threads, I see this when
> using iotop:
> > checkpointer 90+ MB/s
> > walwriter 45+ MB/s
> >
> > Is there any way to minimize it more than I already have, or is that
> just how much work needs to be done when restoring the database?
> >
> > Here's the current configuration (which will be reset after the
> post-restore ANALYZE):
> > declare -gi SharedBuffs=32
> > declare -gi MaintMem=3 # so the oom killer doesn't kill it
> > declare -gi CheckPoint=30
> > declare -gi MaxWalSize=36
> > declare -gi WalBuffs=64
> > pg_ctl restart -wt$TimeOut -mfast \
> > -o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
> > -o "-c fsync=off" \
> > -o "-c log_statement=none" \
> > -o "-c log_temp_files=100kB" \
> > -o "-c log_checkpoints=on" \
> > -o "-c log_min_duration_statement=120000" \
> > -o "-c shared_buffers=${SharedBuffs}GB" \
> > -o "-c maintenance_work_mem=${MaintMem}GB" \
> > -o "-c synchronous_commit=off" \
> > -o "-c archive_mode=off" \
> > -o "-c full_page_writes=off" \
> > -o "-c checkpoint_timeout=${CheckPoint}min" \
> > -o "-c max_wal_size=${MaxWalSize}GB" \
> > -o "-c wal_level=minimal" \
> > -o "-c max_wal_senders=0" \
> > -o "-c wal_buffers=${WalBuffs}MB" \
> > -o "-c autovacuum=off"
>
> I would not set "fsync" to off. It won't make a measurable difference for
> pg_restore, and it may break your database. The same applies to
> "full_page_writes".
>

If the pg_restore fails, I'm dropping the database no matter what. A few
more minutes to drop $PGDATA and re-run initdb won't be noticed compared to
the length of the pg_restore.

> What might make a difference is if you use the --single-transaction option
> of
> pg_restore.

A single 4+TB transaction??

> If "wal_level = minimal", that could skip writing a substantial
> amount of WAL.

That's already one of the pg_ctl restart options.