One thing I've looked at doing is adding an LRU mechanism (or some such
discipline) to the existing Apache::DBI.
I've already modified Apache::DBI to use 'reauthenticate' if a DBD
implements it so that only 1 connection needs to be maintained per child.
This greatly improves performance with a 1:1 ratio between Apache children
and database connections.
However, some (most) DBD don't implement this method and in fact some
databases cannot even do a 'reauthenticate' on an existing connection.
Also, there are situations where performance can be greatly improved by a
judicious choice of the # of connections maintained per child. An example
of this is where one user is used to verify authentication info stored in a
database and then if authenticated a new user id is used to actually do the
work. In this case keeping 2 connections per Apache child, pins the common
"authenticating" connection and rotates the other connection, while still
keeping the total number of connections quite low.
There are other disciplines besides LRU which might work well. Keeping a
"Hot Set" of connections by keeping a count of how often they're accessed
might a useful alternative. I'm sure there's all sorts of interesting
disciplines that could be dreamed up!
I'm looking at potential changes to Apache::DBI which would allow a choice
of discipline (LRU, HotSet, etc.) along with whether or not to use
'reauthenticate' DBD function all configurable from apache config files.
I'd be interested in any input on this course of action!
-- Jeff Horn
> ----- Original Message -----
> From: "Michael Peppler" <[EMAIL PROTECTED]>
> To: "Barrie Slaymaker" <[EMAIL PROTECTED]>
> Cc: "Michael Peppler" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
Sent: Wednesday, September 06, 2000 12:24 PM
> Subject: Re: Poor man's connection pooling
>
>
> > Barrie Slaymaker writes:
> > > Michael Peppler wrote:
> > >
> > > > Based on preliminary tests I was able to use a 1 in 10 ratio of
> > > > database handles per httpd child processes, which, on a large site
> > > > would cut down on the number of connections that the database
server
> > > > needs to handle.
> > >
> > I'd be interested to see how this compares with Apache::DBI
performance
> with
> > > MaxRequestsPerChild >= 100. I suspect it's negligable unless you're
> using
> > > a database like Oracle (with lots of connection establishment
overhead)
> over
> > a *slow* link.
> >
> > The back-end is Sybase. The actual connect time isn't the issue here
> > (for me.) It's the sheer number of connections, and the potential
> > issue with the number of sockets in CLOSE_WAIT or TIME_WAIT state on
> > the database server. We're looking at a farm of 40 front-end servers,
> each runnning ~150 modperl procs. If each of the modperl procs opens
> > one connection that's 6000 connections on the database side.
> >
> > Sybase can handle this, but I'd rather use a lower number, hence the
> > pooling.
> >
> > I don't think there's any significant memory savings here, is there?
> Things
> > > you think might share physical memory probably don't after a SQL
> statement
> > > or two and some perl code gets run. Oracle certainly changes the RAM
> in
> > their connection and cursor handles, for instance.
> >
> > I'm not worried about RAM usage on the web servers.
> >
> > Michael> > --
> Michael Peppler -||- Data Migrations Inc.
> > [EMAIL PROTECTED] -||- http://www.mbay.net/~mpeppler
> > Int. Sybase User Group -||- http://www.isug.com
> > Sybase on Linux mailing list: [EMAIL PROTECTED]
> >
