Thread

1. Simmultanous Connections (fwd)

   Vince Vielhaber <vev@michvhf.com> — 2000-01-10T16:09:06Z

   Can anyone here help?
   
   Vince.
   
   ---------- Forwarded message ----------
   Date: Mon, 10 Jan 2000 08:52:06 +0000
   From: Jude Weaver <exec@shreve.net>
   To: webmaster@postgresql.org
   Subject: Simmultanous Connections
   
   We are a company that writes academic software . We are converting our
   software to use  either PostgreSQL or MySQL. We are leaning toward
   PostgreSQL, but, I still have several questions.
   I hope someone can answer these for me.
   
   1.  I have read the Q&A for postgreSQL and would like to know the
   difference between a temporary
        and a permanant connection. Do you have a connection when you open
   the database or only when
         the frontend sends a job to the backend? If 32 people are running
   a module that opens a database
          is that 32 connections or will it vary as users read and write to
   the database?
   
   2. I saw in the Q&A that to run more than 32 simmultanous connects could
   be a big drain on our re-
       sources. Our  Linux boxes , in general, are Intel 166 to 500s, 128MG
   of RAM and 6.2 to 13 GIG.
       Can anyone tell me roughly how much resources per connection does
   PostgreSQL use?
   
   3. If I have 90 teachers posting grades at the same time, (the grade
   posting program opens 5 dif-
       ferent databases) and  25 secretaries and administrators poking
   around in assorted databases
        looking at information, will postgresql handle that much traffic?
   
   I would appreciate any information you can give me,
   Thank you - Jude Weaver.
   
   
   
   

2. Re: [HACKERS] Simmultanous Connections (fwd)

   Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-10T16:46:27Z

   >   Can anyone here help?
   >
   >   Vince.
   >
   >   ---------- Forwarded message ----------
   >   Date: Mon, 10 Jan 2000 08:52:06 +0000
   >   From: Jude Weaver <exec@shreve.net>
   >   To: webmaster@postgresql.org
   >   Subject: Simmultanous Connections
   >
   >   We are a company that writes academic software . We are converting our
   >   software to use  either PostgreSQL or MySQL. We are leaning toward
   >   PostgreSQL, but, I still have several questions.
   >   I hope someone can answer these for me.
   >
   >   1.  I have read the Q&A for postgreSQL and would like to know the
   >   difference between a temporary
   >	and a permanant connection. Do you have a connection when you open
   >   the database or only when
   >	 the frontend sends a job to the backend? If 32 people are running
   >   a module that opens a database
   >	  is that 32 connections or will it vary as users read and write to
   >   the database?
   
   Sounds like she may looking at postgres in PHP - at least PHP uses
   that temporary and permanant connection concept.  My experience is
   that PHP persistent connections are not worth it - the time to
   establish a new connection is pretty small, and stale connections can
   cause problems.
   
   >   2. I saw in the Q&A that to run more than 32 simmultanous connects could
   >   be a big drain on our re-
   >       sources. Our  Linux boxes , in general, are Intel 166 to 500s, 128MG
   >   of RAM and 6.2 to 13 GIG.
   >       Can anyone tell me roughly how much resources per connection does
   >   PostgreSQL use?
   
   If an idle psql connection is left open, we're looking at about 1 MB
   RAM plus 4MB swap on my linux box.
   
   As I noted above, I'd generally recommend against persistent
   connections when there are more than a few users.
   
   Sounds like the machines have the capacity for what sounds like a
   fairly small task.  Of course, there would generally be only one
   server machine, so I would recommend choosing one of the faster ones.
   But it should be stable and usable ath eith end of the spectrum, at
   least from my experience.
   
   >   3. If I have 90 teachers posting grades at the same time, (the grade
   >   posting program opens 5 dif-
   >       ferent databases) and  25 secretaries and administrators poking
   >   around in assorted databases
   >	looking at information, will postgresql handle that much traffic?
   
   Postgres should handle that easily.
   
   Just my $0.02 worth.  Hope it's helpful.
   
   -- 
   Karl DeBisschop <kdebisschop@alert.infoplease.com>
   617.832.0332 (Fax: 617.956.2696)
   
   Information Please - your source for FREE online reference
   http://www.infoplease.com  - Your Ultimate Fact Finder
   http://kids.infoplease.com - The Great Homework Helper
   
   Netsaint Plugins Development
   http://netsaintplug.sourceforge.net
   
   

