Re: Connection Pooling / TP Monitor

[Jeff Horn]

The only way I really see this working is in a threading environment.  First
of all, for some databases database connections don't survive forking
(Oracle is the notable example here).  Also, even if we could get forking to
work, we would still get the scaling problem we are trying to avoid.
Instead of Oracle keeping a huge list of persistent connections, the
Connection Pool would keep a huge list of persistent connections.  In both
cases each connection would map to a Unix process and all these processes
would chew up OS resources big time!


- Original Message -
From: "Matt Sergeant" <[EMAIL PROTECTED]>
To: "Tim Bunce" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; "Jeff Horn" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, October 27, 2000 7:02 AM
Subject: Re: Connection Pooling / TP Monitor


> On Fri, 27 Oct 2000, Tim Bunce wrote:
>
> > > Sounds like just a CORBA/RPC type thing. Wouldn't you be better off
using
> > > CORBA::ORBit?
> >
> > Maybe. I dunno. I don't actually need this stuff, I just want there to
> > be a solution out there for those that do. I'm waving my hands around
> > and pointing in various directions hoping someone will _do_ something!
>
> Hehe...
>
> OK, lets think about exactly what is needed here then. I figure Doug's
> Apache::DBIPool module (for mod_perl 2.0) is exactly the right
> architecture:
>
> 2 pools of connections (Busy and Waiting)
> New connections always taken from the head of Waiting
> Finished connections always replaced on the head of Waiting
> Threaded architecture (DBI::Oracle handles don't survive a fork)
> One thread for management
> One thread per connection once a handle has been supplied
> Some sort of timeout mechanism for connections if the pool is
> fully allocated
>
> Anything I've missed?
>
> If we don't go the threaded route, we can't easily expand and contract the
> connection pool I don't think - but I'd love to be proved wrong. Also an
> entire Apache server for the connection pool would be too much - the
> pre-forking server from the cookbook would be better. And it should even
> work on Win32 now...
>
> --
> 
>
> /||** Director and CTO **
>//||**  AxKit.com Ltd   **  ** XML Application Serving **
>   // ||** http://axkit.org **  ** XSLT, XPathScript, XSP  **
>  // \\| // ** Personal Web Site: http://sergeant.org/ **
>  \\//
>  //\\
> //  \\
>

Re: Connection Pooling / TP Monitor

[Jeff Horn]

I think that something like Apache::DBIPool is exactly right on!  Is there
any way that this can be made to work as a standalone process like
DBI::ProxyServer or be made to work with Apache 1.3.x?  I'm leary of using
alpha software for my whole production server and would be more comfortable
running tests just on the database pooling within Apache 1.3.x or as a
seperate process.

In any event, where can I get my hands on Apache::DBIPool... I cannot seem
to find it on CPAN or on apache.org.

-- Jeff
- Original Message -
From: "Matt Sergeant" <[EMAIL PROTECTED]>
To: "Tim Bunce" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; "Jeff Horn" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, October 27, 2000 7:02 AM
Subject: Re: Connection Pooling / TP Monitor


> On Fri, 27 Oct 2000, Tim Bunce wrote:
>
> > > Sounds like just a CORBA/RPC type thing. Wouldn't you be better off
using
> > > CORBA::ORBit?
> >
> > Maybe. I dunno. I don't actually need this stuff, I just want there to
> > be a solution out there for those that do. I'm waving my hands around
> > and pointing in various directions hoping someone will _do_ something!
>
> Hehe...
>
> OK, lets think about exactly what is needed here then. I figure Doug's
> Apache::DBIPool module (for mod_perl 2.0) is exactly the right
> architecture:
>
> 2 pools of connections (Busy and Waiting)
> New connections always taken from the head of Waiting
> Finished connections always replaced on the head of Waiting
> Threaded architecture (DBI::Oracle handles don't survive a fork)
> One thread for management
> One thread per connection once a handle has been supplied
> Some sort of timeout mechanism for connections if the pool is
> fully allocated
>
> Anything I've missed?
>
> If we don't go the threaded route, we can't easily expand and contract the
> connection pool I don't think - but I'd love to be proved wrong. Also an
> entire Apache server for the connection pool would be too much - the
> pre-forking server from the cookbook would be better. And it should even
> work on Win32 now...
>
> --
> 
>
> /||** Director and CTO **
>//||**  AxKit.com Ltd   **  ** XML Application Serving **
>   // ||** http://axkit.org **  ** XSLT, XPathScript, XSP  **
>  // \\| // ** Personal Web Site: http://sergeant.org/ **
>  \\//
>  //\\
> //  \\
>

Connection Pooling / TP Monitor

[Jeff Horn]

First let me say that I'm aware that this topic 
comes up with some frequency on the mod_perl and DBI-users list.  I am 
aware of posts like this one:
 
http:[EMAIL PROTECTED]
 
which argue against the necessity of pooling.  

 
However, I am also aware of a _major_ ISP that 
implements their email system using a _major_ RDBMS that has had problems that 
are best solved via connection pooling.  Essentially, the time it takes 
them to search through all the cached connections is nearly as long as the time 
it is taking to read/write to the database.   Although, I'm not 
implementing email as this ISP is, I think that scalability in my case may 
definitely run into similar roadblocks.
 
I am interested in hearing from anyone that has 
tried to implement true connection pooling either within Apache or as an 
external process.  I'm particularly interested in hearing about 
implementations that could be made to work or are done using Perl and 
DBI/DBD.  I am mostly interested in things that are Open Source or licensed 
like Perl itself.
 
I am aware of a project called Gnu Transaction 
Server (GTS), but it doesn't seem like this is quite ready for prime time at the 
moment or is even under active development.  I've seen posts that hint at 
using shared memory and IPC to implement this within Apache as well as posts 
that hint at possibilities of implementing this using DBI::Proxy.
 
I basically want to do what the big TP monitors 
(Tuxedo/Encina/CICS) do with respect to condensing connections to a database, 
but I'm not in need of features like two-phase commit, cross database joins, 
heterogeneous database environment, etc. incorporated in these 
products.
 
Even if you'd simply be interested in working on 
such a project, I'd like to hear from you.  If you think such a project is 
plain stupid, I'd also be interested in hearing from you (but be gentle!).  
If you already have something sort of working along these lines, I'd DEFINITELY 
be interested in hearing from you!
 
-- Jeff Horn

Re: Auto rollback using Apache::DBI

[Jeff Horn]

Yes, I ran into this while I was making a version of Apache::DBI which uses
'reauthenticate' to maintain a single connection per Apache child (per
database) and simply reauthenticate on that connection.  It turned out that
I modified what $Idx was composed of and didn't understand why I was not
getting rollbacks when sessions ended without commits.

I too think that the cleanup handler should ALWAYS be pushed and that the
handler itself should check for the AutoCommit status before issuing a
rollback.  Should be easy enough to implement.

-- Jeff
- Original Message -
From: "Honza Pazdziora" <[EMAIL PROTECTED]>
To: "Nicolas MONNET" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 07, 2000 9:17 AM
Subject: Re: Auto rollback using Apache::DBI


> On Thu, Sep 07, 2000 at 04:03:04PM +0200, Nicolas MONNET wrote:
> >
> > I might get something wrong, but while in non-autocommit, if a script
dies
> > before rollbacking or commiting, looks like the transaction never gets
> > cancelled until I kill -HUP httpd! Quite a problem ...
> >
> > Is there any known way to catch this?
>
> Looking at the code in Apache::DBI 0.87, the handle is only rollbacked
> if the AutoCommit is set to zero during connect, not if you do
>
> $dbh->{'AutoCommit'} = 0;
>
> in your script.
>
> I wonder if the $needCleanup test is wanted at all. We could make it
> a configuration option, not to push the cleanup handler, but I believe
> that generally the rollback is wanted thing in all cases.
>
> --
> 
>  Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/
>  .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain, ...
> 
>

Re: Poor man's connection pooling

[Jeff Horn]

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]
> >

New Version of Apache::DBI which uses 'reauthenticate' instead of caching all connections

[Jeff Horn]

I've made some changes which allow this module to 
be used in situations where scripts mess with common and database handle 
attributes.  Each connection can be made and get defaults for such 
attributes modulo any changes that are made in the connect call.
 
This module is very useful in situations where a 
web application wishes to use database security.  In this situation, the 
current Apache::DBI on CPAN quickly runs into scaling problems as it attempts to 
maintain many, many connections.
 
I would like input on whether people think it would 
be better to distribute this in the future as Apache::DBIReauth or somehow meld 
this with the existing Apache::DBI with some configuration options as to which 
kind of caching to use.
 
Please let me know what you think!
 
-- Jeff Horn
 DBI.pm

Fw: Apache::DBI using 'reauthenticate' instead of caching

[Jeff Horn]

Gentlemen:
 
Have you had any time to look at this?  I'm 
attatching my modified version of Apache::DBI.  Please send me your 
comments and concerns as I would like to send this out to the mod_perl and DBI 
community very shortly.  I thank you for your time!
 
- Original Message - 
From: Jeff Horn 
To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; [EMAIL PROTECTED] 
Sent: Tuesday, June 20, 2000 7:50 PM
Subject: RFC: Apache::DBI using 'reauthenticate' instead of 
caching

Gentlemen:
 
John Groenveld suggested that I run this by each of 
you.  I have made a hack to Apache::DBI which uses the Oracle specific DBD 
call to
$dbh->func($user, $pwd, 'reauthenticate') to 
maintain a single connection per Oracle database and then simply reauthenticate 
on the existing connection.  This turns out to be 5-6 times faster than 
initiating a brand new connect for each script.
 
Now, this is slower than straight Apache::DBI in an 
environment where the same connect string is used for every script running on a 
web server.  However, it is a definite win in situations where database 
level security is being used and there are large numbers of logins!
 
If you have any time to look at this I'd be most 
appreciative.  I'd like to contribute this to CPAN as soon as I run it by a 
few more people and add a few bells and whistles.  Your suggestions as to 
what to name the new module would also be appreciated.  Currently it's 
implemented as a replacement for Apache::DBI (and steals the name), but before I 
submit it I want to rename the module (perhaps to something like 
Apache::DBI::Reauth).
 
One problem that needs to be addressed before 
making this generally available is that I need to set DBI attributes back to 
their defaults and then reset any specified in the connect string before 
returning the database handle.  This is not a problem in my current 
environment, but will be in a general environment.  Any other problems you 
see with the code are appreciated!
 
Thank you so much for your valuable 
time!
 
-- Jeff Horn
 
 
 DBI.pm