DBI Module.

From: Dave Mullen - Marikina Cgi (dm_at_sia.nu)
Date: 06/30/04 

To: <dbi-users@perl.org>
Date: Wed, 30 Jun 2004 15:19:15 +0200

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

Relevant Pages

  • Re: Rows returned are out of sync with the request.
    ... When I make a call to an Oracle 10g database using DBI and SQLRelay ... (for connection pooling) ... I cannot switch to direct connections across the board ...
    (perl.dbi.users)
  • many database connections causes Cant locate Carp/Heavy.pm
    ... I am purposefully attempting to open ~1000 database connections using ... DBI and DBD-Oracle. ... After about 255 connections I am getting ...
    (perl.dbi.users)
  • Measuring wait time for openning a new connection
    ... using DBI) which opens many connections to ... the database. ... Will I see the effect of the connections on the wait events? ...
    (comp.databases.oracle.server)
  • RE: [PHP] Re: Understanding persistent connections with oci8
    ... persistent connections per server and the timeout interval. ... may outside of php used. ... that's an over-simplification of the purpose of "persistent" ...
    (php.general)
  • Re: timout issues in cube processing SSAS 2005
    ... In BIDS double click on your data sourceand change parameter "Maximum Number of connections" to something smaller, ... All this will increase your processing time, buy you might avoid timeout. ... exec sp_configure @configname='remote query timeout ',@configvalue ... Analysis Services 2005 is on the same box as the SQL Server 2005 DB. ...
    (microsoft.public.sqlserver.olap)