Thread: Re: RES: pg_dump slow

Re: RES: pg_dump slow

From
"Merlin Moncure"
Date:
> 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

wrong. default MTU for windows 2000 server is 1500, as was NT4.
http://support.microsoft.com/?id=140375

However tweaking rwin is certainly something to look at.

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

wrong again. WIN2k gets free security hotfixes and paid support until
2010.
http://www.microsoft.com/windows2000/support/lifecycle/

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

wrong again!!  There is every reason to believe win2k is *more* secure
than win2003 sever because it is a more stable platform.  This of course
depends on what other services are running, firewall issues, etc etc.

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

I encourage use of open source software.  However encouraging other
people to spontaneously switch hardware/software platforms (especially
when they just stated when win2k is a requirement) is just or at least
not helpful.

> 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

you are on a roll here.  You must not be aware of 10 connection limit
for win2k pro and winxp pro.

http://winhlp.com/WxConnectionLimit.htm

There are hackerish ways of getting around this which are illegal.
Cheating to get around this by pooling connections via tcp proxy for
example is also against EULA (and, in my opinion, unethical).

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

Now, who is guilty of propaganda here?  Also, your comments regarding
hard disks while correct in the general sense are not helpful.  This is
clearly not a disk bandwidth problem.

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

Slow performance in extraction of bytea column strongly suggests tcp/ip.
issue.  I bet if you blanked out bytea column pg_dump will be fast.

Franlin: are you making pg_dump from local or remote box and is this a
clean install?  Try fresh patched win2k install and see what happens.

Merlin

Re: RES: pg_dump slow

From
Ron
Date:
At 05:13 PM 11/30/2005, Merlin Moncure wrote:
> > 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
>
>wrong. default MTU for windows 2000 server is 1500, as was NT4.
>http://support.microsoft.com/?id=140375
>
LOL.  Good to see it is now.  I got bit by the problem when it wasn't.


>However tweaking rwin is certainly something to look at.
>
> > >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.
>
>wrong again. WIN2k gets free security hotfixes and paid support until
>2010.
>http://www.microsoft.com/windows2000/support/lifecycle/
>

I've _lived_ what I'm talking about.  I've built some of the largest
M$ installations in existence at the time of their deployment.

Type of Support Availability
Mainstream
    * Paid-per-incident support
    * Free hotfix support
June 30, 2005
Extended
    * Hourly support
    * Paid hotfix support
June 30, 2010
Security hotfixes Free to all customers through March 31, 2010

 From your own source.  And good luck getting M$ to give you free
support for anything except what _They_ consider to be Their Fault
without paying them a boatload of $$$$.  The standard M$ party line
at this point is "Upgrade, Upgrade, Upgrade.  ...Or pay us so much
$$$$ to do it that we feel it makes economic sense for M$ to Play Ball".


> > 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.
>
>wrong again!!  There is every reason to believe win2k is *more* secure
>than win2003 sever because it is a more stable platform.  This of course
>depends on what other services are running, firewall issues, etc etc.
You evidently do not have a very serious background in network or
systems security or professional experience would tell you that your
above sentence is dangerously misguided.

Reality is that platforms stay marginally secure _only_ by constant
patching of newly discovered exploits and never ceasing vigilance
looking for new exploits to patch.  Regardless of platform.

Obsoleted platforms are at greater risk because the White Hats are no
longer paying as much attention to them and the Black Hats are
basically opportunistic parasites.  They play with anything and
everything they can get their hands on in the hopes of finding
exploitable security flaws.


> >> 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.
>
>I encourage use of open source software.  However encouraging other
>people to spontaneously switch hardware/software platforms (especially
>when they just stated when win2k is a requirement) is just or at least
>not helpful.
>
> > 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
>
>you are on a roll here.  You must not be aware of 10 connection limit
>for win2k pro and winxp pro.
>
>http://winhlp.com/WxConnectionLimit.htm
I'm excruciatingly aware of the 10 connection limit AND how stupid it
is.  It's one of the reasons, along with what M$ thought they could
get away with charging to increase it, M$ got thrown out of my server rooms.

Also, we are talking about a _DB_ server.  Not a web server or some
other function that deals with relatively light load per
connection.  Just how many open active DB connections do want active
concurrently?  Not Many.  Once all the HW is being utilized to full
capacity, DBs get best performance by being asked to do as little as
possible concurrently beyond that.

Long before you will want more than 10 active open DB connections
banging on modest HW you are going to want a queuing system in front
of the DB in order to smooth behavior.    By the time you _really_
need to support lots of open active DB connections, you will in a
position to spend more money (and probably will have to on both
better HW and better SW).


>There are hackerish ways of getting around this which are illegal.
>Cheating to get around this by pooling connections via tcp proxy for
>example is also against EULA (and, in my opinion, unethical).
I'm sorry you evidently feel your income stream is threatened, but
there is no way that is either immoral or illegal for anyone to use
the industry standard layered architecture of having a DB connection
layer separate from a Queuing system.   M$MQ is provided
_specifically_ for that use.

Certainly "twiddling the bits" inside a M$ OS violates the EULA, and
I'm not advocating anything of the kind.

OTOH, that Draconian EULA is yet _another_ reason to get rid of M$
OS's in one's organization.  When I buy something, it is _mine_.  You
can tell me you won't support it if I modify it, but it's the height
of Hubris to tell me that I'm not allowed to modify SW I paid for and
own.  Tell your managers/employers at M$ that Customer Service and
Respecting Customers =keeps= customers.  The reverse loses them.  Period.


> > 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.
>
>Now, who is guilty of propaganda here?

There is no propaganda here.  The statement is accurate in terms of
the information given.  The biggest differentiations among M$
licenses is the CPU and RAM limit.


>Also, your comments regarding hard disks while correct in the
>general sense are not helpful.  This is clearly not a disk bandwidth problem.
As Evidenced By?  His IO numbers are p*ss poor for any reasonable
RAID setup, and 375KBps is bad even for a single HD.  He's claiming
this is local IO, not network, so that possibility is out.  If you
feel this is "clearly not a disk bandwidth problem", I fail to see
your evidence or your alternative hypothesis.


> > >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
>
>Slow performance in extraction of bytea column strongly suggests tcp/ip.
>issue.  I bet if you blanked out bytea column pg_dump will be fast.
>
>Franlin: are you making pg_dump from local or remote box and is this a
>clean install?  Try fresh patched win2k install and see what happens.
He claimed this was local, not network.  It is certainly an
intriguing possibility that W2K and WinXP handle bytea
differently.  I'm not competent to comment on that however.

Ron



pg_dump slow

From
"Franklin Haut"
Date:
I Maked a new install on machine this night, and the same results, on
console localhost

Windows 2000 Server
Version 5.00.2195

PG Version 8.1


Franklin



>Franlin: are you making pg_dump from local or remote box and is this a
>clean install?  Try fresh patched win2k install and see what happens.
He claimed this was local, not network.  It is certainly an
intriguing possibility that W2K and WinXP handle bytea
differently.  I'm not competent to comment on that however.