[PostgreSQL] Simple query takes a long time on win2K

Hmm - very strange. It couldn't be network related could it? IIRC on
Windows machines you connect via localhost (because there aren't any
unix domain sockets). There have been reports of different performance
over network connections, but I don't know if this applies to local
connections or if it's as serious as this.

None of these tests were run over a network - all local. Given that the
hardware is very different, however, I did find it strange that all win2k
(Pro, not server) served up the records in almost exactly the same time,
while the 2.4 celeron is 80ms! In terms of processor speed, that difference
in time is not explained between the 2.4GHz and 1.8GHz machines. Perhaps it
is a memory issue since the 1.8GHz box does have only 256, but I've been
running Postgres on these machines for some time now and I don't recall this
sort of latency with earlier versions.

I'll install an earlier version of postgres and do a little test.

Stay tuned...


-----Original Message-----
From: Richard Huxton
Sent: March 23, 2005 4:29 AM
To: A. Mous
Cc: [email protected]
Subject: Re: [GENERAL] Simple query takes a long time on win2K

A. Mous wrote:
Hmm - very strange. It couldn't be network related could it? IIRC on
Windows machines you connect via localhost (because there aren't any
unix domain sockets). There have been reports of different performance
over network connections, but I don't know if this applies to local
connections or if it's as serious as this.

Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.

Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.

I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.

regards, tom lane

Yeah, thanks. I did see that post about the QoS and it doesn't help in this
case.

-----Original Message-----
From: Tom Lane
Sent: March 23, 2005 9:51 AM
To: A. Mous
Cc: 'Richard Huxton'; [email protected]
Subject: Re: [GENERAL] Simple query takes a long time on win2K

"A. Mous" <[email protected]> writes:
Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.

Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.

I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.

regards, tom lane

For the sake of further comparison, I added another computer to the
comparison. Another Celeron 2400, running win XP again, but only 256MB ram.
All records returned to client in 200ms (slightly longer than the 2400
machine with 500MB ram). Recall that the win2K box with 256MB Ram (PIII
1.8) took 4 seconds.

The confusing thing for me is that so far, the only consistent pattern here
is that machines running win2k Pro take roughly 4 seconds to deliver the
data to the client, while win XP machines perform much better (<200ms to
deliver recordset). I've tried installing QoS packet scheduler on win2K pro
machines to no avail.

For kicks, I'll repeat the tests on the PII 233 machine with WinXP
installed. Thank you Tom for pointing out that the EXPLAIN result shows the
data is being fetched in under 100ms, but why is it taking so darn long to
move that data into the client (be it psql or pgAdminIII)?

I'll let you all know how the winXP test on the PII goes. Thank you all for
your insights.





-----Original Message-----
From: Tom Lane
Sent: March 23, 2005 9:51 AM
To: A. Mous
Cc: 'Richard Huxton'; [email protected]
Subject: Re: [GENERAL] Simple query takes a long time on win2K

"A. Mous" <[email protected]> writes:
Nonetheless, the client-to-server communication goes through the Windows
TCP stack, because that's the only comm protocol we support on Windows.

Notice that your EXPLAIN ANALYZEs show the query as executing in less
than 100ms even on the slower machine --- so it seems the bottleneck has
to be in sending the results to the client.

I seem to recall threads on the win32-hackers list to the effect that
local TCP performance really sucks on Win2K unless you have the right
patch installed ("QoS" comes to mind, but I didn't pay attention to
details). We probably need to get that info into the FAQ.

regards, tom lane

I really thought the QoS thing would be it. Maybe there was some other
thing to do, configuration-wise, to make that do its thing properly?

If you could reproduce this behavior across the network I would tell you
to get out a packet analyzer (ethereal or some such) and sniff the
traffic to learn more. Are there any programs that can sniff local TCP
traffic on win2k?

regards, tom lane

PS: I've added pgsql-hackers-win32 to the cc list.

A Celeron 2400 is obviously much faster than any of these machines, no?
COmpared to the 200Mhz, you have 12 times the processor power.

This is fram frmo the difference you're seeing, though. But it might be
something like the server being able to complete a lot more work in a
single timeslice and thus decreasing context switching between
processes.

Is this Windows 2000 Server or Workstation? The difference in timeslice
lengths could make a difference here.

Do you see similar differences if you run it across the network?

//Magnus

