Re: pg_dump slow

At 08:35 AM 11/30/2005, Franklin Haut wrote:
>Hi
>
>i´m using PostgreSQL on windows 2000, the pg_dump take around 50 minutes
>to do backup of 200Mb data ( with no compression, and 15Mb with
>compression),

Compression is reducing the data to 15/200= 3/40= 7.5% of original size?

>but in windows XP does not pass of 40 seconds... :(

You mean that 40 secs in pg_dump under Win XP
crashes, and therefore you have a WinXP problem?

Or do you mean that pg_dump takes 40 secs to
complete under WinXP and 50 minutes under W2K and
therefore you have a W2K problem?

In fact, either 15MB/40secs= 375KBps or
200MB/40secs= 5MBps is _slow_, so there's a problem under either platform!

>This happens with 8.1 and version 8.0, somebody
>passed for the same situation?
>
>It will be that a configuration in the priorities of the exists
>processes ? in Windows XP the processing of schemes goes 70% and
>constant accesses to the HardDisk, while that in windows 2000 it does
>not pass of 3%.
Assuming Win XP completes the dump, the first thing to do is
*don't use W2K*
M$ has stopped supporting it in anything but absolutely minimum fashion anyway.
_If_ you are going to use an M$ OS you should be using WinXP.
(You want to pay licensing fees for your OS, but
you are using free DB SW? Huh? If you are
trying to save $$$, use Open Source SW like Linux
or *BSD. pg will perform better under it, and it's cheaper!)

Assuming that for some reason you can't/won't
migrate to a non-M$ OS, the next problem is the
slow HD IO you are getting under WinXP.

What is the HW involved here? Particularly the
HD subsystem and the IO bus(es) it is plugged into?

For some perspective, Raw HD average IO rates for
even reasonably modern 7200rpm HD's is in the
~50MBps per HD range. Top of the line 15Krpm
SCSI and FC HD's have raw average IO rates of
just under 80MBps per HD as of this post.

Given that most DB's are not on 1 HD (if you DB
_is_ on only 1 HD, change that ASAP before you
lose data...), for anything other than a 2 HD
RAID 1 set I'd expect raw HD average IO rates to be at least 100MBps.

If you are getting >= 100MBps of average HD IO,
you should be getting > 5MBps during pg_dump, and certainly > 375MBps!

Ron