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

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

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

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.

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.

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=PAS SWORD;};
my $options = { RaiseError => 1 } ;

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

On 6/2/06, David Brewer 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
>

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

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=PAS SWORD;};
> my $options = { RaiseError => 1 } ;
>
> use Apache::Leak;
> leak_test {
> my $dbd = DBI->connect($dsn, $options);
> $dbd->disconnect();
> };
> ##################################
>
> On 6/2/06, David Brewer 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
> >

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

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=PAS SWORD;};
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 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=PAS SWORD;};
> > my $options = { RaiseError => 1 } ;
> >
> > use Apache::Leak;
> > leak_test {
> > my $dbd = DBI->connect($dsn, $options);
> > $dbd->disconnect();
> > };
> > ##################################
> >
> > On 6/2/06, David Brewer 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
> > >
>

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=PAS SWORD;};
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 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=PAS SWORD;};
> 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 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=PAS SWORD;};
> > > my $options = { RaiseError => 1 } ;
> > >
> > > use Apache::Leak;
> > > leak_test {
> > > my $dbd = DBI->connect($dsn, $options);
> > > $dbd->disconnect();
> > > };
> > > ##################################
> > >
> > > On 6/2/06, David Brewer 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
> > > >
> >
>

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=PAS SWORD;};
> 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 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=PA SSWORD;};
> >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 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=PAS SWORD;};
> >> > my $options = { RaiseError => 1 } ;
> >> >
> >> > use Apache::Leak;
> >> > leak_test {
> >> > my $dbd = DBI->connect($dsn, $options);
> >> > $dbd->disconnect();
> >> > };
> >> > ##################################
> >> >
> >> > On 6/2/06, David Brewer 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
> >> > >
> >>
> >
>
>

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

OK, I will attempt to replicate in mysql and get back to you. In the
meantime, I have run my test script in some older versions of DBI to
see if that helps anything. I tried 1.49, 1.48, 1.47, 1.45, 1.41, and
1.38. I saw pretty the same behavior in each version.

Thanks,

David

On 6/6/06, Tim Bunce wrote:
> 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=PAS SWORD;};
> > 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 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=PA SSWORD;};
> > >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 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=PAS SWORD;};
> > >> > my $options = { RaiseError => 1 } ;
> > >> >
> > >> > use Apache::Leak;
> > >> > leak_test {
> > >> > my $dbd = DBI->connect($dsn, $options);
> > >> > $dbd->disconnect();
> > >> > };
> > >> > ##################################
> > >> >
> > >> > On 6/2/06, David Brewer 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
> > >> > >
> > >>
> > >
> >
> >
>

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

I have not yet been able to replicate in mysql but I spent some time
paring down the query to the minimum version that causes the memory
leak. The results are interesting and I think may point toward
DBD::ODBC being the issue. I don't understand the internals well
enough to be sure of that, though.

The basic idea behind the original query was to do a complicated
search, store the resulting distinct ids into a table variable called
@search_hits, and then use that table joined to some other tables to
select the final results. I cut out everything that I could remove
while still seeing the leak. Here is the simplest query I could come
up with that exhibits the behavior:

################################################
DECLARE @search_hits TABLE (
ObjectID int DEFAULT(0)
);

INSERT INTO @search_hits (ObjectID) VALUES (1);

SELECT TOP 0
*
FROM
Objects;
################################################

Basically, it has to have the following properties to exhibit the large leak:

1) You must create a table variable.
2) You must insert at least one row into the table variable.
3) You must select from a table that contains "text" columns. The
more columns you select, especially those that contain long text, the
greater the leak. Note that you don't have to select ANY rows in
order to cause the leak!
4) After executing the query, you must do something which triggers the
lookup of column information. I've found that either $sth->{NAME} or
$sth->fetchrow_hashref() will cause the behavior.

If any of these conditions is not met in the query, the leak does not
happen. I'm guessing this means it will not be reproducable in mysql,
as if I recall correctly mysql doesn't have an equivalent to a table
variable.

