scalability issues on win32 - Mailing list pgsql-performance

Following is the promised writeup in performance related issues
comparing win32 with linux x86 and linux x86-64.  Unfortunately, the 64
bit portion of the test is not yet completed and won't be for a bit.
However there are some telling things about the win32/linux comparison.
If you are considering deploying postgres in a win32 environment read
on...

First a description of the client app:
Our company develops an ERP/CRM written in cobol which we are porting to
run on PostgreSQL.  Part of this porting effort was development of an
ISAM 'driver' for our app to allow it to store/fetch data from the
database in place of a traditional file system, which is complete.

For those of you unfamiliar with COBOL/ISAM, applications written with
it have a 'one record at a time' mentality, such the application tends
to spam the server with queries of the select * from t where k = k1
variety.  Our driver creates stored procedures as necessary and uses
ExecParams wherever possible to cut down on server CPU time, which is a
precious resource in our case.  Over time we plan to gradually redeploy
our application logic to make better use of the sql server's server side
power.  Our application is very rarely i/o bound because we will make
sure our server has enough memory so that the data will be rarely, if
ever, *not* run from the cache.

A good benchmark of our application performance is the time it takes to
read the entire bill of materials for a product.  This is a recursive
read of about 2500 records in the typical case (2408 in the test case).

Test platform:
Pentium 4 3.06 GHz/HT
10k SATA Raptor
1Gb memory
Windows XP Pro SP2/Redhat Fedora 3 (64 bit results coming soon)

BOM traversal for product ***** (1 user):
win32: runtime: 3.34 sec  avg cpu load: 60%
redhat: runtime: 3.46 sec  avg cpu load: 20%

Well, win32 wins this test.  There is some variability in the results
meaning for a single user scenario there is basically no difference
between win32 and linux in execution time.  However the cpu load is much
lower for linux which spells problems for win32 with multiple users:

BOM traversal for product ***** (6 users):
win32: runtime (each): 7.29 sec  avg cpu load: 100%
redhat: runtime (each): 4.56 sec  avg cpu load: 90%

Here, the win32 problems with cpu load start to manifest.  The cpu meter
stays pegged at 100% while the redhat hand around 90%.  The difference
in times is telling.

The third and final test is what I call the query 'surprise' factor, IOW
surprise! your query takes forever!  The test involves a combination of
the previous test with a query with a couple of joins that returns about
15k records.  On both redhat/win32, the query takes about .35 seconds to
execute on a unloaded server...remember that figure.



Item List generation while 6 clients generating BOM for multiple
products:
Redhat: 2.5 seconds
Win32: 155 seconds (!)

Here the win32 server is showing real problems.  Also, the query
execution time is really variable, in some cases not returning until the
6 workhorse processes completed their tasks.  The linux server by
contrast ran slower but never ran over 4 seconds after multiple runs.

Also, on the purely subjective side, the linux server 'feels' faster and
considerably more responsive under load, even under much higher load.

Comments/Suggestions?

Merlin