Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

[<as...@ko...>]

On Sun, 05 Aug 2007 19:22:12 +0200, Lionel wrote:

>> Here's one example from 1.6.7, there are others:
 
>> sub is_in_from_awl {
>> my ($self, $sender_name, $sender_domain, $host) = @_;
>> 
>> # last_seen less than $self->{sqlgrey}{awl_age} days ago
>> my $sth = $self->prepare("SELECT 1 FROM $from_awl " .

> Note that SQLgrey uses prepare and not prepare_cached here: the prepared 
> statement ($sth) will be reclaimed when it goes out of scope (unless 
> there is a bug in DBI or the driver...).

This should be easy to test, comparing something like:

 #!/usr/bin/perl

 use strict;
 use warnings;
 use DBI;

 print "Testing... \n"; test(); print "Done\n"; my $wait=<>;

 sub test {
     my $dbh=DBI->connect("DBI:Pg:dbname=sqlgrey", "sqlgrey", "xxxxxxxx",  { PrintError=>0, AutoCommit=>1, InactiveDestroy=>1 });

     my $i=0;
     while ($i++<10000) {
         my $sth=$dbh->prepare("SELECT 1 FROM domain_awl WHERE sender_domain = ? AND src = ? AND last_seen > timestamp '2007-08-05 07:00:00' - INTERVAL '6 DAY';");
         $sth->execute("xxxxxxxxxxxx", "xxxxxxxxxxxxx");
         my $res=$sth->fetchall_arrayref;
     }
 }

to a program where the timestamp varies in the string.

                                 o o o

Oh, but, running the program above on a Debian etch amd64 box uses an
increasing amount of RAM, ending at around ~70MB.

That is without varying the "dbnow" that Karl O. Pinc suggested as a
possible leak.

What is then causing this loop of a common sqlgrey-query to leak?

                                 o o o

Removing the "InactiveDestroy=>1" option gives the result that the
program uses ~6MB the whole time.

                                 o o o

Quickly leafing through some of the DBI docs, I guess that this may be
handled correctly in sqlgrey, but not in my simple, non-forking
test-script. I am probably chasing a red herring here?

I may very well be, but I'll try commenting the InactiveDestroy=>1 line
out anyway and see if that alleviates my problem.


(The versions I used to test are:

 DBI        1.57
 DBD::Pg    1.49
 Postgresql 8.1.9)


  Best regards,

    Adam

-- 
 "Money always takes the place of life"                       Adam Sjøgren
                                                         as...@ko...