[cgiapp] cached DBI connections with cgiapp/fastcgi

[Benjamin Hitz]

>
> On Oct 22, 2008, at 10:39 AM, Michael Peters wrote:
>>
>> It will probably reconnect if the handle isn't there. But not if it  
>> goes bad. I don't know for sure since I don't use that plugin. But  
>> it's a common problem when dealing with persistant DB connections.  
>> If you want to see how mod_perl folks handle, take a look at  
>> Apache::DBI. Look at it's connect() method and particularly how it  
>> uses ping().
>

I am sure are all dying for an update.
1) Unable to turn on DBI_TRACE within perl ($ENV{DBI_TRACE} = 2=/path/ 
to/file).  I have had this problem in the past with other scripts, I  
suppose I will have to fiddle around more with this.
2) The error is here: DBD::Oracle::st execute failed: ORA-01012: not  
logged on (DBD ERROR: OCIStmtExecute)  and it will occur after the  
IDLE time of the user is exceeded. This was tested with a special user  
with 10M idle time.

My review of cgiapp::Plugin::DBH and Apache::DBI
Turns up the following:

my code:

$self->dbh_config(sub { DBI->connect_cached(..) });

Plugin::DBH

(sub dbh)
     ...
     unless( defined($self->{__DBH}{$name}) && $self->{__DBH}{$name}- 
 >ping ) {
         # create DBH object
         if(my $config = $self->{__DBH_CONFIG}{$name} ) {
             # Use a callback
             if (ref $config eq 'CODE') {
                 $self->{__DBH}{$name} = $config->();
             }
             # use the parameters the user supplied
             else {
                 require DBI;
                 $self->{__DBH}{$name} = DBI->connect(@{ $self- 
 >{__DBH_CONFIG}{$name} });
             }
         } else {
         }
     }


sub connect_cached in DBI.pm:

	if ($dbh && $dbh->FETCH('Active') && eval { $dbh->ping }) {
             # If the caller has provided a callback then call it
             if ($cb and $cb = $cb->{"connect_cached.reused"}) {
		local $_ = "connect_cached.reused";
		$cb->($dbh, $dsn, $user, $auth, $attr);
             }
	    return $dbh;
	}

	# If the caller has provided a callback then call it
	# ... not using Callbacks so deleted for brevity

	$dbh = $drh->connect(@_);
	$cache->{$key} = $dbh;	# replace prev entry, even if connect failed
	return $dbh;


Apache::DBI essentially reimplements connect_cached with a ping only  
when necessary:

     # do we need to ping the database ?
     $PingTimeOut{$dsn}  = 0 unless $PingTimeOut{$dsn};
     $LastPingTime{$dsn} = 0 unless $LastPingTime{$dsn};
     my $now = time;
     my $needping = (($PingTimeOut{$dsn} == 0 or $PingTimeOut{$dsn} > 0)
		    and (($now - $LastPingTime{$dsn}) >= $PingTimeOut{$dsn})
		   ) ? 1 : 0;
     print STDERR "$prefix need ping: ", $needping == 1 ? "yes" :  
"no", "\n" if $Apache::DBI::DEBUG > 1;
     $LastPingTime{$dsn} = $now;

     # check first if there is already a database-handle cached
     # if this is the case, possibly verify the database-handle
     # using the ping-method. Use eval for checking the connection
     # handle in order to avoid problems (dying inside ping) when
     # RaiseError being on and the handle is invalid.
     if ($Connected{$Idx} and (!$needping or eval{$Connected{$Idx}- 
 >ping})) {
         print STDERR "$prefix already connected to '$Idx'\n" if  
$Apache::DBI::DEBUG > 1;
         return (bless $Connected{$Idx}, 'Apache::DBI::db');
     }

     # either there is no database handle-cached or it is not valid,
     # so get a new database-handle and store it in the cache
     delete $Connected{$Idx};
     $Connected{$Idx} = $drh->connect(@args);
     return undef if !$Connected{$Idx};

     # return the new database handle
     print STDERR "$prefix new connect to '$Idx'\n" if  
$Apache::DBI::DEBUG;
     return (bless $Connected{$Idx}, 'Apache::DBI::db');


I note that in the Plugin::DBH code there is no eval{} around the  
ping...  I will probably try that.

First I am going to try what I have with just connect() instead of  
connect_cached.. I am wondering if the dual layer of $dbh checking is  
causing system to get confused.  I agree oracle connections are  
slow... but the FastCGI version of this page takes 3 seconds WHEN  
CACHED... so I am not really worried about it (will profile it though)

Ben


--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu