[Dbix-class] memory leak - found

[Alan Humphrey]

Found it.  There's good news and there's bad news.  The good news: most
people won't be affected by the leak.   The bad news: it's not just me.

 

The problem is in DBI.pm, specifically in the sth method:

 

sub sth {

  my ($self, $sql) = @_;

  # 3 is the if_active parameter which avoids active sth re-use

  return $self->dbh->prepare_cached($sql, {}, 3);

}

 

The prepare_cached call means that the statement handle is saved in
$self->dbh for that particular sql statement.  If the statement is used
again the existing statement handle is returned from the cache.  Using the
cache is where the leak occurs.  It isn't cleared until dbh is destroyed.

 

So why was I affected and most people won't be?  I'm using the FreeTDS
library for low level access to the database.  That library doesn't support
bind variables, so I subclassed DBI.pm to override the _execute method so I
could build sql statements without bind variables.

 

That's all well and good, but it meant that the $sql passed to sth was
always different.  There were no hits in the cache so a new statement handle
was created for each statement.  The solution: override sth and use
dbh->prepare instead of dbh->prepare_cached:

 

sub sth {

  my ($self, $sql) = @_;

  return $self->dbh->prepare( $sql, {} );

}

 

Bottom line: there is a leak but as long as your driver supports bind
variables you are not likely to be affected.

 

- Alan

 

-----Original Message-----
From: dbix-class-bounces at lists.rawmode.org
[mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Alan Humphrey
Sent: Wednesday, March 15, 2006 9:40 AM
To: dbix-class at lists.rawmode.org
Subject: [Dbix-class] memory leak

 

I've found a memory leak in DBIx::Class that I'm not really sure how to
track down further.  I'm hoping someone with more familiarity with the
internals can give me a pointer as to where to look.  Here are the
particulars:

 

OS: NetBSD 3.0

DBIx: 0.05007

 

Sample program:

 

#!/usr/pkg/bin/perl -w

use lib "/home/alanh/birdweb/trunk/BirdWeb-Admin/lib";

use BirdWeb::Admin;

my $staging = "BirdWeb::Admin::Model::Production";

#my $staging = "BirdWeb::Admin::Model::Staging";

 

my $res = $staging->resultset('Birds')->search({id => { '>' => 450} } );

my $counter =1;

my $count = $res->count();

while ( my $row = $res->next ) {

      print  "\t$count\t".$counter++. "\n";

      print $row->default_bird_common_name_id->bird_common_name . "\n";

}

warn "end of program\n";

 

 

With the DBI TraceLevel set to 1 here's the relevant part of the stderr
output:

 

 

    <- FETCH('Driver')= DBI::dr=HASH(0x8fb1294) at Limit.pm line 301

    <- FETCH('Driver')= DBI::dr=HASH(0x8fb1294) at Limit.pm line 338

    <- FETCH('Active')= 1 at DBI.pm line 276

    <- ping= 1 at DBI.pm line 276

    <- prepare_cached('SELECT me.id, me.bird_id, me.bird_common_name,
me.common_name_notes FROM bird_common_names me WHERE ( ( ( me.id = ? ) ) )'
HASH(0x91824c0) ...)= DBI::st=HASH(0x96e8af8) at DBI.pm line 448

    <- execute('450')= 1 at DBI.pm line 386

    <- fetchrow_array= ( '450' '484' 'Painted Bunting' undef ) [4 items]
row1 at Cursor.pm line 36

    <- FETCH('Active')= 1 at Cursor.pm line 66

    <- finish= 1 at Cursor.pm line 66

end of program

!   <- DESTROY(DBI::st=HASH(96e8af8))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(95cefd8))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(964d774))= undef during global destruction

!   <- DESTROY(DBI::db=HASH(9679f54))= undef during global destruction

 

 

Notice that the statement handles aren't destroyed until after the end of
the program.  The first one in the list should have been destroyed at the
bottom of the while loop (before "end of program" was output).

 

Why does this matter?  It depends on the DBI driver you're using.  In the
example above the MySQL driver is used and it reuses the statement handle.
The same program executed against a MSSQL database using the Sybase driver
has stderr output that looks like this:

 

    <- FETCH('Driver')= DBI::dr=HASH(0x8fb1294) at Limit.pm line 301

    <- FETCH('Driver')= DBI::dr=HASH(0x8fb1294) at Limit.pm line 338

    <- FETCH('Active')= 1 at DBI.pm line 276

    <- ping= 1 at DBI.pm line 276

    <- quote(450)= ''450'' at NoBindStorage.pm line 18

    <- FETCH('Active')= 1 at DBI.pm line 276

    <- ping= 1 at DBI.pm line 276

    <- prepare_cached('SELECT me.id, me.bird_id, me.bird_common_name,
me.common_name_notes FROM bird_common_names me WHERE ( ( ( me.id = '450' ) )
)' HASH(0x9768cfc) ...)= DBI::st=HASH(0x98738c4) at DBI.pm line 

448

    <- execute= -1 at NoBindStorage.pm line 31

    <- fetchrow_array= ( 450 484 'Painted Bunting' undef ) [4 items] row1 at
Cursor.pm line 36

    <- FETCH('Active')= 1 at Cursor.pm line 66

    <- finish= 1 at Cursor.pm line 66

end of program

!   <- DESTROY(DBI::st=HASH(9873534))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(98730c0))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9873618))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(98731a4))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(98736fc))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9873288))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(98737e0))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(987336c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(98738c4))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9873450))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768510))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(976809c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(97685f4))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768180))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(97686d8))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768264))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(97687bc))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768348))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(97688a0))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(976842c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768984))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768a68))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768b4c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768c30))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768d14))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768df8))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768edc))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9768fc0))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9743f9c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9750678))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(9683d2c))= undef during global destruction

!   <- DESTROY(DBI::st=HASH(963ee40))= undef during global destruction

!   <- DESTROY(DBI::db=HASH(967fe1c))= undef during global destruction

 

 

As you can see, that driver doesn't reuse the statement handle and memory is
leaked.

 

I looked at the Cursor.pm code and it looks like pains are taken to try and
delete the statement handle, but clearly something is maintaining a
reference to it.

 

Anybody have any ideas?  This leak makes it impossible to use DBIx::Class
for batch operations against my production database.

 

Thank you!

 

- Alan

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060315/973ddf79/attachment-0001.htm