3. Re: [HACKERS] Simmultanous Connections (fwd)

   Marc G. Fournier <scrappy@hub.org> — 2000-01-10T16:46:41Z

   On Mon, 10 Jan 2000, Vince Vielhaber wrote:
   
   > 1.  I have read the Q&A for postgreSQL and would like to know the
   > difference between a temporary and a permanant connection. Do you have
   > a connection when you open the database or only when the frontend
   > sends a job to the backend? If 32 people are running a module that
   > opens a database is that 32 connections or will it vary as users read
   > and write to the database?
   
   You will have 32 connections open to the backend ...
   
   > 2. I saw in the Q&A that to run more than 32 simmultanous connects
   > could be a big drain on our re- sources. Our Linux boxes , in general,
   > are Intel 166 to 500s, 128MG of RAM and 6.2 to 13 GIG.  Can anyone
   > tell me roughly how much resources per connection does PostgreSQL use?
   
   It depends on what the connections are doing...if someone is doing a
   'SELECT...ORDER BY', it will take more resources then if you are doing
   something that doesn't involve any sort routines...
   
   > 3. If I have 90 teachers posting grades at the same time, (the grade
   > posting program opens 5 dif- ferent databases) and 25 secretaries and
   > administrators poking around in assorted databases looking at
   > information, will postgresql handle that much traffic?
   
   	5 different databases, vs 5 different tables?  5 different
   databases will mean 90 x 5 (450) connections opened up...whereas 5 tables
   would be just 90 connections...
   
   	... but, either way, will it handle that much traffic?  give it
   enough RAM, and I personally don't see why not, but I've yet to hit *that*
   kind of a load on it.  Right now, I have PostgreSQL setup to handle
   several databases, and the postmaster processes each take up ~4-5Meg:
   
   hub> ps aux | grep data
   pgsql     895  0.0  0.2  4508 1416  d0- S     6:52AM   0:00.98 /home/database/v
   pgsql     896  0.0  0.2  3976 1308  d0- I     6:52AM   0:00.02 /home/database/v
   
   When I open up a session/connection to a database, I'm seeing:
   
   pgsql   71041  5.1  0.4  5028 3492  ??  R    11:40AM   0:00.54 /home/database/v
   pgsql   71032  0.0  0.4  4992 3148  ??  S    11:40AM   0:00.02 /home/database/v
   pgsql   71034  0.0  0.4  4980 2976  ??  S    11:40AM   0:00.02 /home/database/v
   
   Now, I always get this backwards/confused, but...the first value (ie. 4508) is 
   the binary size, which is mis-informed due to the use of shared libraries...
   the important one is the second value (ie. 1416), which, again, if I recall
   correctly, is the datasize...for the udmsearch database, just starting up
   'psql udmsearch', each database is taking <3.5Meg...depending on the sizes of
   your queries and whatnot, figure that I'd need 3.5Meg*450 (~1.5gig) of memory
   on this machine to handle it (I have half of that now)...bear in mind that
   not all 450 connections would be active, so there is room for some processes
   to be swap'd out and whatnot..
   
   My personal opinion is that there isn't anything that PostgreSQL hasn't been
   able to handle so far, to the best of my knowledge...my next step for my 
   system is to go dual-processor, and bring on a full gig of RAM, but my machine
   also does alot more then just PostgreSQL :)
   
   Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
   Systems Administrator @ hub.org 
   primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
   
   
   

