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

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: MDAC memory leak
    ... Most libraries place the decision of when to free ... There's a capability of breaking on a particular memory allocation, ... leak 500 objects, on the second test I leak 3, because I fixed the bug). ... "App shows memory leak on some machines." ...
    (microsoft.public.vc.mfc)
  • Re: MDAC memory leak
    ... Also when we used some of the memory leak tools suggested on microsoft site ... A mutex is a considerably less efficient synchronization ... "App shows memory leak on some machines." ...
    (microsoft.public.vc.mfc)
  • Re: Possible memory leak using $sth->{NAME} ?
    ... I'm actually not too concerned about that particular leak -- it seems ... to be a fairly insignificant amount of memory. ... of memory per trip through the loop. ... use DBI; ...
    (perl.dbi.users)
  • Re: Help! define_method leaking procs...
    ... >>> VmSize value. ... there is no evidence yet that ruby is not ... I am not saying there is no memory leak ...
    (comp.lang.ruby)