|
Please see below ...
-------Original Message-------
Date: Wednesday, July 09, 2003 08:02:51
Subject: Re: DBD::Informix Slow Disconnect
Paul Reilly wrote:
>> I want to connect and disconnect to several databases on 'Informix
>> Dynamic Server 2000 Version 9.21.UC5'
>>
>> However, it is running very slowly because I am calling
>>
>> $dbh->disconnect
>>
>> after each connection and it seems to take a very long time.
>>
>>
>> I am using DBI 1.37 and the latest version of DBD::Informix.
>>
>> Any ideas ??
>Succinctly - no.
>I've had such reports before, but not been able to get much in the way
>of evidence to back it up. I've also been unable to reproduce the
>problem myself.
In a way I'm glad. There's always a danger with these lists of asking a very easy question and embarrasing yourself :-)
>Which version of ESQL/C? Which platform? Which version of Perl?
My system stats are as follows
This is perl, v5.8.0 built for sun4-solaris
Informix Dynamic Server 2000 Version 9.21.UC5
IBM Informix CSDK Version 2.81, IBM Informix-ESQL Version 9.53.UC1
DBI 1.37
IBM Informix Database Driver for Perl Version 2003.04 (2003-03-05)
>How long is a very long time - milliseconds, seconds, minutes, hours?
I should add that the problem is not there all the time, rather about half the time.
Sometimes $dbh->disconnect() works immediatedly, and sometimes takes up to about 50 seconds
>What sort of database connection are you using - olipcshm, oltlitcp, olsoctcp, olipcstr?
onipcshm
>Can you run with SQLIDEBUG=2:dbdix and then run sqliprint on the resulting dbdix_* file.
>This would show what is happening at the DBI/DBD::Informix level - with time stamps, I seem to >recall. It might therefore show what is causing the delay - in terms of which SQL statement is >taking its time.
I used the following script for testing purposes
//////////////////////////////////////////////
#!/usr/local/bin/perl
use DBI;
$dsn = "DBI:Informix:sysmaster";
print "DBG - 1 Connecting $dsn ... \n";
my $dbStart = DBI->connect($dsn, '', '')
or die "Can't connect to Database : $DBI::errstr \n";
print " Connected <br> \n";
push (@db_used, "stores");
push (@db_used, "dimonex");
$sDB = $dbStart->prepare("select name from sysdatabases");
$sDB->execute();
my @DBrows;
while ( $DBrow = $sDB->fetchrow_array() ) {
$indOfCheck = -1;
$DBrow =~ s/\W//gs;
$indOfCheck = &indexof($DBrow, @db_used);
if($indOfCheck > -1){
push (@DBrows, $DBrow);
}
}
$sDB->finish();
print " Disconnecting ... \n";
$dbStart->disconnect();
print "Disconnected <br> <br> <br> \n";
foreach $DBrow (@DBrows) {
$DBrow =~ s/\W//gs;
my $dsnDbh ="DBI:Informix:$DBrow";
print "DBG - 2 Connecting $dsnDbh ... \n";
$dbh = DBI->connect($dsnDbh, '', '')
or die "Can't connect to Database : $DBI::errstr \n";
print "Connected <br> \n";
# $sth = $dbh->prepare("select systables.tabname, syscolumns.colname, #syscolumns.tabid from syscolumns, systables where syscolumns.tabid > 99 and systables.tabid = #syscolumns.tabid order by tabname, colname ");
#
#
# $sth->execute();
# while ( ($tabName, $colName, $colId) = $sth->fetchrow_array() ) {
# $tabName =~ s/\W//gs;
# $colName =~ s/\W//gs;
# $colId =~ s/\W//gs;
#
# print "$DBrow,$tabName,$colName,$colId \n";
# }
# $sth->finish();
#
print "DBG - 2 Disconnecting ... \n";
$dbh->disconnect();
print "Disconnected <br> <br> \n";
print " \n \n";
}
sub indexof {
local($i);
for($i=1; $i <= $#_; $i++) {
if ($_[$i] eq $_[0]) { return $i - 1; }
}
return -1;
}
/////////////////////////////////////////////
I ran this a few times until I got an example of an immediate disconnect and of a delayed disconnect. The only difference between the outputs were the timestamps.
On the output that failed , it looked sa if the whoe thing took less than 2.5 seconds whereas it actually took about 3 minutes.
This portion of output is between one disconnect and the following connect and took about 50 seconds
///////////////////////////////
S->C (2) Time: 2003-07-09 12:18:50.87513
SQ_EXIT
C->S (30) Time: 2003-07-09 12:19:41.87095
SQ_DISCONNECT
"sysmaster" [9]
"x_000000000" [11]
S->C (4) Time: 2003-07-09 12:19:42.83955
SQ_INTERNALVER
Internal Version Number: 307
C->S (190) Time: 2003-07-09 12:19:42.84194
SQ_INFO
INFO_ENV
Name Length = 12
Value Length = 94
"DBTEMP"="/tmp"
"SHELL"="/bin/sh"
"TZ"="GB"
"SUBQCACHESZ"="10"
"PATH"="/usr/bin::/usr/local/mysql/bin:/usr/local/bin:/usr/local/sbin:/usr/ccs/bin:/apps/informix/bin
"
"NODEFDAC"="no"
INFO_DONE
SQ_EOT
S->C (2) Time: 2003-07-09 12:19:42.84257
SQ_EOT
C->S (26) Time: 2003-07-09 12:19:42.84265
SQ_CONNECT
"dimonex" [6]
"x_000000001" [11]
//////////////////////////////
| 
IncrediMail - Email has finally evolved - Click Here