Do DBI drivers typically store information about a query aside from
information about the actual result of the query? My original
assumption was that if you did a complicated query, it was only the
results of the query that would make a difference to the memory usage
of perl. It surprised me to find that creating a table variable,
doing an insertion, and then ignoring that table could have an effect
on memory usage in the perl process.

I've included the entire script for reproducing the problem below,
this time including the SQL. Thanks again!

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

use strict;
use warnings;
use DBI;

my $dsn = qq{DBI:ODBC:driver={SQL
Server};Server=SERVERNAME;database=DBNAME;uid=DBUSER;pwd=PAS SWORD;};

# 'Objects' must contain several columns of type 'text' to get
# a substantial leak
my $sql = q{
DECLARE @search_hits TABLE (
ObjectID int DEFAULT(0)
);

INSERT INTO @search_hits (ObjectID) VALUES (1);

SELECT TOP 0
*
FROM
Objects;
};

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

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

On 6/6/06, David Brewer wrote:
> OK, I will attempt to replicate in mysql and get back to you. In the
> meantime, I have run my test script in some older versions of DBI to
> see if that helps anything. I tried 1.49, 1.48, 1.47, 1.45, 1.41, and
> 1.38. I saw pretty the same behavior in each version.
>
> Thanks,
>
> David
>
> On 6/6/06, Tim Bunce wrote:
> > 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=PAS SWORD;};
> > > 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 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=PA SSWORD;};
> > > >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 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=PAS SWORD;};
> > > >> > my $options = { RaiseError => 1 } ;
> > > >> >
> > > >> > use Apache::Leak;
> > > >> > leak_test {
> > > >> > my $dbd = DBI->connect($dsn, $options);
> > > >> > $dbd->disconnect();
> > > >> > };
> > > >> > ##################################
> > > >> >
> > > >> > On 6/2/06, David Brewer 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
> > > >> > >
> > > >>
> > > >
> > >
> > >
> >
>

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

I'm still not able to replicate on mysql. However, I have created a
test script (included below) which, given access to an empty mssql
database, should do everything necessary to demonstrate the behavior.

Please let me know if there is any further information I could provide
or tests I could create that might be helpful.

Thanks,

David Brewer

############################################################ ###############
# mssql_leak.t
#
# Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
# a MSSQL 2000 database.
#
# To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
# access to an MSSQL database. A table called 'leak_test' will be created
# and destroyed in the database. See the CONFIGURATION section below to set
# up the desired connection.
#
# In order to reproduce the leak, you must meet four criteria:
#
# 1) In your query, you must create a table variable.
# 2) You must insert at least one row into the table variable. After that,
# you can completely ignore the table variable!
# 3) You must select from a table that contains 'text' columns. The more
# text columns you select, the larger the leak. Note that you don't
# actually have to select any rows to cause the leak!
# 4) After executing the query, you must do something which triggers the
# lookup of column information. I've found that either $sth->{NAME} or
# $sth->fetchrow_hashref() will cause the leak, for instance.
#
# In addition, the 'LongReadLen' property of the database handle is somehow
# related because the higher this number, the greater the leak.
#
# Once you've verified that the leak is occuring using the code below, you
# can try commenting out various lines to see how it affects things.
# The two clearest examples are commenting out the insertion into the
# table variable, and commenting out the $sth->{NAME} line.
#
# I wasn't able to figure out how to get at the memory usage of perl
# programmatically on Windows, so rather than writing tests to test the
# memory usage, I just made a loop that demonstrates the leak. It pauses
# after the first iteration and then again at the end so you can observe the
# memory used by perl using the task manager or 'Process Explorer' from
# sysinternals.com.
#
# Author: David Brewer, Second Story Interactive
# Date: June 7, 2006
############################################################ ###############

use strict;
use warnings;

############################################################ ###############
# CONFIGURATION
############################################################ ###############

# Database connection
my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
my $user = "test";
my $pass = "test";

# number of times to perform the leaky query in the loop
my $leak_iterations = 1200;

# seconds to pause at beginning and end of loop to permit memory observation
my $pause = 7;