4. Re: [HACKERS] Simmultanous Connections (fwd)

   Don Baccus <dhogaza@pacifier.com> — 2000-01-10T18:07:24Z

   At 11:46 AM 1/10/00 -0500, Karl DeBisschop wrote:
   
   >Sounds like she may looking at postgres in PHP - at least PHP uses
   >that temporary and permanant connection concept.  My experience is
   >that PHP persistent connections are not worth it - the time to
   >establish a new connection is pretty small, and stale connections can
   >cause problems.
   
   Boy, persistent connections in AOLserver sure help a lot (ask Lamar
   Owen!).  If stale connections cause problems in your PHP environment,
   then the PHP persistent connection implementation needs some work.
   
   Forking a new backend is actually considerably more expensive then
   just passing back the PID of an existing backend...
   
   On Sun Solaris systems, forking is about 25 times as costly as 
   starting up a new thread (according to data from Sun).  Of course,
   returning an existing persistent db connection's even cheaper than
   starting a new thread.  And that comparative cost will vary between
   OS.
   
   But not necessarily in a direction favoring more forking :)
   
   I sent her a private note saying she really probably shouldn't be looking
   at MySQL for her application, presumably having a real transaction-based
   db is a Good Thing when maintaining a database of student grades.  Told
   her she should be looking at various real RDBMS solutions and should leave
   MySQL out of the picture entirely (while also telling her I thought PG
   would work fine for her needs, of course).
   
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, Pacific Northwest
     Rare Bird Alert Service and other goodies at
     http://donb.photo.net.
   
   

5. Re: [HACKERS] Simmultanous Connections (fwd)

   Lamar Owen <lamar.owen@wgcr.org> — 2000-01-10T18:35:38Z

   Don Baccus wrote:
   > 
   > At 11:46 AM 1/10/00 -0500, Karl DeBisschop wrote:
   > 
   > >Sounds like she may looking at postgres in PHP - at least PHP uses
   > >that temporary and permanant connection concept.  My experience is
   > >that PHP persistent connections are not worth it - the time to
   > >establish a new connection is pretty small, and stale connections can
   > >cause problems.
    
   > Boy, persistent connections in AOLserver sure help a lot (ask Lamar
   > Owen!).  If stale connections cause problems in your PHP environment,
   > then the PHP persistent connection implementation needs some work.
   
   Let's work some math.
   
   Under AOLserver, using the pooled connection paradigm that it uses, for
   5 databases, you would need to define 5 pools.  You then can control how
   many instances of each pool can be opened at any given time.  So, if all
   databases need the same number of connections average, you raise the max
   on pool instances until users quit getting busy messages during normal
   usage -- which usually , for a small number of users (~25 here), is only
   2 or 3 instances.  
   
   The persistent pooled model avoids fork() penalties -- after all, there
   is overhead there, regardless of how small that overhead may be.
   
   I have gone as far as reducing the instances to 1 here -- it's amazing
   how few people actually do simultaneous accesses!  I currently am
   running with an instance max of 3 -- and users get busy's very rarely.
   
   With 90 users on a single database with 5 tables, an instance max of
   10-20 would probably give less than a 10% busy rate.  And, as you add
   more RAM, you can up your instance max to adjust.
   
   I don't know how close to the AOLserver model PHP is (I think it is
   pretty close, as the beta of PHP4 is buildable to run as a module under
   AOLserver), but the concept of pooled persistent connections is a sound
   one, and eliminates some grief (as long as you watch your transactions
   -- don't want two connections that happen to share a pool instance to
   share a transaction roolback!).  Plus, you can service that required
   nnumber of users at varying satisfaction/busy levels depending upon your
   current server resources.
   
   --
   Lamar Owen
   WGCR Internet Radio
   1 Peter 4:11
   
   

