PostgreSQL Discussion - DBI/DBD::Pg mem. use goes exponential

Author

DBI/DBD::Pg mem. use goes exponential

Bob Parkinson

2005-09-29, 9:24 am

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 right RSN :- )), 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:dbna
me=......................................') || die;

$stmt=qq(create temp table tt as select key,acronym,start_da
te,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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Greg Sabino Mullane

2005-09-29, 9:24 am

-----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...14964
AC8

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

iEYEARECAAYFAkM77PcA
CgkQvJuQZxSWSshhDQCd
H+duTaoTw4wSk/ykkP2ChbXq
IcIAoPqj/ 5mtqznh7W0O2gxye2yd5
F2P
=1T95
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Vivek Khera

2005-09-29, 11:23 am

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Keary Suska

2005-09-29, 1:23 pm

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

Vivek Khera

2005-09-29, 8:23 pm

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 :-)

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

SCassidy@overlandstorage.com

2005-09-29, 8:23 pm

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
----------------------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links