Brief background: I am working to keep the total connection count to our database as low as possible. Apache::DBI tends to accumulate database connections over time, including connections from rarely-accessed CGI programs. I wanted a way to limit the connection count beyond the child spawning and lifespan configuration options Apache gives me.
What I want from connection pooling is to cache database connections for a relatively short period of time, such as while a user is actively clicking around a web application. Once that user leaves the application, ideally the database connections would expire.
The logic of this patch is to keep a timestamp for every connection in the pool, the timestamp is updated whenever a connect() call comes in for that connection.
On each connect() call, the timestamps of all the open connections are examined. If any connection is found to have a timestamp older than 1 hour (default, configured with $Apache::DBI::CONN_TTL), that connection is deleted.
In addition to this, I added some statistics tracking, and updated the Apache::Status report to show totals for all connections: the number of times that connection has been requested, a count of how many times the same connection has been reused, how many times the connection had failed ping or otherwise needed to be reconnected, and how many times that connection has been deleted for being idle.
A big downside to this idea is that the server has to be actively asking for new connections to the database for the code to run and clean out old connections, and that every apache child process has its own independent pool of new and old connections. I would like to see some dependable way to get all the apache children to clean up old connections on a regular basis.
I am very interested in hearing ideas on how else to address this problem. Any feedback on this patch is appreciated.
Thanks,
- Robert
--- /tmp/DBI.pm Thu Aug 26 14:59:20 2004
+++ DBI.pm Thu Aug 26 18:53:36 2004
@@ -1,7 +1,7 @@
package Apache::DBI;
use strict;
-# $Id: DBI.pm,v 1.12 2004/02/18 00:18:50 ask Exp $
+# $Id: DBI.pm 40336 2004-08-27 01:53:36Z jrray $
BEGIN { eval { require Apache } }
use DBI ();
@@ -9,20 +9,31 @@
require_version DBI 1.00;
-$Apache::DBI::VERSION = '0.94';
+$Apache::DBI::VERSION = '0.941';
# 1: report about new connect
# 2: full debug output
$Apache::DBI::DEBUG = 0;
#DBI->trace(2);
+# Allow connections to sit idle for only an hour
+$Apache::DBI::CONN_TTL = 3600;
+
my %Connected; # cache for database handles
my @ChildConnect; # connections to be established when a new httpd child is created
my %Rollback; # keeps track of pushed PerlCleanupHandler which can do a rollback
after the request has finished
my %PingTimeOut; # stores the timeout values per data_source, a negative value
de-activates ping, default = 0
my %LastPingTime; # keeps track of last ping per data_source
+my %LastConnTime; # keeps track of when the last time this connection was used
my $Idx; # key of %Connected and %Rollback.
+my %Statistics; # table to keep track of connection usage statistics, keyed
+ # by $Idx
+ # attempts - number of times connect() is called
+ # expired - number of times connection was deleted due to idle
timeout
+ # reused - number of times connection successfully reused
+ # invalid - number of times connection in pool needed to be
recreated
+
# supposed to be called in a startup script.
# stores the data_source of all connections, which are supposed to be created upon
@@ -82,6 +93,9 @@
pop @args;
}
+ # This counts as a connect
+ $Statistics{$Idx}{attempts}++;
+
# don't cache connections created during server initialization; they
# won't be useful after ChildInit, since multiple processes trying to
# work over the same database connection simultaneously will receive
@@ -112,6 +126,32 @@
print STDERR "$prefix need ping: ", $needping == 1 ? "yes" : "no", "\n" if
$Apache::DBI::DEBUG > 1;
$LastPingTime{$dsn} = $now;
+ # clean up old connections
+ while (my ($Conn, $Last) = each %LastConnTime) {
+
+ # skip the current connection attempt of course
+ next if $Conn eq $Idx;
+
+ # CONN_TTL < 0 means this behavior is disabled
+ last if $Apache::DBI::CONN_TTL < 0;
+
+ if ($Last + $Apache::DBI::CONN_TTL < $now) {
+ # reap this old connection
+ print STDERR "$prefix removing timed-out connection '$Conn'\n" if
$Apache::DBI::DEBUG > 1;
+
+ $Statistics{$Conn}{expired}++;
+
+ if ($Connected{$Conn} && $Connected{$Conn}->{Active}) {
+ $Connected{$Conn}->orig_disconnect();
+ }
+
+ delete $Connected{$Conn};
+ delete $Rollback{$Conn};
+ delete $LastPingTime{$Conn};
+ delete $LastConnTime{$Conn};
+ }
+ }
+
# 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
@@ -119,14 +159,21 @@
# 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;
+ $LastConnTime{$Idx} = $now;
+ $Statistics{$Idx}{reused}++;
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
+ if ($Connected{$Idx}) {
+ $Statistics{$Idx}{invalid}++;
+ }
+
delete $Connected{$Idx};
$Connected{$Idx} = $drh->connect(@args);
return undef if !$Connected{$Idx};
+ $LastConnTime{$Idx} = $now;
# return the new database handle
print STDERR "$prefix new connect to '$Idx'\n" if $Apache::DBI::DEBUG;
@@ -184,6 +231,10 @@
no strict;
@ISA=qw(DBI::db);
use strict;
+ sub orig_disconnect {
+ my $self = shift;
+ $self->SUPER::disconnect();
+ };
sub disconnect {
my $prefix = "$$ Apache::DBI ";
print STDERR "$prefix disconnect (overloaded) \n" if $Apache::DBI::DEBUG > 1;
@@ -191,19 +242,45 @@
};
}
-
# prepare menu item for Apache::Status
Apache::Status->menu_item(
- 'DBI' => 'DBI connections',
+ 'DBI_STATUS' => 'DBI connections',
sub {
my($r, $q) = @_;
- my(@s) = qw(<TABLE><TR><TD>Datasource</TD><TD>Username</TD></TR>);
- for (keys %Connected) {
- push @s, '<TR><TD>', join('</TD><TD>', (split($;, $_))[0,1]),
"</TD></TR>\n";
+ my $now = time;
+
+ my @s = '<table width="100%"><tbody><tr>';
+
+ for ('Datasource', 'Username', 'Connected', 'Idle (Minutes)',
+ 'Connect Count', 'Reused Count', 'Invalid Count',
+ 'Expired Count') {
+
+ push @s, "<td>$_</td>\n";
+ }
+
+ push @s, "</tr>\n";
+
+ for (sort keys %Statistics) {
+ push @s, '<tr>';
+
+ push @s, '<td>', join('</td><td>', (split($;, $_))[0,1]), "</td>\n";
+
+ if ($Connected{$_}) {
+ my $minutes_ago = int(($now - $LastConnTime{$_}) / 60);
+ push @s, "<td>yes</td><td>$minutes_ago</td>";
+ } else {
+ push @s, '<td>no</td><td>-</td>';
+ }
+
+ for my $stat (qw(attempts reused invalid expired)) {
+ push @s, '<td>', $Statistics{$_}{$stat} || '0', '</td>';
}
- push @s, '</TABLE>';
+
+ push @s, "</tr>\n";
+ }
+ push @s, "</tbody></table>\n";
return [EMAIL PROTECTED];
}
@@ -346,6 +423,14 @@
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::CONN_TTL
+
+This variable controls how long, in seconds, to allow a database connection
+to sit idle before deleting the connection. A value of -1 disables this
+feature. The default value is 3600 (1 hour). Idle connections are only
+discovered during an attempt to connect to a database, and so idle connections
+may last longer than this value if no connection attempts are made.
+
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.
-- Report problems: http://perl.apache.org/bugs/ Mail list info: http://perl.apache.org/maillist/modperl.html List etiquette: http://perl.apache.org/maillist/email-etiquette.html
