pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql function seems to be leaking memory
6+ messages / 3 participants
[nested] [flat]
* plpgsql function seems to be leaking memory
@ 2008-06-24 08:56 Marc Cousin <mcousin@sigma.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Marc Cousin @ 2008-06-24 08:56 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql function seems to be leaking memory
@ 2008-06-24 14:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Cousin <mcousin@sigma.fr>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2008-06-24 14:56 UTC (permalink / raw)
To: Marc Cousin <mcousin@sigma.fr>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql function seems to be leaking memory
@ 2008-06-24 15:29 Marc Cousin <mcousin@sigma.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Marc Cousin @ 2008-06-24 15:29 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql function seems to be leaking memory
@ 2008-06-24 15:37 Guillaume Lelarge <guillaume@lelarge.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2008-06-24 15:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Cousin <mcousin@sigma.fr>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql function seems to be leaking memory
@ 2008-06-24 15:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2008-06-24 15:48 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Marc Cousin <mcousin@sigma.fr>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql function seems to be leaking memory
@ 2008-06-26 15:48 Guillaume Lelarge <guillaume@lelarge.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2008-06-26 15:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Cousin <mcousin@sigma.fr>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-06-26 15:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-24 08:56 plpgsql function seems to be leaking memory Marc Cousin <mcousin@sigma.fr>
2008-06-24 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-24 15:29 ` Marc Cousin <mcousin@sigma.fr>
2008-06-24 15:37 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-06-24 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-26 15:48 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox