Re: Possible memory leak using $sth->{NAME} ?

Can you get DBD::mysql to leak in the same way ($sth->{NAME})?
I need to be able to duplicate the problem and I can't do that
easily with ODBC or ADO.

Tim.

On Tue, Jun 06, 2006 at 12:14:36PM -0700, David Brewer wrote:
I've tried this with a couple of different drivers now -- DBD::ADO and
DBD::mysql. In both cases the same simple connect and disconnect
routine seems to leak 1 SV per execution.

The ADO example is exactly the same as the previous example, except I
substituted "DBI:ADO" for "DBI:ODBC". The MySQL example is a little
different and is included below as 'Example 1'.

I'm actually not too concerned about that particular leak -- it seems
to be a fairly insignificant amount of memory. It's just the simplest
example of a leak I could construct and I was hoping that it might be
a symptom of some mistake that I might be making. The problem that
led me to it is a much larger leak that occurs when try to get the
column names from a very complicated query (either directly using
$sth->{NAME} or indirectly by calling $sth->fetchrow_hashref).

In the case of one query I am using 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 is 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. You
can see an example of this loop as Example 2, below.

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 also done the same test using Apache::Leak to measure SVs being
leaked, and there are apparently 4 SVs leaked per pass. They just
happen to be relatively large ones, I guess. :-)

I am very willing to run any other tests you might suggest. I am
thoroughly mystified at this point and eager to get to the bottom of
this.

###########################################
# Example 1:
###########################################
use strict;
use warnings;
use DBI;

my $dsn = "DBI:mysql:database=DBNAME;host=DBHOST;port=3306";
my $user = "USER";
my $password = "PASSWORD";

use Apache::Leak;
leak_test {
for (1..50) {
my $dbd = DBI->connect($dsn, $user, $password);
$dbd->disconnect();
undef($dbd);
}
};
###########################################

###########################################
# Example 2
###########################################

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 $dbd = DBI->connect($dsn, $options);
$dbd->{LongReadLen} = 20000;
my $sth = $dbd->prepare($sql);
$sth->execute();
$sth->{NAME};
$sth->finish;
$dbd->disconnect();
undef($dbd);
sleep(1);
}


###########################################


On 6/6/06, David Brewer <david.brewer@xxxxxxxxx> wrote:
Sure, I'd be glad to check into that.

I think Apache::Leak already runs the code twice -- first it runs the
code to make sure that anything that would get cached is already in
memory. Then it measures the memory usage, runs the code again, and
measures the memory usage a final time to determine how much was
leaked.

I made a new script which you can see below. This one does a similar
leak test but repeats it 50 times. leak_test reports 50 SV leaked, so
it seems like it's consistent.

I will start trying some different drivers and report back my results
shortly. Thanks for your response!

David

###################################

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 } ;

use Apache::Leak;
leak_test {
for (1..50) {
my $dbd = DBI->connect($dsn, $options);
$dbd->disconnect();
undef($dbd);
}
};

###################################

On 6/6/06, Tim Bunce <Tim.Bunce@xxxxxxxxx> wrote:
On Mon, Jun 05, 2006 at 07:51:22PM -0700, David Brewer wrote:
OK, I have pared my problem down to a small test script and I've cut
away a lot of the modules I'm using that don't seem to be part of the
issue. The test script is included at the end of this message.

Thanks.

This small script doesn't leak much memory, but it's surprising to me
that it leaks at all. Essentially, I just connect to a database and
then disconnect from it, and Apache::Leak reports that this process
leaks 1 SV. If I add a simple query then Apache::Leak reports I leak
4 SVs.

'leaks' from one-off calls are rarely real leaks, they're often just
internal caching of one kind or another.

Real leaks leak in proportion to the number of calls made. I'd expect
you to be able to say something like "each call to foo leaks N scalars"
(because 100 calls leak X and 101 calls leak X+N).

Can you check for that? And can you also try a different driver or two?

Tim.

I am using DBI 1.50 with DBD::ODBC 1.13. This is on a Windows XP
machine, using ActivePerl 5.8.8 (I was using 5.8.7 previously with the
same issue). I am talking to a MSSQL Server 2000 database.

##################################
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 } ;

use Apache::Leak;
leak_test {
my $dbd = DBI->connect($dsn, $options);
$dbd->disconnect();
};
##################################

On 6/2/06, David Brewer <david.brewer@xxxxxxxxx> wrote:
I am having what appears to a memory leak problem on a mod_perl
project I am working on. On the worst case web page (a search results
page) I am leaking an average of about 160k per page load! I think
I've finally isolated the problem and it appears to be related to DBI.
It's very possible that I am doing something wrong to cause the
problem. :-)

First of all, some version and module information. I am using DBI
1.50 with DBD::ODBC 1.13. I am using persistent database connections
via the Apache::DBI module. The project is using mod_perl and
Apache::ASP.

I isolated the problem by commenting out great swaths of code until
the problem went away, and then slowly adding them back in until it
reappeared. My first thought was that it had something to do with
fetchrow_hashref. I have a loop like this:

while ($row = $sth->fetchrow_hashref) {
# do stuff here
}

All of the functionality inside the loop has been commented, but my
memory leak still happens. However, if I comment the loop entirely,
the leak goes away (well, about 158k of it at least!).

If I replace the loop with something like:

while ($row = $sth->fetchrow_arrayref) {
# do stuff here
}

... no leak. I need to get at some of the column names, though, so I
added this line before the loop:

my $column_names = $sth->{NAME};

... and the leak was back! It stays even if I don't save the column
names into a variable, but just touch them:

$sth->{NAME};

In fact, it even stays if I remove the loop entirely and just include
the line above! Any ideas why this might be happening and what I
could do to fix it or work around it? Is there possibly something I'm
doing wrong here?

My next step is going to be to try to make some kind of simple test
outside of the framework of my web probject that reproduces the same
behavior. I'll post that here when I have it.

Thanks in advance for any insight,

David Brewer





.

Relevant Pages

  • Re: Possible memory leak using $sth->{NAME} ?
    ... > routine seems to leak 1 SV per execution. ... > to be a fairly insignificant amount of memory. ... > of memory per trip through the loop. ... > version of the script where the loop executes once, ...
    (perl.dbi.users)
  • Re: Possible memory leak using $sth->{NAME} ?
    ... The test script is included at the end of this message. ... This small script doesn't leak much memory, ... I am using DBI 1.50 with DBD::ODBC 1.13. ... All of the functionality inside the loop has been commented, ...
    (perl.dbi.users)
  • Re: Possible memory leak using $sth->{NAME} ?
    ... Then it measures the memory usage, runs the code again, and ... leak test but repeats it 50 times. ... > I am using DBI 1.50 with DBD::ODBC 1.13. ... However, if I comment the loop entirely, ...
    (perl.dbi.users)
  • Re: String to VT_BSTR conversion problem
    ... I don't see memory leaks inside the loop. ... The only leak is when you ... which allocates a temporary buffer on the stack using _alloca. ...
    (microsoft.public.vc.atl)
  • Re: long running perl programs & memory untilization
    ... >>all in memory somewhere by mistake? ... >>Post just the loop for us to have a look at. ... If that doesn't leak, then try again, cutting out *absolutely* ...
    (comp.lang.perl.misc)