[Pdx-pm] Memory leak issue in DBI?

[David Brewer]

I am having a memory leak issue that seems to involve DBI -- I've
already posted it on perl.dbi.users, but I thought I'd also see if
anyone here has run into this problem before.

First off, a brief summary.  I am working on a large website project
using Apache::ASP / mod_perl.  This is on Windows servers, so the Perl
version is ActivePerl 5.8.7 (build 815).  In our testing we we noticed
that the Apache process just kept growing, and growing, and growing.
After an extensive hunt for the culprit I believe I've narrowed it
down to DBI.

In fact, I've been able to construct a relatively simple test script
which demonstrates the problem.  It does nothing but connect to a
database, execute a query, look at the column names, and then close
the database connection.  I think that rules out any responsibility
for this problem lying with mod_perl, Apache::ASP, or any of the many
other modules I am using on this project.  My own code probably has a
few small leaks which I am trying to track down, but the bulk of the
problem is demonstrated by this test script:

##################################################
use strict;
use warnings;
use DBI;

my $dsn = qq{DBI:ODBC:driver={SQL
Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PASSWORD;};
my $options = { RaiseError => 1 } ;

# in reality this is a enormous query involving table variables
# in MSSQL -- excluded here for simplicity.  I can include
# it if you want to see it.
my $sql = '';

for my $i (1..50) {
    print "Executing iteration $i... \n";
    my $dbh = DBI->connect($dsn, $options);
    $dbh->{LongReadLen} = 20000;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->{NAME};
    $sth->finish;
    $dbh->disconnect();
    undef($dbh);
    sleep(1);

}
##################################################

Some notes about this script:

* I am connecting to an MSSQL database.
* The query I am running is not included in the example because it is
huge and complicated.  It makes use of table variables and the result
is about 10 rows, each of which has 22 columns.  Several of the
columns contain long text.
* It seems to be leaking about 144k of memory per trip through the
loop.  The same loop leaks about 27.5k each time if I use the default
'LongReadLen', so it may be somehow related to column size.
* I'm not fetching any data after executing the query, just touching
$sth->{NAME} to force it to find the column names. If I comment the
line with $sth->{NAME}, then the leak becomes so small as to be
negligible.

The way I am computing the size of the leak in this case is running a
version of the script where the loop executes once, then looking at
the memory taken up by the perl process.  Then I execute a version of
the script where the loop executes N times, subtract the memory taken
in the first test from the memory taken by the second test, and divide
by N-1 to get the average memory leaked per pass.

I have tested this same operation using Apache::Leak and Devel::Leak
to try to get more detailed information about what, specifically, is
leaking.  I wasn't able to figure out where the leak is happening but
I did determine that 4 SVs are getting leaked per iteration.

I am using DBI 1.50 with DBD::ODBC 1.13.  I am using persistent
database connections
via the Apache::DBI module.  I am using ActivePerl 5.8.7.

Does this issue ring any bells for anyone out there?  I've been
fighting with it on and off for several days now and I'm not sure
where to go with it next...

Thanks for any help,

David Brewer