Обсуждение: DBI/DBD::Pg mem. use goes exponential

Hi,

I'd really like to aviod another list (ie. interfaces) if at all possible, so...

Got a script (trimmed version below) that starts to have exponential memory use after a number of iterations around the
while((....$sth->fetch())){ } struct. 

Table is quite small (520 odd rows) and the row size is v. small.

Mem. use starts off at 64M (using top SIZE), after 20 iterations it's bloated to 74M then it goes on each fetch to 82,
99,129, 193, 257, 321M, ...kerbang... err. msg below. 

Out of memory during "large" request for 134221824 bytes, total sbrk() is 419323904 bytes at export.pl line 205

I know this is the script trying to (m|p|?)alloc more mem. for the process and failing.

Upgraded PG to 8.0.3, DBI to 1.48 and DBD::Pg to 1.43 this morning, but made no difference. FreeBSD 5.4 (they'll get it
rightRSN :- )), perl 5.8.2 

Any ideas please? This seems well weird to me, but...

Cheers,

Bob


##!/usr/local/bin/perl

### SNIP set up %gwc ###

use DBI;

$dbh=DBI->connect('dbi:Pg:dbname=......................................') || die;

$stmt=qq(create temp table tt as select key,acronym,start_date,end_date,duration,charging_policy from template_event);

$dbh->prepare($stmt)->execute () || die;

foreach my $key (keys %gwc) {

    my $gatewaycode=$gwc{$key};

    # TEMPLATE_EVENT

    if($gatewaycode eq 'B' || gatewaycode eq 'Q'){

    my $stmt=qq(select acronym from tt where key=$key order by start_date asc);

    my $sth=$dbh->prepare($stmt) || die;

    my $rv=$sth->execute() || die;

    while((my $rref=$sth->fetch())){

        my ($acro,$start_date,$end_date,$duration,$charging)=@$rref;

                    sleep(4);

                   ### eyeball top here and watch it grow ###


    }

    }

}

Out of memory during "large" request for 134221824 bytes, total sbrk() is 419323904 bytes at export.pl line 205




This message has been checked for viruses but the contents of an attachment
may still contain software viruses, which could damage your computer system:
you are advised to perform your own checks. Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.



This message has been checked for viruses but the contents of an attachment
may still contain software viruses, which could damage your computer system:
you are advised to perform your own checks. Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Any ideas please? This seems well weird to me, but...

I could not duplicate this. Can you provide a self-contained test case?
It's not useful if we don't know what's actually in the table and what
the table structure looks like.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509290932
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkM77PcACgkQvJuQZxSWSshhDQCdH+duTaoTw4wSk/ykkP2ChbXq
IcIAoPqj/5mtqznh7W0O2gxye2yd5F2P
=1T95
-----END PGP SIGNATURE-----

On Sep 29, 2005, at 8:38 AM, Bob Parkinson wrote:

> Upgraded PG to 8.0.3, DBI to 1.48 and DBD::Pg to 1.43 this morning,
> but made no difference. FreeBSD 5.4 (they'll get it right
> RSN :- )), perl 5.8.2

I've had bad mem leaks on FreeBSD with perl < 5.8.6.  Try upgrading
that as well.

on 9/29/05 6:38 AM, rwp@biome.ac.uk purportedly said:

> Any ideas please? This seems well weird to me, but...

Assuming the code snippet is representative, a couple things:

1. $dbh->prepare($stmt)->execute () || die;

This is mostly a point of protocol, but I can't resist pointing it out.
Although the above syntax works, it is not precisely correct. You should
instead:

    $dbh->do($stmt) || die;

2. my $rv=$sth->execute() || die;

You should always finish() every "select" statement handle, or both DBI and
libpq will leak:

    $sth->finish;

after the closing bracket of the while() loop.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

On Sep 29, 2005, at 1:23 PM, Keary Suska wrote:

> You should always finish() every "select" statement handle, or both
> DBI and
> libpq will leak:
>
>     $sth->finish;
>
> after the closing bracket of the while() loop.
>
>

No, you don't need to call finish() if you fall off the end of a
while $sth->fetchrow() loop.  DBI already knows you're done with it.
You only call finish if you break out of the loop.  Ask Tim.  He told
me personally :-)

Hi,

You cannot actually say that "do" is more correct than prepare/execute (for
a non-select statement), because "do" is just a shortcut.  According to the
DBI documentation, it does a prepare and execute for you.  In fact, if you
look at the DBI.pm code, that is what it is doing.  I do agree that the
original $dbh->prepare($stmt)->execute () || die;
is kind of odd.


However, certainly inside the loop where the original  code example was
doing the same basic statement in a loop, it would be more efficient to
"prepare" the statement outside the loop, using a placeholder for the $key
value, then "execute" multiple times.

As far as "finish" is concerned, unless you did not go through all the
data, it is not necessary to call "finish". From the docs:

      The "finish" method is rarely needed, and frequently overused ...

He may also need to check again for errors after the end of the loop.  It
may be there in the actual code.  The original example appears to have been
"paraphrased".

Susan



                  
                           Keary Suska
                  
                      <hierophant@pcisys.net>        To:       Postgres General <pgsql-general@postgresql.org>
                  
                           Sent by:                  cc:
                  
                                                     Subject:  Re: [GENERAL] DBI/DBD::Pg mem. use goes exponential
                  

                  
                      pgsql-general-owner@pos         |-------------------|
                  
                      tgresql.org                     | [ ] Expand Groups |
                  
                                                      |-------------------|
                  

                  
                           09/29/2005 10:23
                  
                      AM
                  

                  

                  




on 9/29/05 6:38 AM, rwp@biome.ac.uk purportedly said:

> Any ideas please? This seems well weird to me, but...

Assuming the code snippet is representative, a couple things:

1. $dbh->prepare($stmt)->execute () || die;

This is mostly a point of protocol, but I can't resist pointing it out.
Although the above syntax works, it is not precisely correct. You should
instead:

    $dbh->do($stmt) || die;

2. my $rv=$sth->execute() || die;

You should always finish() every "select" statement handle, or both DBI and
libpq will leak:

    $sth->finish;

after the closing bracket of the while() loop.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster





----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------