Re: RES: pg_dump slow

Поиск
Список
Период
Сортировка
От Ron
Тема Re: RES: pg_dump slow
Дата
Msg-id 6.2.5.6.0.20051130152830.01d9e178@earthlink.net
обсуждение исходный текст
Ответ на Re: RES: pg_dump slow  (Richard Huxton <dev@archonet.com>)
Список pgsql-performance
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



В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Select with grouping plan question
Следующее
От: "Merlin Moncure"
Дата:
Сообщение: Re: RES: pg_dump slow