You're right, the Celeron 2400 is much faster than the 200, but not that
much more than the 1800, and all win2k (professional) machines are serving
up the records in exactly the same amount of time.

Across a network (issuing the query from the 2400 celeron win XP to the 233
PII win2k) the records are served up in about 300ms! Much faster than
simply performing the exact same query locally on the PII.

Now I'm really confused!

Any ideas?



-----Original Message-----
From: Magnus Hagander
Sent: March 23, 2005 4:43 AM
To: A. Mous; [email protected]
Subject: RE: [GENERAL] Simple query takes a long time on win2K
A Celeron 2400 is obviously much faster than any of these machines, no?
COmpared to the 200Mhz, you have 12 times the processor power.

This is fram frmo the difference you're seeing, though. But it might be
something like the server being able to complete a lot more work in a
single timeslice and thus decreasing context switching between
processes.

Is this Windows 2000 Server or Workstation? The difference in timeslice
lengths could make a difference here.

Do you see similar differences if you run it across the network?

//Magnus

They are quite different hardware.

How long does it take for the _first_ time you do the query on the Celeron
machine? The first time. Wait until everything has started up first and the
machine is quiescent.

How long does it take for the _second_ and _third_ times?

Do the same for all the machines.

Are the drives on the machine very different?

How about you analyze the disks on each machine and compare how fragmented
the database files are on the various machines?

128MB RAM is not very much for a Win2K machine. Not very far from swapping.

Win2K pro or Win2K server? Performance optimized for server or
desktop/applications?

Regards,
Link.

Just for reference. The reason we want to know about subsequent runs
is that things will be cached.
This is where I am leaning without any further information because
the older machine (in theory) are going to have slower drives. If the
celeron has a 7200 rpm machine and the others have 5400 rpm drives...
Depending on what you are doing, you may already be swapping.

It would be good to also see an explain anaylze

Sincerely,

Joshua D. Drake

Yes, it is quite a range of hardware, and even within the win2k pro machines
there is quite a range of hardware which is why I would expect all of them
to produce slightly different latency times to serve up the records. Yet,
all are serving in 4 seconds! If I saw a pattern that suggested that the
slowest, most inept machine produced the slowest results I'd be satisfied
that it was strictly hardware, but I'm not seeing that. The slowest machine
serves the records up in the same time as the second fastest machine!

The hardware between all of these machines is as widely varied as you can
get.

The drive on the Celeron 400 was just defragmented and it made no difference
at all.

Fresh reboot on Celeron 2400: 1st query took 561ms, 2nd and thereafter
takes 70ms.

Fresh reboot on PII 233: 1st query took 4300ms, 2nd and thereafter took
4000ms.




-----Original Message-----
From: Lincoln Yeoh
Sent: March 23, 2005 7:51 AM
To: A. Mous; [email protected]
Subject: Re: [GENERAL] Simple query takes a long time on win2K

They are quite different hardware.

How long does it take for the _first_ time you do the query on the Celeron
machine? The first time. Wait until everything has started up first and the
machine is quiescent.

How long does it take for the _second_ and _third_ times?

Do the same for all the machines.

Are the drives on the machine very different?

How about you analyze the disks on each machine and compare how fragmented
the database files are on the various machines?

128MB RAM is not very much for a Win2K machine. Not very far from swapping.

Win2K pro or Win2K server? Performance optimized for server or
desktop/applications?

Regards,
Link.

Yes, I suspect it has to do with context switching. When you run it
remotely the server can do it's job without much interference. When you
run the client on the same machine, it keeps flipping back and forth
between the server and the client.

You might get better performance on Win2k server, as that would increase
the timeslice for each piece of work. Also, try tweaking the
"Performance boost for foreground application" on the performance tab of
the system properties.

//Magnus

So, does this lend evidence to the theory that the difference is due to
insufficient RAM in all of the win2K pro machines?


-----Original Message-----
From: Magnus Hagander
Sent: March 23, 2005 9:29 AM
To: A. Mous; [email protected]
Subject: RE: [GENERAL] Simple query takes a long time on win2K
Yes, I suspect it has to do with context switching. When you run it
remotely the server can do it's job without much interference. When you
run the client on the same machine, it keeps flipping back and forth
between the server and the client.

You might get better performance on Win2k server, as that would increase
the timeslice for each piece of work. Also, try tweaking the
"Performance boost for foreground application" on the performance tab of
the system properties.