# LongReadLen value to use on the database handle; this is somehow related
# to the leak because the greater this value, the greater the leak.
my $LongReadLen = 20000;


############################################################ ###############
# SETUP
############################################################ ###############

use Test::More tests => 8;

# Verify we have the correct versions of the modules
BEGIN {
use_ok( 'DBI 1.50' );
use_ok( 'DBD::ODBC 1.13' );
}

# Prepare the database by creating the test table. Drop it first if it
# already exists.

my $create_sql = q{
IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
CREATE TABLE [dbo].[leak_test] (
ID int NOT NULL,
LongText1 text,
LongText2 text,
LongText3 text,
LongText4 text,
LongText5 text,
PRIMARY KEY (ID)
)
};

my $dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($create_sql), 'Created leak_test table');
ok($dbh->disconnect, 'Disconnected from database');


############################################################ ###############
# THE TEST
############################################################ ###############

my $leaky_sql = q{
DECLARE @table_variable TABLE (
TestInteger int DEFAULT(0)
);

-- Commenting the line below prevents the leak!
INSERT INTO @table_variable (TestInteger) VALUES (1);

SELECT TOP 0
*
FROM
leak_test;
};

my $iterations = 1200;
print(qq{
We will run the leaky query $leak_iterations times, pausing $pause seconds
after the first run so you can observe the memory usage.
});

$dbh = DBI->connect($dsn, $user, $pass);
$dbh->{LongReadLen} = $LongReadLen;

for my $i (1..$leak_iterations) {
# print a dot for each run through the query
print "\n" if ($i % 60 == 1);
print ".";

# run our leaky query...
my $sth = $dbh->prepare($leaky_sql);
$sth->execute();

# Getting column names triggers link; you can comment out to verify.
my $names = $sth->{NAME};

# clean up
$sth->finish;
undef $sth;

# pause after the first run so you can observe the memory usage
sleep($pause) if ($i == 1);
}

$dbh->disconnect();
undef $dbh;

print(qq{
\nPausing $pause seconds so you can observe final memory usage.
});
sleep($pause);


############################################################ ###############
# CLEANUP
############################################################ ###############

# Remove the leak_test table
my $cleanup_sql = q{
IF OBJECT_ID('leak_test','U') IS NOT NULL DROP TABLE leak_test;
};

$dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($cleanup_sql), 'Removed leak_test table');
ok($dbh->disconnect, 'Disconnected from database');
############################################################ ###############

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

At this point I'm pretty sure it's a bug in DBD::ODBC, so I'll let
Jeff Urlwin pick up from here.

Tim.

