Обсуждение: Memory leak!!

Hi all,

We've have a supply chain management system running under the following 
configuration.

Redhat 7.1
Perl v5.6.0
Postgres 7.3.2
Perl/DBI 1.35
DBD-Pg   1.22

Please note that Postgres 7.3.2, DBI 1.35 and DBD-Pg 1.22 have been 
upgraded recently.

I wrote a small script to check for any memory leak. This is what i've 
found. Initially, the memory occupied was about 5 MB and then, it started 
to grow at the range of 5-10k for every 1-2 minutes. After running the 
script for about 40 minutes in a while loop, the memory occupied was 7.4 
MB.

Here's the code snippet :

while (1) { print "sql executed and returned :", $Dbh->prepare("select user_id from
users limit 1")->execute, "\n"; 
}

Pls. note that there were no "lost references" in my script and it's a
very simple one. What could be the problem ? 

Please advice me. 

TIA.

Regards,
Guru Prasad.
Systems Engineer
bk Systems (P) Ltd.

"P.N.Guru Prasad" <pnguruji@yahoo.com> writes:
> while (1) {
>   print "sql executed and returned :", $Dbh->prepare("select user_id from
> users limit 1")->execute, "\n"; 
> }

I'm not a DBI user, but I'd kinda expect that executing a query would
result in an object holding the results from that query.  Don't you need
to do something to close/delete the result objects you're generating?
        regards, tom lane

On Wednesday 09 April 2003 06:58, Tom Lane wrote:
> "P.N.Guru Prasad" <pnguruji@yahoo.com> writes:
> > while (1) {
> >   print "sql executed and returned :", $Dbh->prepare("select user_id from
> > users limit 1")->execute, "\n";
> > }
>
> I'm not a DBI user, but I'd kinda expect that executing a query would
> result in an object holding the results from that query.  Don't you need
> to do something to close/delete the result objects you're generating?

Does

while (1) { my $sth = $Dbh->prepare("select user_id from users limit 1"); print "sql executed and returned :",
$sth->execute,"\n";  $sth->finish(); 
}

make any difference?

If not try asking the dbi-users list,  dbi-users@perl.org
( http://lists.perl.org/showlist.cgi?name=dbi-users )

Ian Barwick
barwick@gmx.net

On Thursday 10 April 2003 16:39, guru prasad wrote:
> Hi All,
(...)
> Also, i wanted to narrow down the problem, ie. whether
> the problem is in DBI or DBD::Pg. Could anyone throw
> some light on where should i start ? I can go by trial
> & error method to find out but at the same time, i
> feel there will be many guru's in this list who can
> help me out.

Unfortunately I'm still waiting for the Academy to send
me my Guru Certificate, so I can't help you much more ;-).

Seriously, I have no idea off spec and haven't got
the time to go into it right now. As I said, do try
the dbi-users list, it's fairly low traffic but has a higher
concentration of Perl / DBI gurus and other very helpful
people who may have much more insight, especially if it
is more a DBI than an DBD::Pg problem.

> > dbi-users@perl.org
> > http://lists.perl.org/showlist.cgi?name=dbi-users

Ian Barwick
barwick@gmx.net

Hi All,

> while (1) {
>   my $sth = $Dbh->prepare("select user_id from users
> limit 1");
>   print "sql executed and returned :",
> $sth->execute, "\n"; 
>   $sth->finish();
> }
> 
> make any difference?

I tried it. Here's the observation. Initially, the
script didn't eat so much RAM and it just stayed at
6604 MB for some time (atleast for 4 hours). I wanted
the script to run for more than a day. Next day, when
i did a 'top' in my  RH Linux box, the mem. occupied
by the script was 8028 MB of RAM. Why the difference ?

May be, my test script wasn't a perfect one to check
for memory leaks in DBI or DBD::Pg. How can i create a
more efficient test script to check for memory leaks ?
Using the script, i should be able to find out any
memory leak, in about say 1 or 2 hours. Any thoughts ?

Also, i wanted to narrow down the problem, ie. whether
the problem is in DBI or DBD::Pg. Could anyone throw
some light on where should i start ? I can go by trial
& error method to find out but at the same time, i
feel there will be many guru's in this list who can
help me out. 

Regards,
Guru Prasad.

--- Ian Barwick <barwick@gmx.net> wrote:
> On Wednesday 09 April 2003 06:58, Tom Lane wrote:
> > "P.N.Guru Prasad" <pnguruji@yahoo.com> writes:
> > > while (1) {
> > >   print "sql executed and returned :",
> $Dbh->prepare("select user_id from
> > > users limit 1")->execute, "\n";
> > > }
> >
> > I'm not a DBI user, but I'd kinda expect that
> executing a query would
> > result in an object holding the results from that
> query.  Don't you need
> > to do something to close/delete the result objects
> you're generating?
> 
> Does 
> 
> while (1) {
>   my $sth = $Dbh->prepare("select user_id from users
> limit 1");
>   print "sql executed and returned :",
> $sth->execute, "\n"; 
>   $sth->finish();
> }
> 
> make any difference?
> 
> If not try asking the dbi-users list, 
> dbi-users@perl.org
> ( http://lists.perl.org/showlist.cgi?name=dbi-users
> )
> 
> Ian Barwick
> barwick@gmx.net
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "P.N.Guru Prasad" <pnguruji@yahoo.com> writes:
> > while (1) {
> >   print "sql executed and returned :", $Dbh->prepare("select user_id from
> > users limit 1")->execute, "\n"; 
> > }
> 
> I'm not a DBI user, but I'd kinda expect that executing a query would
> result in an object holding the results from that query.  Don't you need
> to do something to close/delete the result objects you're generating?

Perl reference counts these objects so that shouldn't be a problem.

You could be sure by using Devel::LeakTrace or Devel::Leak or something like
that. But I suspect they won't find anything because I suspect the problem is
in the DBD::pg driver or libpq at the C level.

--
greg