[rfc] Apache::DBI - Limiting cached handles

[Patrick Mulvany <pa...@firedrake.org>]

I have recently had issues related to Apache::DBI caching rarely used connection strings. This is not really a bug but rather a feature. However when working in a production enviroment this means that a minor oversight can result in a number of rarely used connections from each apache process being created. These are never cleaned up until the process finally dies.

I propose an extention to the existing API that adds two new functions :-

        Apache::DBI->setCacheTimeOut($timeout)

       This configures the usage of the cache timeout method, to timeout con-
       nections after a set length of time in seconds. Setting the timeout to
       0 (Default) will disable this option. This can be used to force a
       reconnect for drivers, which do not implement the ping-method.

        Apache::DBI->setCacheMax($max)

       This configures the usage of the cache max connections method, to limit
       the number of cached connections. Setting the max to 0 (Default) will
       disable this option. This can be used to stop rouge connection strings
       from being perminently cached.

I have included a patch that has been tested under my development systems (Apache 1.3.xx) but needs to be confirmed as viable against Apache 2.

Hope this is of interested and I would welcome some comment on this.

Paddy



Index: DBI/DBI.pm
===================================================================
RCS file: /cvs/public/Apache/DBI/DBI.pm,v
retrieving revision 1.7
diff -u -r1.7 DBI.pm
--- DBI/DBI.pm	4 Apr 2003 10:58:53 -0000	1.7
+++ DBI/DBI.pm	20 May 2003 13:30:23 -0000
@@ -23,6 +23,12 @@
 my %LastPingTime; # keeps track of last ping per data_source
 my $Idx;          # key of %Connected and %Rollback.
 
+my %DisconnectTime;    # keeps track of handle connect time
+my @DisconnectIdx;     # keeps track of disconnect order time
+my %ConnectCount;      # keeps track of number of uses of a handle
+my $CacheTimeOut = 0 ; # max time a connection can be cached for 
+my $CacheMax     = 0 ; # max connection cached
+
 
 # supposed to be called in a startup script.
 # stores the data_source of all connections, which are supposed to be created upon
@@ -55,6 +61,33 @@
     }
 }
 
+# supposed to be called in a startup script.
+# stores the maximinium length that any handle will be 
+# cached before being automatically disconnected.
+# 0 = Ignore
+
+sub setCacheTimeOut { 
+    my $class       = shift;
+    my $timeout     = shift;
+    # sanity check
+    if ($timeout =~ /\d+/) {
+        $CacheTimeOut = $timeout;
+    }
+}
+
+# supposed to be called in a startup script.
+# stores the maximinium number of handles that will be 
+# cached before the oldest is automatically disconnected.
+# 0 = Ignore
+
+sub setCacheMax { 
+    my $class       = shift;
+    my $max         = shift;
+    # sanity check
+    if ($max =~ /\d+/) {
+        $CacheMax = $max;
+    }
+}
 
 # the connect method called from DBI::connect
 
@@ -110,19 +143,60 @@
     # 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;
+    # If cache timeout enabled then the handle must be within
+    # it's active period.
+    if ($Connected{$Idx} and (!$needping or eval{$Connected{$Idx}->ping}) and (!$CacheTimeOut or $DisconnectTime{$Idx}>$now)) {
+        print STDERR "$prefix already connected to '$Idx' Total handles ".(scalar keys %Connected)."\n" if $Apache::DBI::DEBUG > 1;
         return (bless $Connected{$Idx}, 'Apache::DBI::db');
     }
 
+    # Clean up timed out handles and remove last used when needed
+    if ($CacheTimeOut and scalar @DisconnectIdx and ($DisconnectIdx[0]<$now 
+          or ($CacheMax and (scalar @DisconnectIdx)>=$CacheMax))) {
+        print STDERR "$prefix Checking for redundant handles\n" if ($Apache::DBI::DEBUG > 1);
+        my $break_out = 5; # Break out after max 5 old handles cleaned up
+        while (scalar @DisconnectIdx and ($DisconnectIdx[0]<$now or ($CacheMax and (scalar @DisconnectIdx)>=$CacheMax))
+                and $break_out) {
+            for my $handle (keys %Connected) {
+                if ($DisconnectTime{$handle}==$DisconnectIdx[0]) {
+                   if ($Apache::DBI::DEBUG > 1) {
+                      if ($DisconnectIdx[0]<$now) {
+                          print STDERR "$prefix Expired '$handle'\n" 
+                      } else {
+                          print STDERR "$prefix Released '$handle'\n" 
+                      }
+                   }
+                   delete $Connected{$handle};
+                   shift @DisconnectIdx;
+                   delete $DisconnectTime{$handle};
+                   last;
+                }
+            }
+            $break_out--;
+        }
+        print STDERR "$prefix Breakout at $break_out\n" if ($Apache::DBI::DEBUG > 1);
+    }
+
+    # Clean up broken handles
+    my $find_id=undef;
+    for my $id (0..$#DisconnectIdx) {
+       $find_id=$id if ($DisconnectIdx[$id]==$DisconnectTime{$Idx});
+    }
+    splice(@DisconnectIdx, $find_id,1) if (defined $find_id);
+    delete $Connected{$Idx};
+    delete $DisconnectTime{$Idx};
+
     # 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};
 
+    $ConnectCount{$Idx} = 0;
+    $DisconnectTime{$Idx} = $now + $CacheTimeOut;
+    push @DisconnectIdx, $DisconnectTime{$Idx};
+
     # return the new database handle
-    print STDERR "$prefix new connect to '$Idx'\n" if $Apache::DBI::DEBUG;
+    print STDERR "$prefix new connect to '$Idx' as handle ".(scalar @DisconnectIdx)."\n" if $Apache::DBI::DEBUG;
     return (bless $Connected{$Idx}, 'Apache::DBI::db');
 }
 
@@ -340,6 +414,20 @@
 the validation of the database handle. This can be used for drivers, which 
 do not implement the ping-method. Setting the timeout > 0 will ping the 
 database only if the last access was more than timeout seconds before. 
+
+ Apache::DBI->setCacheTimeOut($timeout)
+
+This configures the usage of the cache timeout method, to timeout connections
+after a set length of time in seconds. Setting the timeout to 0 (Default) will 
+disable this option. This can be used to force a reconnect for drivers, which 
+do not implement the ping-method.
+
+ Apache::DBI->setCacheMax($max)
+
+This configures the usage of the cache max connections method, to limit the 
+number of cached connections. Setting the max to 0 (Default) will disable 
+this option. This can be used to stop rouge connection strings from being 
+perminently cached. 
 
 For the menu item 'DBI connections' you need to call Apache::Status BEFORE 
 Apache::DBI ! For an example of the configuration order see startup.pl.