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?

I think he is saying the time to dump does not take more than 40 seconds, but I'm not sure.

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

5 mb/sec dump output from psql is not terrible or even bad, depending on hardware.

> >not pass of 3%.
> Assuming Win XP completes the dump, the first thing to do is
> *don't use W2K*

XP is not a server platform. Next level up is 2003 server. Many organizations still have 2k deployed. About half of my servers still run it. Anyways, the 2k/xp issue does not explain why there is a performance problem.

> 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!)

I would like to see some benchmarks supporting those claims. No comment on licensing issue, but there are many other factors in considering server platform than licensing costs. That said, there were several win32 specific pg performance issues that were rolled up into the 8.1 release. So for win32 you definitely want to be running 8.1.

> 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.

Problem is almost certainly not related to disk unless there is a imminent disk failure. Could be TCP/IP issue (are you running pg_dump from remote box?), or possibly a network driver issue or some other weird software issue. Can you determine if disk is running normally with respect to other applications? Is this a fresh win2k install? A LSP, virus scanner, backup software, or some other garbage can really ruin your day.

Merlin