pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump -Z6 (the default) can be pretty slow
5+ messages / 2 participants
[nested] [flat]
* pg_dump -Z6 (the default) can be pretty slow
@ 2023-10-18 22:30 Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2023-10-18 22:30 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
In preparation for moving from 9.6 to something supported, I ran a
pg_dump/pg_restore test (since the migrated databases will be on new
servers, and we purge off old partitions and add new partitions, pg_upgrade
and logical replication are off the table).
(The servers are VMs on ESX hosts, and on the same subnet.)
Our databases are chock full of bytea fields holding compressed images.
pg_dump -Fd -Z6 took 25 minutes, and 5.5GB disk space. (remember, it's a
test!), while pg_dump -Fd -Z0 only took 90 seconds, but consumed 15GB.
This isn't really surprising to anyone who's ever tried to gzip a jpg file...
Quite the speed increase if you can swallow the increased disk usage.
pg_dump -Z3 did the best: only 8.5 minutes, while using just 5.8GB disk space.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump -Z6 (the default) can be pretty slow
@ 2023-10-18 22:37 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Ribe @ 2023-10-18 22:37 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
If you can use the directory format, then you can use multiple jobs to really speed up compressed dump (and restore).
Also, I'd suggest trying a run with lz4 compression--lz4 is particularly good at not slowing down when it encounters already-compressed data. Doesn't give really high compression ratios, but since you're already at -Z3, might be worth comparing.
(Personally, I stay away from zstd, as I've seen it create malformed backups because the encoder crashes with out-of-memory.)
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
> On Oct 18, 2023, at 4:30 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> In preparation for moving from 9.6 to something supported, I ran a pg_dump/pg_restore test (since the migrated databases will be on new servers, and we purge off old partitions and add new partitions, pg_upgrade and logical replication are off the table).
>
> (The servers are VMs on ESX hosts, and on the same subnet.)
>
> Our databases are chock full of bytea fields holding compressed images. pg_dump -Fd -Z6 took 25 minutes, and 5.5GB disk space. (remember, it's a test!), while pg_dump -Fd -Z0 only took 90 seconds, but consumed 15GB.
>
> This isn't really surprising to anyone who's ever tried to gzip a jpg file...
>
> Quite the speed increase if you can swallow the increased disk usage.
>
> pg_dump -Z3 did the best: only 8.5 minutes, while using just 5.8GB disk space.
>
> --
> Born in Arizona, moved to Babylonia.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump -Z6 (the default) can be pretty slow
@ 2023-10-18 23:21 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2023-10-18 23:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 10/18/23 17:37, Scott Ribe wrote:
> If you can use the directory format, then you can use multiple jobs to really speed up compressed dump (and restore).
It didn't occur to me to mention that I used it. Do people really still not
use -Fd?
> Also, I'd suggest trying a run with lz4 compression--lz4 is particularly good at not slowing down when it encounters already-compressed data. Doesn't give really high compression ratios, but since you're already at -Z3, might be worth comparing.
I'm still using 9.6, so that feature isn't available yet. When I get the Pg
15 VMs stood up (Pg15 binaries are not available for RHEL 6), I'm definitely
going to try that.
> (Personally, I stay away from zstd, as I've seen it create malformed backups because the encoder crashes with out-of-memory.)
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>> On Oct 18, 2023, at 4:30 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> In preparation for moving from 9.6 to something supported, I ran a pg_dump/pg_restore test (since the migrated databases will be on new servers, and we purge off old partitions and add new partitions, pg_upgrade and logical replication are off the table).
>>
>> (The servers are VMs on ESX hosts, and on the same subnet.)
>>
>> Our databases are chock full of bytea fields holding compressed images. pg_dump -Fd -Z6 took 25 minutes, and 5.5GB disk space. (remember, it's a test!), while pg_dump -Fd -Z0 only took 90 seconds, but consumed 15GB.
>>
>> This isn't really surprising to anyone who's ever tried to gzip a jpg file...
>>
>> Quite the speed increase if you can swallow the increased disk usage.
>>
>> pg_dump -Z3 did the best: only 8.5 minutes, while using just 5.8GB disk space.
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump -Z6 (the default) can be pretty slow
@ 2023-10-18 23:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Ribe @ 2023-10-18 23:42 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Oct 18, 2023, at 5:21 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> It didn't occur to me to mention that I used it. Do people really still not use -Fd?
I don't know--I guess it depends on context. Certainly for upgrades I don't know any reason not to.
> I'm still using 9.6, so that feature isn't available yet. When I get the Pg 15 VMs stood up (Pg15 binaries are not available for RHEL 6), I'm definitely going to try that.
I thought of that mere seconds after posting my prior reply ;-)
Have you considered pg_upgrade?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_dump -Z6 (the default) can be pretty slow
@ 2023-10-19 00:00 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ron @ 2023-10-19 00:00 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 10/18/23 18:42, Scott Ribe wrote:
>> On Oct 18, 2023, at 5:21 PM, Ron<ronljohnsonjr@gmail.com> wrote:
>>
>> It didn't occur to me to mention that I used it. Do people really still not use -Fd?
> I don't know--I guess it depends on context. Certainly for upgrades I don't know any reason not to.
Even for nightly backups (we still use pg_dump instead of pgbackrest on a
few smaller systems), I always use directory format backups.
>> I'm still using 9.6, so that feature isn't available yet. When I get the Pg 15 VMs stood up (Pg15 binaries are not available for RHEL 6), I'm definitely going to try that.
> I thought of that mere seconds after posting my prior reply ;-)
>
> Have you considered pg_upgrade?
Since "the migrated databases will be on new servers, and we purge off old
partitions and add new partitions, pg_upgrade and logical replication are
off the table".
And, of course, 9.6 binaries aren't available at all (at least I can't find
9.6.24 RPMs anywhere under https://download.postgresql.org/pub/), so
rsyncing the database files to the new server and then doing a pg_upgrade
won't work. Even then, I'd have to rebuild many indices anyway, due to glibc
locale changes.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-19 00:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-18 22:30 pg_dump -Z6 (the default) can be pretty slow Ron <ronljohnsonjr@gmail.com>
2023-10-18 22:37 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-18 23:21 ` Ron <ronljohnsonjr@gmail.com>
2023-10-18 23:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-19 00:00 ` Ron <ronljohnsonjr@gmail.com>
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