6. Re: [HACKERS] Simmultanous Connections (fwd)

   Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-01-10T18:38:14Z

   > Boy, persistent connections in AOLserver sure help a lot (ask Lamar
   > Owen!).  If stale connections cause problems in your PHP environment,
   > then the PHP persistent connection implementation needs some work.
   
   This isn't really a hackers issue, so I'll try to be brief but also
   give a little more info than I originally did.  Maybe any further
   discussion would be best placed in pgsql-general.
   
   Basically, I think it may depend on the use - for our website, we get
   connections from a variety of sources - most of them don't repeat for
   a long time, if ever.  Which means a bunch sit around at any given
   time, never to be reused.  If the new connections come fast enough,
   this can translate to real problems unless they timeout quickly, which
   defeats the purpose.
   
   That being said, maybe the PHP implementaion does need some work, or
   maybe there are site parameters we could tune to make it work.  But
   whenever we use it, we do eventually end up in trouble as a result.
   
   So, personally, I don't recommend it in situations where alot of
   different clients will be connecting to the DBMS - at least if low
   maintennence is a key goal.
   
   > Forking a new backend is actually considerably more expensive then
   > just passing back the PID of an existing backend...
   
   >From the point of view of the server, absolutely.  But that connection
   time is still a very small part of the user's total trransaction time.
   And, although I am making alot of guesses as to the nature of the
   planned DB will be, my guess is that overall machine load will not be
   so high that the process forking becomes critical.  My guess is that
   support will be hard to come by in alot of public school environments,
   so I'd guess their building for trouble-free operation before speed.
   
   > I sent her a private note saying she really probably shouldn't be looking
   > at MySQL for her application, presumably having a real transaction-based
   > db is a Good Thing when maintaining a database of student grades.  Told
   > her she should be looking at various real RDBMS solutions and should leave
   > MySQL out of the picture entirely (while also telling her I thought PG
   > would work fine for her needs, of course).
   
   That's a good summary of my intended take-home point as well, though
   you said it much more clearly.  All the rest was just personal
   experience that applies to our environment but my not apply to yours
   or hers.
   
   Karl
   
   

7. Re: [HACKERS] Simmultanous Connections (fwd)

   Don Baccus <dhogaza@pacifier.com> — 2000-01-10T19:23:45Z

   At 01:35 PM 1/10/00 -0500, Lamar Owen wrote:
   
   >I don't know how close to the AOLserver model PHP is (I think it is
   >pretty close, as the beta of PHP4 is buildable to run as a module under
   >AOLserver), but the concept of pooled persistent connections is a sound
   >one, and eliminates some grief (as long as you watch your transactions
   >-- don't want two connections that happen to share a pool instance to
   >share a transaction roolback!).
   
   Spoken like a long-suffering user of AOLserver's original postgres
   driver :)
   
   I've solved this particular problem in the latest version of the driver,
   and other problems related to backends crashing and the like.  This is
   why I suggest that if there are problems with PHPs persistent database
   connections and Postgres that the PHP implementation of such connections
   needs work.  I know from experience that persistent pooled connections
   can be implemented in a non-robust fashion (the old postgres driver for
   AOLserver) but I also know that they can be made robust, from personal
   experience.
   
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, Pacific Northwest
     Rare Bird Alert Service and other goodies at
     http://donb.photo.net.
   
   

