DBI Module.

[Dave Mullen - Marikina CGI]

Dear Mr Bunce,

We are using perl 5.005 and mysql 3.23 on a Linux Redhat 7.2 system.

We are trying to implement a better "timeout" for the DBI connections, as
the one incorporated in the DBI module only has a granularity of seconds.

We don't use persistent connections or mod-perl, so connection time is an
important factor for us, and we wish to avoid excessive times, as this
affects our server performance.

In general we can connect to a mysql database on a remote server via a VPN
usually in 5 milliseconds. Indeed we ran a torture test to connect and
disconnect every second for 24 hours. 99.9% of the connections were good at
5 milliseconds, but we did see some connections at 200ms and even a couple
that took a whole 3 seconds to connect.

So, we have attempted to implement a "connection timeout" using the
Time::HiRes module, which has a suitable alarm function.

Herewith is the test script, which basically wraps the connection in an eval
block, and the Time::HiRes "alarm" will cause the eval to die after 10ms. If
outside the eval block we have a good handle, then no problem, otherwise the
eval will be retryed up to 10 times ...

We log the "die" results to a text file, to understand exactly what is
happening.

use Time::HiRes qw ( gettimeofday tv_interval setitimer ITIMER_REAL);
use DBI;

for ($zzz=0; $zzz<86400; $zzz++) {

$ConnectTimeOut = 0.01; # 10 milliseconds ...
$ConnectRetrys = 10;
$ConnectAttempts = 0;

$STATUS = "";
$SQL = "";

$Handle = "";

while (($ConnectAttempts < $ConnectRetrys) && ($Handle eq "")) {
 $STATUS = "";
 eval {
  local $SIG{ALRM} = sub { die "DB CONN FAIL" };
  setitimer(ITIMER_REAL, $ConnectTimeOut);
  $Handle = DBI->connect(
"DBI:mysql:Gino2;host=10.10.10.10;port=3306","user","password",{ RaiseError
=> 1});
  setitimer(ITIMER_REAL, 0);
 };
 if ($@) {
  open
HANDLE,">>/usr/local/apache/sites/secure.charmingescapes.com/cgi-bin/DummyTi
meout.log";
  print HANDLE $SQLTime." - ".$@;
  close HANDLE;
 }
 if ($@ and $@ =~ /DB CONN FAIL/) {
  $STATUS = "TIMED OUT";
 }
 if ($@ and $@ !~ /DB CONN FAIL/) {
  $STATUS = "OTHER PROBLEM";
 }
 if (!$@) {
  $STATUS = "GOOD CONNECT";
 }

 $ConnectAttempts++;

}

if ($Handle) {
 $Handle->disconnect;
}

sleep 1;

}

This script perfomed well over a 24 hour period, our only concern is that
sometimes we recorded the following error message during the "eval cleanup"
...

(in cleanup) dbih_getcom handle DBI::db=HASH(0x8258a28) is not a DBI handle
(has no magic)

Obviously, the alarm triggers and the eval block dies at some point during
the creation of the DBI object, our concern is that this "eval cleanup
failure" may be leaving resources behind on the system ???

Your comments please.

Regards

Dave Mullen