Re: SAPI (Apache 2) question & connection pooling

> On Fri, Feb 01, 2002 at 09:14:24AM -0800, Rasmus Lerdorf wrote:
> > > a) if Apache 2 is a single-process server (as opposed to Apache 1.x which
> > > forks a new process for a new request)
> >
> > It is a hybrid server which can be anything from one to the other.
> >
> > > b) if PHP will work, or does work, with Apache 2 in this mode
> >
> > It works today.
> >
> > > c) how database connection pooling will work with a single http/php
> > > multithreaded server process
> >
> > There is no database pooling.
>
> Then how does pg_pconnect() work, for instance, when there's only a
> single process that talks to the database? With persistent connections,
> as they work now, you can have as many database connections per apache
> child as you have databases hosted on your box. On our in-house server
> (which is typically not very busy) it looks like this at the moment:
>
> ---------------------- snip ----------------------
> frank@limedes:~ > ps ax |grep postgres
> 17954 ?     S      0:09 postgres: wwwrun archi 127.0.0.1 idle
> 20479 ?     S      0:00 postgres: wwwrun ip_adressen [local] idle
> 21576 ?     S      0:12 postgres: wwwrun archi 127.0.0.1 idle
> 21707 ?     S      0:00 postgres: nobody crewscout 192.168.100.220 idle
> 21736 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> 21737 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> 21738 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> 21739 ?     S      0:00 postgres: nobody crewscout 192.168.100.220 idle
> 21740 ?     S      0:00 postgres: nobody crewscout 192.168.100.220 idle
> 21777 ?     S      0:00 postgres: nobody crewscout 192.168.100.220 idle
> 21844 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> 21845 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> 21846 ?     S      0:00 postgres: nobody archimeta 192.168.100.220 idle
> ---------------------- snap ----------------------
>
> Which is not a problem at all. However, things start to get awkward when
> the system gets busier: Say you have 20 apache children sitting around
> waiting for clients, and each one of them has at some stage in their
> career been asked to talk to each of the 5 databases using persistent
> connections that are hosted on this box. This means you'd have 100
> postgres processes sitting around, where most of them would be idle . .

Correct.  That's exactly how it is supposed to work.  They sit around idle
so that the startup time will be minimal when it is needed.  If you don't
want this, simply don't use a pconnect.

> > Why do you think you need connection pooling?  Having contention for a
> > small set of connections is certainly not going to speed anything up.  Is
>
> Compared to persistent connections, there would be no speedup; compared
> to non-persistent connections, yes there would - because with a
> connection pool you don't incur the overhead of establishing a new
> connection for every request, basically as with persistent connections.

Yes, and when all the connections in the pool are busy you end up with
massive slowdown as you will have requests sitting around waiting to get
access to a connection.

> No, licensing is not an issue. I use persistent connections to offset
> the overhead of creating a db connection in the first place, which is
> considered hefty if you have simple queries (it matters less with more
> expensive sql queries). Persistent connections are awkward because you
> need to configure your database server to accept the number of
> simultaneous connections that you actually need many times over, which
> is not exactly resource-friendly and may bog down a busy server. Then
> there's the added complication with transacations that is listed amongst
> the caveats with persistent connections at

Well, the many-times-over part is only if you use many different
connection credentials and use a database that is unable to dynamically
switch its credentials on the fly.

> http://www.php3.de/manual/en/features.persistent-connections.php
>
> The transaction issue supposedly does not arise with a
> single-process/multithreaded/connection pooling server, presumably (never
> wrote any pooling code myself . . . ) because it stems from the fact
> that with a multiprocess server, you don't know which process will
> answer the next request.

I believe this has been fixed for Postgres.

-Rasmus