From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: filesystem full during vacuum - space recovery issues |
Date: | 2024-07-17 13:49:26 |
Message-ID: | CANzqJaCp6PLz2KJLzYJbNrLiddEf0bsYpaoauAzW1_fJnu8e+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-hackers |
On Wed, Jul 17, 2024 at 9:26 AM Thomas Simpson <ts(at)talentstack(dot)to> 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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Holger Jakobs | 2024-07-17 13:53:39 | Re: Error; pg_upgrade 10 to 15 |
Previous Message | Ron Johnson | 2024-07-17 13:34:30 | Re: Error; pg_upgrade 10 to 15 |
From | Date | Subject | |
---|---|---|---|
Next Message | Jacob Champion | 2024-07-17 14:09:44 | Re: PG_TEST_EXTRA and meson |
Previous Message | Robert Haas | 2024-07-17 13:44:01 | Re: Add a GUC check hook to ensure summarize_wal cannot be enabled when wal_level is minimal |