8. Re: [HACKERS] Simmultanous Connections (fwd)

   Don Baccus <dhogaza@pacifier.com> — 2000-01-10T19:40:52Z

   At 01:38 PM 1/10/00 -0500, Karl DeBisschop wrote:
   
   >This isn't really a hackers issue, so I'll try to be brief but also
   >give a little more info than I originally did.  Maybe any further
   >discussion would be best placed in pgsql-general.
   
   Perhaps.  I'll give one brief answer here, though.  It probably
   doesn't hurt the developers to see how their product is used in
   real-life scenarios anyway...
   
   >Basically, I think it may depend on the use - for our website, we get
   >connections from a variety of sources - most of them don't repeat for
   >a long time, if ever.  Which means a bunch sit around at any given
   >time, never to be reused.  If the new connections come fast enough,
   >this can translate to real problems unless they timeout quickly, which
   >defeats the purpose.
   
   >That being said, maybe the PHP implementaion does need some work, or
   >maybe there are site parameters we could tune to make it work.  But
   >whenever we use it, we do eventually end up in trouble as a result.
   
   My short answer: yes, it does need work if it works as you describe.
   The whole point of pooling persistent connections is to allow re-use.
   It sounds like either PHP makes it hard/impossible or that (maybe?)
   you folks haven't quite figured out how fully exploit their implementation
   of pooled connections.
   
   >So, personally, I don't recommend it in situations where alot of
   >different clients will be connecting to the DBMS - at least if low
   >maintennence is a key goal.
   
   The problem isn't persistent connections, the problem is the particular
   implementation you're using.  AOLserver's implementation is trouble
   free, for Postgres, Sybase, Oracle, and Solid.  And totally
   transparent to scripts and dynamic pages (other than SQL differences
   due to the dbs themselves).  The PHP folks are making it available 
   within AOLserver, as Lamar Owen has pointed out.  If they also plug
   into the AOLserver implementation of pooled persistent database
   connections, then PHP users will also have a platform available which 
   reliably supports such connections.
   
   >> Forking a new backend is actually considerably more expensive then
   >> just passing back the PID of an existing backend...
   >
   >>From the point of view of the server, absolutely.  But that connection
   >time is still a very small part of the user's total trransaction time.
   
   Depends on how you're using the database.  If you're using it to
   personalize pages, for instance, you'll be using a lot of simple,
   quick selects.  If you're only using the database for complicated,
   slow queries then perhaps you're right.
   
   Let's put it this way ... folks who have a lot more experience than
   me at running very busy database-backed web sites have observed that it
   DOES make a large difference in the scalability of a site.  These,
   though, are sites make heavy use of the database when serving up
   pages.
   
   If forking weren't a problem, the Apache folks wouldn't've bothered
   building modPerl, for instance...
   
   >And, although I am making alot of guesses as to the nature of the
   >planned DB will be, my guess is that overall machine load will not be
   >so high that the process forking becomes critical.  My guess is that
   >support will be hard to come by in alot of public school environments,
   >so I'd guess their building for trouble-free operation before speed.
   
   Nothing to disagree with here, other than the fact that my own 
   personal experience tells me that persistent connections needn't be a
   source of trouble.  If they are PHP users, though, and if the
   site really is using PHP as you suspect, then they should probably
   avoid them if your experience is an accurate reflection of the state
   of the implementation of persistent connections available to PHP
   users.
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, Pacific Northwest
     Rare Bird Alert Service and other goodies at
     http://donb.photo.net.
   
   

