
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.



Mon, 10 Jan 2000 11:09:06 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Simmultanous Connections (fwd)

>   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

Mon, 10 Jan 2000 11:46:27 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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 


Mon, 10 Jan 2000 12:46:41 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Simmultanous Connections (fwd)
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.

Mon, 10 Jan 2000 10:07:24 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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

Mon, 10 Jan 2000 13:35:38 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: [HACKERS] Simmultanous Connections (fwd)

> 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

Mon, 10 Jan 2000 13:38:14 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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.

Mon, 10 Jan 2000 11:23:45 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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.

Mon, 10 Jan 2000 11:40:52 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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.
>
> ************


Mon, 10 Jan 2000 12:08:33 -0800
Stephen Birch <sbirch@ironmountainsystems.com>
Re: [HACKERS] Simmultanous Connections (fwd)
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

Tue, 11 Jan 2000 08:45:51 -0200 (EDT)
Mateus Cordeiro Inssa <mateus@ifnet.com.br>
Re: [HACKERS] Simmultanous Connections (fwd)
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.

Tue, 11 Jan 2000 07:21:43 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Simmultanous Connections (fwd)