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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
> >> > >
> >>
> >
>
>
