Thread: help with pg_dump timings

I'm wondering, does anyone know of any timing tests for pg_dump?

I am trying to find the fastest way to do our nightly database backups using pg_dump.  And I just wanted to make sure I don't reinvent the wheel if someone has already done a bunch of comparisions with the various ways to backup.

Here are the kind of timings I'm looking at:

pg_dump -Fc -Zx
pg_dump -Fc
pg_dump -Ft
pg_dump
pg_dump -Fc | gzip > backup
pg_dump -Ft | gzip > backup
pg_dump | gzip > backup
pg_dump -Fc | bzip2 > backup
pg_dump -Ft | bzip2 > backup
pg_dump | bzip2 > backup

I know there are many variables, but I'm just looking at the pg_dump process.  Does anyone know what the fastest pg_dump would be?

Also, what -Z compression level equals the compression of gzip?  I've played with -Z9, but it seems to compress better than gzip (but takes a bit longer).

Thanks,

Chris

I rather doubt anyone has tested any of these timings. I've not seen
anything published, anyway. I suggest running them yourself to see
what's fastest.

I have noticed that (at least on FreeBSD), a seperate bzip2/gzip doesn't
utilize a second CPU, which is odd. But it's been a long time since I've
looked at this kind of thing.

If you do run tests, please share your findings with the community
(pgsql-general or -performance might be more appropriate than -admin).

On Wed, Oct 12, 2005 at 09:36:07AM -0400, Chris Hoover wrote:
> I'm wondering, does anyone know of any timing tests for pg_dump?
>
> I am trying to find the fastest way to do our nightly database backups using
> pg_dump. And I just wanted to make sure I don't reinvent the wheel if
> someone has already done a bunch of comparisions with the various ways to
> backup.
>
> Here are the kind of timings I'm looking at:
>
> pg_dump -Fc -Zx
> pg_dump -Fc
> pg_dump -Ft
> pg_dump
> pg_dump -Fc | gzip > backup
> pg_dump -Ft | gzip > backup
> pg_dump | gzip > backup
> pg_dump -Fc | bzip2 > backup
> pg_dump -Ft | bzip2 > backup
> pg_dump | bzip2 > backup
>
> I know there are many variables, but I'm just looking at the pg_dump
> process. Does anyone know what the fastest pg_dump would be?
>
> Also, what -Z compression level equals the compression of gzip? I've played
> with -Z9, but it seems to compress better than gzip (but takes a bit
> longer).
>
> Thanks,
>
> Chris

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Hi,

> Also, what -Z compression level equals the compression of gzip?  I've
> played with -Z9, but it seems to compress better than gzip (but takes a
> bit longer).

   I haven't done timing measurements but what I have noticed is that if
you use the custom format and after you try to compress the resultant
file, the size is not reduced that much, so it's not worth to spend time
compressing.

Cheers
--
Arnau

On Fri, Oct 14, 2005 at 04:56:27PM +0200, Arnau Rebassa Villalonga wrote:
> Hi,
>
> >Also, what -Z compression level equals the compression of gzip?  I've
> >played with -Z9, but it seems to compress better than gzip (but takes a
> >bit longer).
>
>   I haven't done timing measurements but what I have noticed is that if
> you use the custom format and after you try to compress the resultant
> file, the size is not reduced that much, so it's not worth to spend time
> compressing.

I haven't read the source, but I believe that by default custom format
dumps are compressed at zlib level 6. So if you want to compress them
yourself, you should sepcify -Z0.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461