RES: pg_dump slow - Mailing list pgsql-performance

Hi,

Yes, my problem is that the pg_dump takes 40 secs to complete under
WinXP and 50 minutes under W2K! The same database, the same hardware!,
only diferrent Operational Systems.

The hardware is:
   Pentium4 HT 3.2 GHz
   1024 Mb Memory
   HD 120Gb SATA

Im has make again the test, and then real size of database is 174Mb
(avaliable on pg_admin, properties) and the file size of pg_dump is 18Mb
( with command line  pg_dump -i -F c -b -v -f "C:\temp\BackupTest.bkp"
NameOfDatabase  ). The time was equal in 40 seconds on XP and 50 minutes
on W2K, using PG 8.1

Unhappyly for some reasons I cannot use other platforms, I need use PG
on Windows, and must be W2K.

Is strange to have a so great difference in the time of execution of
dump, therefore the data are the same ones and the archive is being
correctly generated in both OS.

Franklin

-----Mensagem original-----
De: Ron [mailto:rjpeace@earthlink.net]
Enviada em: quarta-feira, 30 de novembro de 2005 10:57
Para: Franklin Haut; pgsql-performance@postgresql.org
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?

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