Re: [pgsql-hackers-win32] scalability issues on win32 - Mailing list pgsql-performance

This was an intersting Win32/linux comparison. I expected Linux to scale
better, but I was surprised how poorly XP scaled.  It reinforces our
perception that Win32 is for low traffic servers.

---------------------------------------------------------------------------

Merlin Moncure wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073