Thread: pg_dump slow

pg_dump slow

From
"Franklin Haut"
Date:
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), but in windows XP does not pass of 40 seconds... :(

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

thanks

Franklin


Re: pg_dump slow

From
Ron
Date:
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



RES: pg_dump slow

From
"Franklin Haut"
Date:
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



Re: pg_dump slow

From
"Merlin Moncure"
Date:
> 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
myservers 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
factorsin considering server platform than licensing costs.  That said, there were several win32 specific pg
performanceissues 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
yourunning pg_dump from remote box?), or possibly a network driver issue or some other weird software issue.  Can you
determineif 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


Re: RES: pg_dump slow

From
Richard Huxton
Date:
Franklin Haut wrote:
> 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

There have been reports of very slow network performance on Win2k
systems with the default configuration. You'll have to check the
archives for details I'm afraid. This might apply to you.

If you're happy that doesn't affect you then I'd look at the disk system
- perhaps XP has newer drivers than Win2k.

What do the MS performance-charts show is happening? Specifically, CPU
and disk I/O.

--
   Richard Huxton
   Archonet Ltd

RES: pg_dump slow

From
"Franklin Haut"
Date:
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


Re: RES: pg_dump slow

From
Ron
Date:
At 12:27 PM 11/30/2005, Richard Huxton wrote:
>Franklin Haut wrote:
>>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

Get the RAM up to at least 4096MB= 4GB for a DB server.  4 1GB DIMMs
or 2 2GB DIMMS are ~ the same $$ as a HD (~$250-$300 US) and well
worth the expense.

>>    HD 120GB SATA
"b" is "bit".  "B" is "Byte".  I made the correction.

You have =1= HD? and you are using it for everything: OS, pq, swap, etc?
Very Bad Idea.

At the very least, a DB server should have the OS on separate
spindles from pg, and pg tables should be on something like a 4 HD
RAID 10.  At the very least.

DB servers are about HDs.  Lots and lots of HDs compared to anything
outside the DB realm.  Start thinking in terms of at least 6+ HD's
attached to the system in question (I've worked on system with
literally 100's).  Usually only a few of these are directly attached
to the DB server and most are attached by LAN or FC.  But the point
remains:  DBs and DB servers eat HDs in prodigious quantities.


>There have been reports of very slow network performance on Win2k
>systems with the default configuration. You'll have to check the
>archives for details I'm afraid. This might apply to you.
Unless you are doing IO across a network, this issue will not apply to you.

By default W2K systems often had a default TCP/IP packet size of 576B
and a tiny RWIN.  Optimal for analog modems talking over noisy POTS
lines, but horrible for everything else

Packet size needs to be boosted to 1500B, the maximum.  RWIN should
be boosted to _at least_ the largest number <= 2^16 that you can use
without TCP scaling.  Benchmark network IO rates.  Then TCP scaling
should be turned on and RWIN doubled and network IO benched
again.  Repeat until there is no performance benefit to doubling RWIN
or you run out of RAM that you can afford to toss at the problem or
you hit the max for RWIN (very doubtful).



>If you're happy that doesn't affect you then I'd look at the disk
>system - perhaps XP has newer drivers than Win2k.
I'll reiterate: Do _not_ run a production DB server on W2K.  M$ has
obsoleted the platform and that it is not supported _nor_ any of
reliable, secure, etc. etc.

A W2K based DB server, particularly one with a connection to the
Internet, is a ticking time bomb at this point.
Get off W2K as a production platform ASAP.  Take to your
CEO/Dean/whatever you call your Fearless Leader if you have to.

Economically and probably performance wise, it's best to use an Open
Source OS like Linux or *BSD.  However, if you must use M$, at least
use OS's that M$ is actively supporting.

Despite M$ marketing propaganda and a post in this thread to the
contrary, you =CAN= often run a production DB server under WinXP and
not pay M$ their usurious licensing fees for W2003 Server or any of
their other products with "server" in the title.  How much RAM and
how many CPUs you want in your DB server is the main issue.  For a
1P, <= 4GB RAM vanilla box, WinXp will work just fine.


>What do the MS performance-charts show is happening? Specifically,
>CPU and disk I/O.
His original post said ~3% CPU under W2K and ~70% CPU under WinXP

Ron