RES: pg_dump slow

Complementing...


The test was maked at the same machine ( localhost ) at Command-Prompt,
no client´s connected, no concurrent processes only PostgreSQL running.

In windows XP, exists much access to the processor (+- 70%) and HD (I
see HD Led allways on), while in the W2K almost without activity of
processor (3%)and little access to the HardDisk (most time of the led HD
is off).

Look, the database has 81 Tables, one of these, has 2 fields ( one
integer and another ByteA ), these table as 5.150 Records.
I´m Dumpped only this table and the file size is 7Mb  (41% of total
(17MB is the total)) was very slow.... Then I Maked Backup of the others
tables was fast!

So i´m conclused that pg_dump and pg_restore is very slow when
manipulates ByteA type on W2K!, is this possible ?


Franklin



-----Mensagem original-----
De: Merlin Moncure [mailto:merlin.moncure@rcsonline.com]
Enviada em: quarta-feira, 30 de novembro de 2005 13:57
Para: Ron
Cc: pgsql-performance@postgresql.org; Franklin Haut
Assunto: RE: [PERFORM] 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