Re: filesystem full during vacuum - space recovery issues

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

On Wed, Jul 17, 2024 at 9:26 AM Thomas Simpson <ts@xxxxxxxxxxxxxx> wrote:

[snip] 

uge time; I know the hardware is capable of multi-GB/s throughput but the reload is taking a long time - projected to be about 10 days to reload at the current rate (about 30Mb/sec).  The old server and new server have a 10G link between them and storage is SSD backed, so the hardware is capable of much much more than it is doing now.

Is there a way to improve the reload performance?  Tuning of any type - even if I need to undo it later once the reload is done.

That would, of course, depend on what you're currently doing.  pg_dumpall of a Big Database is certainly suboptimal compared to "pg_dump -Fd --jobs=24".

This is what I run (which I got mostly from a databasesoup.com blog post) on the target instance before doing "pg_restore -Fd --jobs=24":

declare -i CheckPoint=30
declare -i SharedBuffs=32
declare -i MaintMem=3
declare -i MaxWalSize=36
declare -i 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" 

After the pg_restore -Fd --jobs=24 and vacuumdb --analyze-only --jobs=24:

pg_ctl stop -wt$TimeOut && pg_ctl start -wt$TimeOut

Of course, these parameter values were for my hardware.

My backups were in progress when all the issues happened, so they're not such a good starting point and I'd actually prefer the clean reload since this DB has been through multiple upgrades (without reloads) until now so I know it's not especially clean.  The size has always prevented the full reload before but the database is relatively low traffic now so I can afford some time to reload, but ideally not 10 days.

Yours,
Laurenz Albe