On Wed, Jun 07, 2006 at 11:52:15AM -0700, David Brewer wrote:
> I'm still not able to replicate on mysql. However, I have created a
> test script (included below) which, given access to an empty mssql
> database, should do everything necessary to demonstrate the behavior.
>
> Please let me know if there is any further information I could provide
> or tests I could create that might be helpful.
>
> Thanks,
>
> David Brewer
>
> ############################################################ ###############
> # mssql_leak.t
> #
> # Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
> # a MSSQL 2000 database.
> #
> # To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
> # access to an MSSQL database. A table called 'leak_test' will be created
> # and destroyed in the database. See the CONFIGURATION section below to set
> # up the desired connection.
> #
> # In order to reproduce the leak, you must meet four criteria:
> #
> # 1) In your query, you must create a table variable.
> # 2) You must insert at least one row into the table variable. After that,
> # you can completely ignore the table variable!
> # 3) You must select from a table that contains 'text' columns. The more
> # text columns you select, the larger the leak. Note that you don't
> # actually have to select any rows to cause the leak!
> # 4) After executing the query, you must do something which triggers the
> # lookup of column information. I've found that either $sth->{NAME} or
> # $sth->fetchrow_hashref() will cause the leak, for instance.
> #
> # In addition, the 'LongReadLen' property of the database handle is somehow
> # related because the higher this number, the greater the leak.
> #
> # Once you've verified that the leak is occuring using the code below, you
> # can try commenting out various lines to see how it affects things.
> # The two clearest examples are commenting out the insertion into the
> # table variable, and commenting out the $sth->{NAME} line.
> #
> # I wasn't able to figure out how to get at the memory usage of perl
> # programmatically on Windows, so rather than writing tests to test the
> # memory usage, I just made a loop that demonstrates the leak. It pauses
> # after the first iteration and then again at the end so you can observe the
> # memory used by perl using the task manager or 'Process Explorer' from
> # sysinternals.com.
> #
> # Author: David Brewer, Second Story Interactive
> # Date: June 7, 2006
> ############################################################ ###############
>
> use strict;
> use warnings;
>
> ############################################################ ###############
> # CONFIGURATION
> ############################################################ ###############
>
> # Database connection
> my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
> my $user = "test";
> my $pass = "test";
>
> # number of times to perform the leaky query in the loop
> my $leak_iterations = 1200;
>
> # seconds to pause at beginning and end of loop to permit memory observation
> my $pause = 7;
>
> # LongReadLen value to use on the database handle; this is somehow related
> # to the leak because the greater this value, the greater the leak.
> my $LongReadLen = 20000;
>
>
> ############################################################ ###############
> # SETUP
> ############################################################ ###############
>
> use Test::More tests => 8;
>
> # Verify we have the correct versions of the modules
> BEGIN {
> use_ok( 'DBI 1.50' );
> use_ok( 'DBD::ODBC 1.13' );
> }
>
> # Prepare the database by creating the test table. Drop it first if it
> # already exists.
>
> my $create_sql = q{
> IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
> CREATE TABLE [dbo].[leak_test] (
> ID int NOT NULL,
> LongText1 text,
> LongText2 text,
> LongText3 text,
> LongText4 text,
> LongText5 text,
> PRIMARY KEY (ID)
> )
> };
>
> my $dbh = DBI->connect($dsn, $user, $pass);
> isa_ok($dbh, 'DBI::db');
> ok($dbh->do($create_sql), 'Created leak_test table');
> ok($dbh->disconnect, 'Disconnected from database');
>
>
> ############################################################ ###############
> # THE TEST
> ############################################################ ###############
>
> my $leaky_sql = q{
> DECLARE @table_variable TABLE (
> TestInteger int DEFAULT(0)
> );
>
> -- Commenting the line below prevents the leak!
> INSERT INTO @table_variable (TestInteger) VALUES (1);
>
> SELECT TOP 0
> *
> FROM
> leak_test;
> };
>
> my $iterations = 1200;
> print(qq{
> We will run the leaky query $leak_iterations times, pausing $pause seconds
> after the first run so you can observe the memory usage.
> });
>
> $dbh = DBI->connect($dsn, $user, $pass);
> $dbh->{LongReadLen} = $LongReadLen;
>
> for my $i (1..$leak_iterations) {
> # print a dot for each run through the query
> print "\n" if ($i % 60 == 1);
> print ".";
>
> # run our leaky query...
> my $sth = $dbh->prepare($leaky_sql);
> $sth->execute();
>
> # Getting column names triggers link; you can comment out to verify.
> my $names = $sth->{NAME};
>
> # clean up
> $sth->finish;
> undef $sth;
>
> # pause after the first run so you can observe the memory usage
> sleep($pause) if ($i == 1);
> }
>
> $dbh->disconnect();
> undef $dbh;
>
> print(qq{
> \nPausing $pause seconds so you can observe final memory usage.
> });
> sleep($pause);
>
>
> ############################################################ ###############
> # CLEANUP
> ############################################################ ###############
>
> # Remove the leak_test table
> my $cleanup_sql = q{
> IF OBJECT_ID('leak_test','U') IS NOT NULL DROP TABLE leak_test;
> };
>
> $dbh = DBI->connect($dsn, $user, $pass);
> isa_ok($dbh, 'DBI::db');
> ok($dbh->do($cleanup_sql), 'Removed leak_test table');
> ok($dbh->disconnect, 'Disconnected from database');
> ############################################################ ###############
>
>