Обсуждение: Minimize checkpointer and walwriter io during pg_restore

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
restoringthe 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".

What might make a difference is if you use the --single-transaction option of
pg_restore.  If "wal_level = minimal", that could skip writing a substantial
amount of WAL.

Yours,
Laurenz Albe

On Tue, 2024-06-04 at 11:32 -0400, Ron Johnson wrote:
> > 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.

The *cluster* would be corrupted.
You'd have to start with a new "initdb".
If you want that... but it is a high price for very moderate gains.

> > What might make a difference is if you use the --single-transaction option of
> > pg_restore.
>
> A single 4+TB transaction??

Yes, sure.  Why not?

Yours,
Laurenz Albe

On Wed, 2024-06-05 at 08:22 -0400, Ron Johnson wrote:
> On Wed, Jun 5, 2024 at 5:52 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2024-06-04 at 11:32 -0400, Ron Johnson wrote:
> > > > 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".
> >
> > The *cluster* would be corrupted.
> > You'd have to start with a new "initdb".
>
> This is a brand new instance with zero databases.
>
> If the pg_restore which creates that one database fails for some reason, I just
> restart the pg_restore.  In the unlikely event that the VM crashes or becomes corrupt,
> doing an initdb and creating all the users again adds 10 minutes to the 7 hour pg_restore.
> That's "line noise" compared to the variability in performance of the VM, SAN and network.

Then go ahead and do it.  But I tell you that you won't see a noteworthy performance
gain with a large "pg_restore".

> > > > What might make a difference is if you use the --single-transaction option of
> > > > pg_restore.
> > >
> > > A single 4+TB transaction??
> >
> > Yes, sure.  Why not?
>  
> "Giant transactions bad, small transactions good", right?  It's been drilled into me for 35 years.

It is more "long transactions bad, short transactions good" in PostgreSQL, but that
will amount to the same in your case.

There are certainly negative effects of a large transaction, but I thought you want
to optimize the performance of a "pg_restore".  If you optimize one thing, you will
certainly pessimize some other things.  In the case at hand, you shouldn't run a
heavy data modifying workload in the same database concurrently to the large pg_restore.

The mere size of a transaction can be a problem as such in other databases like
Oracle, but not in PostgreSQL.

Yours,
Laurenz Albe