Обсуждение: scalability issues on win32
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
> 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)
Could you please add information about...
- filesystems ?
- windows configured as "network server" or as "desktop box" ?
- virtual memory
In my experience you MUST deactivate virtual memory on a Windows box to
avoid catastrophic competition between virtual memory and disk cache
- respective pgsql configurations (buffers...) identical ?
- explain analyze for the two, identical ?
- client on same machine or via network (100Mb ? 1G ?)
- size of the data set involved in query
- first query time after boot (with nothing in the cache), and times for
the next disk-cached runs ?
- are the N users doing the same query or exercising different parts of
the dataset ?
You don't do any writes in your test do you ? Just big SELECTs ?
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
Merlin Moncure schrieb: > 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). I always knew that COBOL ultimativly looses, but it's always refreshing to get confirmation from time to time :) > 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% Where did you get the win32 "avg cpu load" number from? AFAIK there's no getloadavg() for windows. At least I tried hard to find one, because I want to add a comparable figure to cygwin core. emacs, coreutils, make and others would need desperately need it, not to speak of servers and real-time apps. Did you read it from taskman, or did you come up with your self-written solution? In taskman there's afaik no comparable figure. But there should be some perfmon api, which would do the trick. Overview: http://www.wilsonmar.com/1perfmon.htm#TaskManager "The load average (LA) is the average number of processes (the sum of the run queue length and the number of jobs currently running) that are ready to run, but are waiting for access to a busy CPU." And thanks for the overview! -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/