//Magnus

Note that the granularity of the performance counters in EXPLAIN and in
psql have a very bad resolution on Win32 in 8.0.1. The EXPLAIN counters
has been fixed for 8.1, not sure if it'll be backpatched to 8.0.2.

What are you getting your time values from? If it's based on either of
these it's entirely possible that the speed difference is completely
inside the margin of error. Also, you are not likely to be very
CPU-bound for a simple query like that.

//Magnus

Queries are issued from, and time values are report in pgAdminIII.


-----Original Message-----
From: Magnus Hagander
Sent: March 23, 2005 9:31 AM
To: A. Mous; Richard Huxton
Cc: [email protected]
Subject: RE: [GENERAL] Simple query takes a long time on win2K
Note that the granularity of the performance counters in EXPLAIN and in
psql have a very bad resolution on Win32 in 8.0.1. The EXPLAIN counters
has been fixed for 8.1, not sure if it'll be backpatched to 8.0.2.

What are you getting your time values from? If it's based on either of
these it's entirely possible that the speed difference is completely
inside the margin of error. Also, you are not likely to be very
CPU-bound for a simple query like that.

//Magnus

Microsoft ships a network monitor with Win2k Server. Otherwise, just get
ethereal, it works perfectly on win2k.

//Magnus

OK, I've taken the PII 233 that had win2K pro on it, and installed winXP
home. Reloaded psql 8.0.1 and the database and ran the exact same query.
Recall with win2K it took 4000ms to get the data to the client (about 1500
rows) and on XP it takes 290ms the first time, and about 250ms every time
thereafter.

Results of EXPLAIN ANALYZE SELECT * FROM foo are:

Seq Scan on foo (cost=0.00..40.72 rows=1472 width=134) (actual
time=0.000..50.000 rows=1472 loops=1)

By the way, this table only has 7 columns...why is the width described as
134?

I've tried installing the QoS packet scheduler with win2K pro and it did
absolutely nothing. I will now download ethereal and install on one of the
other win2K pro machines that has high latency, however, what exactly am I
looking for?

You know, among the win2K machines that I've tested so far, if the only slow
ones were the pII 233 and the Celeron 400 I could accept the fact that it's
ancient hardware. But the pIII800 was also dead slow to return the data to
the client app, and this most recent test clearly shows that the OS is the
problem. Ug.

I'd greatly appreciate any input on this! Thanks.



-----Original Message-----
From: Magnus Hagander
Sent: March 23, 2005 12:04 PM
To: Tom Lane; A. Mous
Cc: Richard Huxton; [email protected]; Joshua D. Drake; Lincoln
Yeoh; [email protected]
Subject: RE: [GENERAL] Simple query takes a long time on win2K
Microsoft ships a network monitor with Win2k Server. Otherwise, just get
ethereal, it works perfectly on win2k.

//Magnus

Thank you all for your input, and thanks about the tip on ethereal - cool
program. Here's what I've found out from today's testing:

The total size of the recordset that is being served up is about 500kb.
When serving across a network, the time to deliver the records to the client
is largely dependent on the hardware doing the serving (as expected) and
regardless of which OS (Win 2K pro or XP pro). The thing that really threw
me off, and still does is when the same data is served up locally. In this
case, all test win2K machines (Celeron 400 up to pIII 800) retrieved the
data from disk in under 100ms but took an additional 4000ms to send to the
local client. This is observed even if QoS packet scheduler is installed.
By contrast, Win XP serves up locally nice and fast, even when installed on
exactly the same hardware that was used for the win2K test.

Thanks to all for your help and insight on this one, although I'm still
puzzled by the behaviour on the win2K boxes serving locally.

Cheers.





-----Original Message-----
From: Magnus Hagander
Sent: March 23, 2005 12:04 PM
To: Tom Lane; A. Mous
Cc: Richard Huxton; [email protected]; Joshua D. Drake; Lincoln
Yeoh; [email protected]
Subject: RE: [GENERAL] Simple query takes a long time on win2K
Microsoft ships a network monitor with Win2k Server. Otherwise, just get
ethereal, it works perfectly on win2k.

//Magnus

If you connect to the local IP (i.e. not 127.0.0.1 but some other
interface), does the same thing happen? (This would tell you whether
the problem lies in some sort of special problem routing localhost,
or whether it's something else.)

A