RE: Apparent memory leak in DBI v1.30

[Jeff Urlwin]

I don't know if this program is sophisticated or not, but...

Note that I tried to stay away from arrays, etc, to avoid potential
additional allocations.  Maybe a dumb script, but...

assumes the DBD::ODBC tests have been run and the PERL_DBD_TEST table is
there with COL_A as an integer and COL_C as a long(ish) char column.

#!perl -w

use Devel::Leak;
use DBI qw(:sql_types);
use strict;

my $insert_value = 0;
my $long = "a" x 1000;

sub connecttest {
   my $dbh = DBI->connect();

   $dbh->disconnect;
}

sub preparetest {
   my $dbh = DBI->connect();
   $dbh->{LongReadLen} = 800;
   my $sth=$dbh->prepare("select * from PERL_DBD_TEST");
   my @row;
   $sth->execute;
   while (@row = $sth->fetchrow_array) {
   }

}

sub inserttest ($) {
   my $delete = shift;


   my $dbh = DBI->connect();
   $dbh->{LongReadLen} = 1500;
   if ($delete) {
      $dbh->do("delete from perl_dbd_test");
   }
   my $sth=$dbh->prepare("insert into PERL_DBD_TEST (COL_A, COL_C) values
($insert_value, ?)");
   my @row;
   $sth->bind_param(1, $long, SQL_LONGVARCHAR);
   $sth->execute;
   $insert_value++;
}

sub selecttest {
   my $dbh = DBI->connect();
   $dbh->{LongReadLen} = 1500;
   my $sth=$dbh->prepare("select COL_A, COL_C FROM PERL_DBD_TEST order by
col_a");
   my @row;
   $sth->execute;
   while (@row = $sth->fetchrow_array) {
   }

}

my $handle;
my $i =0;

my $count;
my $count2;
my $count3;
my $count4;
my $count5;
my $count6;
my $count7;
my $count8;

$count = Devel::Leak::NoteSV($handle);
$i = 0;
while ($i < 100) {
   connecttest;
   $i++;
}

$count2 = Devel::Leak::CheckSV($handle);
$count2 = Devel::Leak::NoteSV($handle);

preparetest;

$count3 = Devel::Leak::CheckSV($handle);
$count3 = Devel::Leak::NoteSV($handle);

$i = 0;
while ($i < 100) {
   preparetest;
   $i++;
}

$count4 = Devel::Leak::CheckSV($handle);
$count4 = Devel::Leak::NoteSV($handle);

inserttest(1);

$count5 = Devel::Leak::CheckSV($handle);
$count5 = Devel::Leak::NoteSV($handle);

$i = 0;
while ($i < 100) {
   inserttest(0);
   $i++;
}
$count6 = Devel::Leak::CheckSV($handle);
$count6 = Devel::Leak::NoteSV($handle);

selecttest;

$count7 = Devel::Leak::CheckSV($handle);
$count7 = Devel::Leak::NoteSV($handle);
$i = 0;
while ($i < 100) {
   selecttest;
   $i++;
}

$count8 = Devel::Leak::CheckSV($handle);
# $count8 = Devel::Leak::NoteSV($handle);

print "$count, $count2, $count3, $count4, $count5, $count6, $count7,
$count8\n";
Jeff

> -----Original Message-----
> From: Tim Bunce [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 29, 2002 4:39 PM
> To: Jeff Urlwin
> Cc: Gary Gauthier; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Apparent memory leak in DBI v1.30
>
>
> On Mon, Jul 29, 2002 at 09:52:33AM -0400, Jeff Urlwin wrote:
> > Gary -- it's more likely in DBD::ODBC than DBI.
>
> But not by much :)
>
> Probably worth trying it with an older version of the DBI as a check.
>
> > If you can create a
> > self-contained sample, it would be greatly appreciated and
> looked at faster
> > :)
>
> Yes, a _small_ simple self-contained example script is a big help.
> Copy the problem script and keep cutting away at it till the leak
> goes away, or it gets so small it's obvious where the problem is.
>
> > You didn't indicate if the loop is a set of inserts, queries,
> etc.  Does it
> > have longs?  What's the LongReadLen value?
> >
> > If you can, include a drop table and create table statement at the top,
> > insert test data, then run through something similar to your loop that's
> > leaking.
> >
> > Tim -- if you have suggestions as to how to approach this with
> Perl, I'd be
> > very appreciative!
>
> The "keep making the sample code smaller" approach usually works well.
>
> The Devel::Leak module may be useful:
> http://search-beta.cpan.org/author/NI-S/Devel-Leak-0.02/Leak.pm
>
> Another approach is to let the process grow huge (many MB) then
> core dump it and look through the binary (using less or other viewer
> that can read binary) and look for clues/patterns in the leaked memory.
>
> Tim.
>
> > Regards,
> >
> > Jeff
> > >
> > > Tim;
> > >
> > > I'm using DBI v1.30 and DBD-ODBC v0.43. My database(SQL2000) app
> > > is invoked about 40,000 times over the course of about 2 days to
> > > generate test scripts for some automated test equipment. I've
> > > been noticing the memory usage creeping up over the course of the
> > > runs (up to  Meg per loop at times). I have seen a report on the
> > > developers mail list regarding this problem in recent versions.
> > > Have you done any tests to check for leaks, or have you had any
> > > reports of leaks? Perhaps there is some peculiarity of SQL2000,
> > > that requires explicit releasing of handles or buffer space?
> > >
> > > Any help would be appreciated.
> > >
> > > Regards;
> > > Gary
> > >
> >
> >
>