DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:message-id:mime-version:content-type:content-transfer-encoding:x-priority:x-msmail-priority:x-mailer:in-reply-to:x-mimeole:importance;
	b=WtvlmfG2BPDq6PUczw2aH5f38ucS8KwZgF5+sSPUGfLUY30CZhY/wuKlUqPrpz6+uVIC9Z7i+gScihDIDjRqUVH9AksWoSqKFAf/rWvyFK/rqm9qkXUHt+sFP+Q/XgXkPW52CBPBPoNxXdqAqRPqPsVQV+k9s4O9cMFsLG5h7Ds=
From: "Franklin Haut" <franklin.haut@gmail.com>
To: "'Ron'" <rjpeace@earthlink.net>,
	<pgsql-performance@postgresql.org>
Subject: RES: pg_dump slow
Date: Wed, 30 Nov 2005 13:20:51 -0300
Message-ID: <000401c5f5ca$0aed7420$8500a8c0@FRANKLIN>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <6.2.5.6.0.20051130081636.01da1d48@earthlink.net>
Importance: Normal

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:=20
   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]=20
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=B4m using PostgreSQL on windows 2000, the pg_dump take around 50=20
>minutes to do backup of 200Mb data ( with no compression, and 15Mb with

>compression),

Compression is reducing the data to 15/200=3D 3/40=3D 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=20
crashes, and therefore you have a WinXP problem?

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

In fact, either 15MB/40secs=3D 375KBps or=20
200MB/40secs=3D 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=20
>processes ?  in Windows XP the processing of schemes goes 70% and=20
>constant accesses to the HardDisk, while that in windows 2000 it does=20
>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=20
you are using free DB SW?  Huh?  If you are=20
trying to save $$$, use Open Source SW like Linux=20
or *BSD.  pg will perform better under it, and it's cheaper!)


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

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

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

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

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

Ron