From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron Johnson <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Minimize checkpointer and walwriter io during pg_restore
Date: Tue, 04 Jun 2024 17:21:08 +0200
Message-ID: <ba92a3e5ad5b44b0ead518bb3ea53040229aad42.camel@cybertec.at> (raw)
In-Reply-To: <CANzqJaCnU9WWWTaC7fMY0d8_acXZR_gnSMvDbKhSwjfBqwUfeQ@mail.gmail.com>
References: <CANzqJaCnU9WWWTaC7fMY0d8_acXZR_gnSMvDbKhSwjfBqwUfeQ@mail.gmail.com>
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".
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
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: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Minimize checkpointer and walwriter io during pg_restore
In-Reply-To: <ba92a3e5ad5b44b0ead518bb3ea53040229aad42.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox