Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32

2004-12-02 Thread Merlin Moncure
  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

2004-12-02 Thread Bruce Momjian
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

2004-12-02 Thread Tom Lane
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

2004-11-24 Thread Reini Urban
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

2004-11-24 Thread Dave Page
 

 -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

2004-11-23 Thread Merlin Moncure
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

2004-11-23 Thread Bruce Momjian
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

2004-11-23 Thread Magnus Hagander
   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

2004-11-23 Thread Merlin Moncure
 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

2004-11-23 Thread Bruce Momjian
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

2004-11-22 Thread Bruce Momjian

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