Skip site navigation (1) Skip section navigation (2)

Re: RES: pg_dump slow

From: Ron <rjpeace(at)earthlink(dot)net>
To: Richard Huxton <dev(at)archonet(dot)com>, Franklin Haut <franklin(dot)haut(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: RES: pg_dump slow
Date: 2005-11-30 21:05:38
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
At 12:27 PM 11/30/2005, Richard Huxton wrote:
>Franklin Haut wrote:
>>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


In response to

pgsql-performance by date

Next:From: Franklin HautDate: 2005-11-30 21:36:42
Subject: RES: pg_dump slow
Previous:From: Tom LaneDate: 2005-11-30 20:41:22
Subject: Re: Select with grouping plan question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group