Thread: plpgsql function seems to be leaking memory

plpgsql function seems to be leaking memory

From

Marc Cousin

Date:

24 June 2008, 09:22:57

Hi,

I'm having a problem with plpgsql functions leaking memory.

I've reproduced the problem with this simple function here :

CREATE OR REPLACE FUNCTION ftest()
  RETURNS boolean AS
$BODY$BEGIN
INSERT INTO test2 values (1);
RETURN true;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


test2 table is a one column table

CREATE TABLE test2
(
  id integer
)
WITH (OIDS=FALSE);



Then to see the leak, I've called it repeatedly with a perl program :

#!/usr/bin/perl -w
#
#
use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=test","postgres");

my $statement = 'SELECT ftest ()';
$sth = $dbh->prepare($statement);
$dbh->begin_work();
for ($i=0;$i<1000000;$i++)
{
        $sth->execute();
}
$dbh->rollback();


I see the postgresql process growing from 8MB to about 400 MB during this
run...

It only seems to happen when a function contains an insert statement (I don't
see leaks when only selecting).



logs=# SELECT version();
                                      version
------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on x86_64-pc-linux-gnu, compiled by GCC cc (Debian 4.3.1-1)
4.3.1
(1 row)

Re: plpgsql function seems to be leaking memory

From

Tom Lane

Date:

24 June 2008, 14:56:45

Marc Cousin <mcousin@sigma.fr> writes:
> I'm having a problem with plpgsql functions leaking memory.

Your example shows absolutely no memory leak here, in either 8.3.3
or CVS HEAD.

> I see the postgresql process growing from 8MB to about 400 MB during this
> run...

What do you have shared_buffers set to?  I think you might be getting
fooled by top's treatment of shared memory (ie, it starts to count
shared pages after the process touches them for the first time).

            regards, tom lane

Re: plpgsql function seems to be leaking memory

From

Marc Cousin

Date:

24 June 2008, 15:29:42

On Tuesday 24 June 2008 16:56:10 Tom Lane wrote:
> Marc Cousin <mcousin@sigma.fr> writes:
> > I'm having a problem with plpgsql functions leaking memory.
>
> Your example shows absolutely no memory leak here, in either 8.3.3
> or CVS HEAD.
>
> > I see the postgresql process growing from 8MB to about 400 MB during this
> > run...
>
> What do you have shared_buffers set to?  I think you might be getting
> fooled by top's treatment of shared memory (ie, it starts to count
> shared pages after the process touches them for the first time).
>
>             regards, tom lane

I'm seeing this with ps aux, you're (almost) right... And I've been wasting your time.

At the begining :
postgres 23305 39.2  0.8 1275484 31208 ?       Rs   17:07   0:18 postgres: postgres test [local] idle in transaction

Then after a few seconds :
postgres 23305 39.2  0.8 1275484 31208 ?       Rs   17:07   0:18 postgres: postgres test [local] idle in transaction

The RSS column is growing ... Here it is only a test, but I've been having
it get to more that 500MB on the real use case, and it got me worried...

shared_buffers is at 1GB...

So in fact, the problem is linked to RSS containing some shared memory...
And that this growing memory is because the process has put all those
modified blocks in the shared buffers... I've confirmed that with doing the
inserts out of plpgsql, and it's also the same if I select the process...
And I never noticed it before ...

It all started with a plperl procedure and I was afraid I had a leak in it ... :)

Sorry to have wasted your time and thanks a lot ...

Regards.

Re: plpgsql function seems to be leaking memory

From

Guillaume Lelarge

Date:

24 June 2008, 15:37:44

Tom Lane a écrit :
> Marc Cousin <mcousin@sigma.fr> writes:
>> I'm having a problem with plpgsql functions leaking memory.
>
> Your example shows absolutely no memory leak here, in either 8.3.3
> or CVS HEAD.
>
>> I see the postgresql process growing from 8MB to about 400 MB during this
>> run...
>
> What do you have shared_buffers set to?  I think you might be getting
> fooled by top's treatment of shared memory (ie, it starts to count
> shared pages after the process touches them for the first time).
>

That's what I thought at first when I saw Marc's email. I tried his
example many times, with different values for shared_buffers. RSS column
climbs until it reaches more or less the shared_buffers configuration.
But, if I launch another psql, this process will have a much lower value
on the RSS column. Shouldn't it get the RSS at the same value, even when
the process starts ?

I'm a bit puzzled by this column, anyone has a URL where I can find more
info?

Regards.

--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com

Re: plpgsql function seems to be leaking memory

From

Tom Lane

Date:

24 June 2008, 15:48:19

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Tom Lane a �crit :
>> What do you have shared_buffers set to?  I think you might be getting
>> fooled by top's treatment of shared memory (ie, it starts to count
>> shared pages after the process touches them for the first time).

> That's what I thought at first when I saw Marc's email. I tried his
> example many times, with different values for shared_buffers. RSS column
> climbs until it reaches more or less the shared_buffers configuration.
> But, if I launch another psql, this process will have a much lower value
> on the RSS column. Shouldn't it get the RSS at the same value, even when
> the process starts ?

No, that's exactly the point.  Linux top includes in a process's
reported size its actual private space, plus however many pages of
shared memory that process has physically accessed so far.  So any PG
backend that's doing a material amount of table access is going to show
an RSS that starts low and creeps up to roughly the size of your shared
memory block, quite independently of what its actual private space usage
is.  It's just a matter of how many buffers it has had occasion to use.

This is all pretty OS-dependent.  Some systems don't count shared memory
at all (HPUX seems not to), and some other ones report a separate total
for shared memory so that you can mentally subtract it.

            regards, tom lane

Re: plpgsql function seems to be leaking memory

From

Guillaume Lelarge

Date:

26 June 2008, 15:49:16

Tom Lane a écrit :
> Guillaume Lelarge <guillaume@lelarge.info> writes:
>> Tom Lane a écrit :
>>> What do you have shared_buffers set to?  I think you might be getting
>>> fooled by top's treatment of shared memory (ie, it starts to count
>>> shared pages after the process touches them for the first time).
>
>> That's what I thought at first when I saw Marc's email. I tried his
>> example many times, with different values for shared_buffers. RSS column
>> climbs until it reaches more or less the shared_buffers configuration.
>> But, if I launch another psql, this process will have a much lower value
>> on the RSS column. Shouldn't it get the RSS at the same value, even when
>> the process starts ?
>
> No, that's exactly the point.  Linux top includes in a process's
> reported size its actual private space, plus however many pages of
> shared memory that process has physically accessed so far.  So any PG
> backend that's doing a material amount of table access is going to show
> an RSS that starts low and creeps up to roughly the size of your shared
> memory block, quite independently of what its actual private space usage
> is.  It's just a matter of how many buffers it has had occasion to use.
>
> This is all pretty OS-dependent.  Some systems don't count shared memory
> at all (HPUX seems not to), and some other ones report a separate total
> for shared memory so that you can mentally subtract it.
>

Forgot to thank you for the answer... Thanks :)


--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com