9. Re: [HACKERS] Simmultanous Connections (fwd)

   Stephen Birch <sbirch@ironmountainsystems.com> — 2000-01-10T20:08:33Z

   When I was researching PG vs MySQL - the big kicker was transaction support
   (mySQL doesn't have it).  It looks like MySQL is faster than PG and so is
   great for serving data to a web site.  But if you also need to perform
   updates to multiple tables, you probably will want to use transactions - so
   use PostgreSQL.
   
   I chose PostgreSQL for this reason.
   
   The PostgreSQL team is incredible.  To try and contribute a little, we
   switched our development from the released software to the PG development
   software (so we can report, and sometimes fix problems) - any bugs we
   discovered but couldn't fix ourselves were fixed within hours.  Our own
   modifications were checked and entered in their development tree in less
   than an hour.  Cool.
   
   The code itself is of very high quality.
   
   Steve
   
   
   
   Vince Vielhaber wrote:
   
   > Can anyone here help?
   >
   > Vince.
   >
   > ---------- Forwarded message ----------
   > Date: Mon, 10 Jan 2000 08:52:06 +0000
   > From: Jude Weaver <exec@shreve.net>
   > To: webmaster@postgresql.org
   > Subject: Simmultanous Connections
   >
   > We are a company that writes academic software . We are converting our
   > software to use  either PostgreSQL or MySQL. We are leaning toward
   > PostgreSQL, but, I still have several questions.
   > I hope someone can answer these for me.
   >
   > 1.  I have read the Q&A for postgreSQL and would like to know the
   > difference between a temporary
   >      and a permanant connection. Do you have a connection when you open
   > the database or only when
   >       the frontend sends a job to the backend? If 32 people are running
   > a module that opens a database
   >        is that 32 connections or will it vary as users read and write to
   > the database?
   >
   > 2. I saw in the Q&A that to run more than 32 simmultanous connects could
   > be a big drain on our re-
   >     sources. Our  Linux boxes , in general, are Intel 166 to 500s, 128MG
   > of RAM and 6.2 to 13 GIG.
   >     Can anyone tell me roughly how much resources per connection does
   > PostgreSQL use?
   >
   > 3. If I have 90 teachers posting grades at the same time, (the grade
   > posting program opens 5 dif-
   >     ferent databases) and  25 secretaries and administrators poking
   > around in assorted databases
   >      looking at information, will postgresql handle that much traffic?
   >
   > I would appreciate any information you can give me,
   > Thank you - Jude Weaver.
   >
   > ************
   
   
   

10. Re: [HACKERS] Simmultanous Connections (fwd)

    Mateus Cordeiro Inssa <mateus@ifnet.com.br> — 2000-01-11T10:45:51Z

    Karl DeBisschop writes:
     > Sounds like she may looking at postgres in PHP - at least PHP uses
     > that temporary and permanant connection concept.  My experience is
     > that PHP persistent connections are not worth it - the time to
     > establish a new connection is pretty small, and stale connections can
     > cause problems.
     > 
     > >   2. I saw in the Q&A that to run more than 32 simmultanous connects could
     > >   be a big drain on our re-
     > >       sources. Our  Linux boxes , in general, are Intel 166 to 500s, 128MG
     > >   of RAM and 6.2 to 13 GIG.
     > >       Can anyone tell me roughly how much resources per connection does
     > >   PostgreSQL use?
     > 
     > If an idle psql connection is left open, we're looking at about 1 MB
     > RAM plus 4MB swap on my linux box.
     > 
     > As I noted above, I'd generally recommend against persistent
     > connections when there are more than a few users.
    
      As an example, I have systems with 2 or 3 hundred simultaneos
    connections and besides being short time connections it's impossible to
    have 200 or 300 backends running at the same time.
      In this case, I had to create a proxy to use few connections. I have 
    AF_INET and AF_UNIX versions.
    
      []'s
    
    Mateus Cordeiro Inssa
    ---------------------
    Linux User: 76186  Kernel: 2.3.36
    ICQ (Licq): 15243895
    ---------------------
    mateus@ifnet.com.br
    mateus@cwb.fnn.net
    
    Tue Jan 11 08:45:00 EDT 2000
    
    

11. Re: [HACKERS] Simmultanous Connections (fwd)

    Don Baccus <dhogaza@pacifier.com> — 2000-01-11T15:21:43Z

    At 08:45 AM 1/11/00 -0200, Mateus Cordeiro Inssa wrote:
    
    >  As an example, I have systems with 2 or 3 hundred simultaneos
    >connections and besides being short time connections it's impossible to
    >have 200 or 300 backends running at the same time.
    
    Again, the problem isn't persistent connections but rather an
    lousy implementation of pooled persistent connections.  
    
    >  In this case, I had to create a proxy to use few connections.
    
    Another approach is to throttle the number of connections in the
    persistent pool manager.  This is how AOLserver deals with the 
    problem.  You tell it the max number of connections to fire up
    and only that many handles are doled out to threads, the rest
    waiting for others to complete.  There's another parameter which
    places a ceiling on the number of threads allowed to wait for
    a pool connection, which allows me to return a "too busy" 
    message to the user if I so choose.  Of course, if a server
    starts getting too many of these it's time to upgrade to
    something faster, to dig into one's queries looking for
    needless inefficiency, or maybe to remember that you forgot
    to say "vacuum analyze" (who, me?)
    
    Some folks like to roll their own.  I'm lazy and picked a web
    server that has already solved such problems for me.
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.