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

[Lionel B. <lio...@bo...>]

Adam Sjøgren wrote the following on 05.08.2007 20:32 :
> 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.
>   

You are right on time, I was considering releasing 1.6.8 shortly. Here 
is what I think happens:

InactiveDestroy => 1 is used to support asynchronsous cleaning of the DB 
(which is quite buggy, I never understood why and still recommend to 
leave it deactivated unless it really stalls the server too long and 
asynchronous cleaning actually works for you). InactiveDestroy is meant 
to prevent the database handle from being destroyed when it goes out of 
context (which happens when you fork and open a new database connection 
for example). I believe that this InactiveDestroy could be extended to 
the prepared statements (probably erroneously, it doesn't make sense for 
me to do so), which would explain the leak.

I'll simply remove InactiveDestroy => 1 when asynchronous cleaning isn't 
active, this should help (and shouldn't have any bad side-effect).

Lionel