pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
help with pg_dump timings
4+ messages / 3 participants
[nested] [flat]

* help with pg_dump timings
@ 2005-10-12 13:36  Chris Hoover <revoohc@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Chris Hoover @ 2005-10-12 13:36 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: help with pg_dump timings
@ 2005-10-12 19:19  Jim C. Nasby <jnasby@pervasive.com>
  parent: Chris Hoover <revoohc@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-10-12 19:19 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: help with pg_dump timings
@ 2005-10-14 14:56  Arnau Rebassa Villalonga <arebassa@andromeiberica.com>
  parent: Chris Hoover <revoohc@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Arnau Rebassa Villalonga @ 2005-10-14 14:56 UTC (permalink / raw)
  To: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: help with pg_dump timings
@ 2005-10-17 14:28  Jim C. Nasby <jnasby@pervasive.com>
  parent: Arnau Rebassa Villalonga <arebassa@andromeiberica.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-10-17 14:28 UTC (permalink / raw)
  To: arnaulist@andromeiberica.com; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-10-17 14:28 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-12 13:36 help with pg_dump timings Chris Hoover <revoohc@gmail.com>
2005-10-12 19:19 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-14 14:56 ` Arnau Rebassa Villalonga <arebassa@andromeiberica.com>
2005-10-17 14:28   ` Jim C. Nasby <jnasby@pervasive.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