[Dbix-class] Memory usage, something hanging on?

[Carl Franks]

On 08/01/07, Matt S Trout <dbix-class at trout.me.uk> wrote:
>
> On 8 Jan 2007, at 15:51, Adam Sjøgren wrote:
>
> >   Hi.
> >
> >
> > In a Catalyst application I am working on, in which we use
> > DBIx::Class, server.pl just grew to use over 300MB ram, staying that
> > size from then on.
> >
> > I've tried to replicate what happens, and hope someone will comment on
> > whether this is expected behaviour, or something else.
> >
> > To test this, I create a testdb, and make a simple table:
> >
> >  CREATE TABLE testtable (
> >    id serial,
> >    title text,
> >    fill text,
> >    primary key (id)
> >  );
> >
> > TestDB.pm:
> >
> >  package TestDB;
> >
> >  use strict;
> >  use warnings;
> >  use base qw/DBIx::Class::Schema/;
> >
> >  __PACKAGE__->load_classes();
> >
> >  1;
> >
> > TestDB/Testtable.pm:
> >
> >  package TestDB::Testtable;
> >
> >  use strict;
> >  use warnings;
> >
> >  use base 'DBIx::Class';
> >
> >  __PACKAGE__->load_components('PK::Auto', 'Core');
> >  __PACKAGE__->table('testtable');
> >  __PACKAGE__->add_columns(qw(id title fill));
> >  __PACKAGE__->set_primary_key('id');
> >
> >  1;
> >
> > I fill this table with rather large records:
> >
> >  #!/usr/bin/perl
> >
> >  use strict;
> >  use warnings;
> >
> >  use TestDB;
> >
> >  my $schema=TestDB->connect("dbi:Pg:dbname=testdb;host=localhost",
> > "user", "pass", { AutoCommit=>0 });
> >
> >  my $long_string='afahabada' x 20000;
> >
> >  my $i=0;
> >  while ($i<1000) {
> >      $schema->resultset('TestDB::Testtable')->create({
> >
> > title=>'title_' . $i++,
> >                                                       fill=>
> > $long_string,
> >                                                      });
> >  }
> >
> >  $schema->txn_commit;
> >
> > Then I keep an eye on memory usage (watch 'ps aux | grep [p]erl')
> > while running this simple test-script:
> >
> >  #!/usr/bin/perl
> >
> >  use strict;
> >  use warnings;
> >
> >  use TestDB;
> >
> >  my $schema=TestDB->connect("dbi:Pg:dbname=testdb;host=localhost",
> > "user", "pass", { AutoCommit=>0 });
> >
> >  do_search();
> >
> >  print "sleeping\n";
> >  sleep 10000;
> >
> >  exit 0;
> >
> >  sub do_search {
> >      my $xs=$schema->resultset('TestDB::Testtable')->search();
> >      print $xs->count . "\n";
> >      while (my $x=$xs->next) {
> >          ;
> >      }
> >  }
> >
> > What happens is that the memory usage grows to ~250MB when ->search()
> > is called. This, I sort of expected.
> >
> > But when do_search() returns, the memory is still in use. The only way
> > I can make it not be so, is by terminating the script or calling
> > $schema->storage->disconnect. I would guess the memory should be freed
> > when $xs goes out of scope(-ish)?
> >
> > If I call do_search() multiple times, memory usage falls when a new
> > do_search() is started, then climbs to reach approximately the same
> > maximum, suggesting that it is not really a leak and that the lump is
> > freed just before a new one is being built up. This is just guessing
> > on my part, though.
>
> Sadly, I think I can tell you exactly what this is, and if I'm right
> it's nothing we can help you with :(
>
> DBD::Pg doesn't use cursors, it fetches the entire data into local
> memory. DBIx::Class uses prepare_cached to get the $sth.
>
> So, I believe what you're seeing is DBD::Pg fetching the full dataset
> and not releasing it until the $sth is re-executed by the next search.
>
> Try forcing the $dbh to disconnect at the end of do_search and see if
> that drops the memory.

I can't remember whether this was platform-specific (win32), but I
remember in the past that perl wouldn't actually free memory back to
the OS after variables were undef'd.
Internally, the memory is available for reuse, but only within that
single process.

I suggest a google, though, to check the current status of this.

Carl