Minimize checkpointer and walwriter io during pg_restore

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Minimize checkpointer and walwriter io during pg_restore
Date: 2024-06-04 13:14:09
Message-ID: CANzqJaCnU9WWWTaC7fMY0d8_acXZR_gnSMvDbKhSwjfBqwUfeQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

PG 14.12

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"

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Naveed Iftikhar 2024-06-04 14:44:18 Re: pgBackRest configuration with DDBoost
Previous Message Amitpal Singh 2024-06-04 04:36:10 Re: pgBackRest configuration with DDBoost