Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
Bruce Momjian <[EMAIL PROTECTED]> writes: > Merlin Moncure wrote: >> Ok, I am starting to strongly suspect the statistics collector of >> various kinds of malfeasance. > OK, the big problem is that we are nearing RC1. We would like some > feedback on this as soon as possible. A major Win32 cleanup for this > could delay the 8.0 release. I would say that it shouldn't delay the release --- worst case, we say "the collector doesn't work very well under Win32 yet". It's probably not the only part of the system we'll find needs work under Win32. This is moot if Merlin can find some simple fixable bug, but I'm worried that doing anything significant might require major work. BTW, what about the issue we just identified with piperead() failing to set errno on Windows? That would certainly account for the "random collector restarts" complaint ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
Merlin Moncure wrote: > > > 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. > > > > That's a bit harsh given the lack of any further investigation so far > > isn't it? Win32 can run perfectly well with other DBMSs with hundreds > of > > users. > > > > Any chance you can profile your test runs Merlin? > > > > Ok, I am starting to strongly suspect the statistics collector of > various kinds of malfeasance. Right now I am running with the stats > collector off and I am getting much better scalability and much more > deterministic query times...that is, even when under moderate to heavy > load query running times are proportional to the number of users on the > system...the system is purring like a kitten right now with over a 100 > users logged in. > > This coupled with the fact that I was getting random restarts with the > collector process makes me think that there is some kind of issue with > the ipc between the collector and the backends that is blocking and/or > is being improperly handled after failure. > > I was running with statement level stats on under scenarios with > extremely high levels of query activity where the server might be > processing 500-1500 queries/second or more spread out over multiple > backends. > > I'll look into this issue more over next several days. I'll dip back > into the code and see if I can come up with a better > answer...unfortunately I can't run the stats collector on until I can > schedule another load test. OK, the big problem is that we are nearing RC1. We would like some feedback on this as soon as possible. A major Win32 cleanup for this could delay the 8.0 release. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
> > 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. > > That's a bit harsh given the lack of any further investigation so far > isn't it? Win32 can run perfectly well with other DBMSs with hundreds of > users. > > Any chance you can profile your test runs Merlin? > Ok, I am starting to strongly suspect the statistics collector of various kinds of malfeasance. Right now I am running with the stats collector off and I am getting much better scalability and much more deterministic query times...that is, even when under moderate to heavy load query running times are proportional to the number of users on the system...the system is purring like a kitten right now with over a 100 users logged in. This coupled with the fact that I was getting random restarts with the collector process makes me think that there is some kind of issue with the ipc between the collector and the backends that is blocking and/or is being improperly handled after failure. I was running with statement level stats on under scenarios with extremely high levels of query activity where the server might be processing 500-1500 queries/second or more spread out over multiple backends. I'll look into this issue more over next several days. I'll dip back into the code and see if I can come up with a better answer...unfortunately I can't run the stats collector on until I can schedule another load test. Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > Of Bruce Momjian > Sent: 23 November 2004 02:26 > To: Merlin Moncure > Cc: [EMAIL PROTECTED]; PostgreSQL Win32 port list > Subject: Re: [pgsql-hackers-win32] scalability issues on win32 > > > 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. That's a bit harsh given the lack of any further investigation so far isn't it? Win32 can run perfectly well with other DBMSs with hundreds of users. Any chance you can profile your test runs Merlin? Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: 23 November 2004 15:06 > To: Dave Page > Cc: Merlin Moncure; [EMAIL PROTECTED]; > PostgreSQL Win32 port list > Subject: Re: [pgsql-hackers-win32] scalability issues on win32 > > The general opinion of server users is that you need 2-4 more > Win32 servers to do the same work as one Unix-like server. > That and the difficulty of automated administration and > security problems is what is preventing Win32 from making > greater inroads into the server marketplace. > > Of course these are just generalizations. I'd rather avoid an OS advocacy war here, but if I'm honest, with group policy and other tools such as SUS, I find that my Windows servers are actually easier to administer than the Linux ones (I have about a 50-50 mix at work). Perhaps that's because I favour Slackware though? As for the 2-4 servers quote, I find that a little on the high side. I agree that generally you might expect a little more performance from an equivalent Linux system on the same hardware, but in my practical experience the difference is far less than you suggest. Regards, Dave. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
Dave Page wrote: > > > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > Sent: 23 November 2004 15:06 > > To: Dave Page > > Cc: Merlin Moncure; [EMAIL PROTECTED]; > > PostgreSQL Win32 port list > > Subject: Re: [pgsql-hackers-win32] scalability issues on win32 > > > > The general opinion of server users is that you need 2-4 more > > Win32 servers to do the same work as one Unix-like server. > > That and the difficulty of automated administration and > > security problems is what is preventing Win32 from making > > greater inroads into the server marketplace. > > > > Of course these are just generalizations. > > I'd rather avoid an OS advocacy war here, but if I'm honest, with group > policy and other tools such as SUS, I find that my Windows servers are > actually easier to administer than the Linux ones (I have about a 50-50 > mix at work). Perhaps that's because I favour Slackware though? > > As for the 2-4 servers quote, I find that a little on the high side. I > agree that generally you might expect a little more performance from an > equivalent Linux system on the same hardware, but in my practical > experience the difference is far less than you suggest. I have never run the tests myself. I am just quoting what I have heard, and maybe that information is a few years old. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
> Is this for Postgresql Cygwin? You surely can't mean "for all server > tasks" - if so, I would say that's *way* off. There is a difference, but > it's more along the line of single-digit percentage in my experience - > provided you config your machines reasonably, of course. > > (In my experience, Win32 MSSQLServer often outperforms postgresql on > Linux. Granted you can tweak postgresql up to higher speeds, but MS does > most of that tweaking automatically... Talking of tweaking a lot more > specific than just raising the memory limits from the installation > default, of course) I agree with Magnus. Specifically, I suspect there is some sort of resource contention going on that is driving up the cpu load when the queries follow certain patterns. This resource contention could be happening in the win32 port code (likely ipc), the mingw api, or inside the o/s itself. Other servers, namely apache, sql server and a host of others do not have this problem. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
> > > 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. > > > > That's a bit harsh given the lack of any further > investigation so far > > isn't it? Win32 can run perfectly well with other DBMSs > with hundreds > > of users. > > The general opinion of server users is that you need 2-4 more > Win32 servers to do the same work as one Unix-like server. > That and the difficulty of automated administration and > security problems is what is preventing Win32 from making > greater inroads into the server marketplace. > > Of course these are just generalizations. Is this for Postgresql Cygwin? You surely can't mean "for all server tasks" - if so, I would say that's *way* off. There is a difference, but it's more along the line of single-digit percentage in my experience - provided you config your machines reasonably, of course. (In my experience, Win32 MSSQLServer often outperforms postgresql on Linux. Granted you can tweak postgresql up to higher speeds, but MS does most of that tweaking automatically... Talking of tweaking a lot more specific than just raising the memory limits from the installation default, of course) I do agree on the automated administration though... It's a major PITA. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
Dave Page wrote: > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf > > Of Bruce Momjian > > Sent: 23 November 2004 02:26 > > To: Merlin Moncure > > Cc: [EMAIL PROTECTED]; PostgreSQL Win32 port list > > Subject: Re: [pgsql-hackers-win32] scalability issues on win32 > > > > > > 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. > > That's a bit harsh given the lack of any further investigation so far > isn't it? Win32 can run perfectly well with other DBMSs with hundreds of > users. The general opinion of server users is that you need 2-4 more Win32 servers to do the same work as one Unix-like server. That and the difficulty of automated administration and security problems is what is preventing Win32 from making greater inroads into the server marketplace. Of course these are just generalizations. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
Reini Urban wrote: > Merlin Moncure schrieb: > > 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 :) Heh. It's important to make the distinction between COBOL, which is just a language, and ISAM, which is a data delivery system. You could, for example, pair COBOL with SQL with good results, (in fact, we plan to). But yes, many legacy COBOL apps were written with assumptions about the system architecture that are no longer valid. > 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. I just eyeballed it :-). So consider the load averages anecdotal, although they are quite stable. However it is quite striking that with the same application code the win32 load average was 2-3 times higher. I also left out the dual processor results, because I did not have time to test them on linux. However, sadly the 2nd processor adds very little extras horsepower to the server. I'm hoping linux will be better. Merlin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32
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 [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
