I have done some beta testing with PostgreSQL 7.4beta2.
I have run a simple set of SQL statements 1 million times:

-- START TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO t_data (data) VALUES ('2500');
UPDATE t_data SET data = '2500' WHERE data = '2500';
DELETE FROM t_data WHERE data = '2500';
-- COMMIT;

The interesting thing was that my postmaster needed around 4mb of RAM 
when I started running my test script using ...

psql test < script.sql

After about 2 1/2 hours the backend process already needed 11mb of ram. 
looking at the output of top you can see that it seems to be in the 
shared memory area:

PID   USER     PRI  NI    SIZE  RSS   SHARE STAT    %CPU    %MEM   TIME 
     COMMAND
28899 hs        39  19   11456  11M   10620    R  N 89.8      2.9 
150:23 postmaster

this seems very surprising to me because I have no explanation why 
PostgreSQL should consume so much more memory than at the beginning of 
the test.
There are no trigger or something like that around.

The table I am working on consist of two columns (one timestamp, one int4).


In addition to that I have made a test with a different set of SQL 
statements. I have tried 1500 concurrent transaction on my good old AMD 
Athlon 500 box running RedHat Linux. It worked out pretty fine. The 
thing I came across was that my memory consumption raised during the 
first two hours of my test (from about 1 1/2 gigs to 1.7 gigs ram). 
pretty surprising as well.

does anybody have an explanation for this behaviour?

	Regards,

		Hans




I have run 1500 concurrent transactions on an AMD Athlon box (RedHat 9).


-- 
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Sat, 30 Aug 2003 14:12:21 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at>
Is it a memory leak in PostgreSQL 7.4beta?
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> The interesting thing was that my postmaster needed around 4mb of RAM 
> when I started running my test script using ...
> After about 2 1/2 hours the backend process already needed 11mb of ram. 

Hmm.  I tried

create table t_data (data int4, ts timestamp default now());

followed by many repetitions of

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO t_data (data) VALUES ('2500');
UPDATE t_data SET data = '2500' WHERE data = '2500';
DELETE FROM t_data WHERE data = '2500';
COMMIT;

I am seeing a slow but steady growth of the backend process on a Linux
box (RHL 8.0) --- top shows it growing a few K every few seconds.

But I see *zero* growth with the same test on HPUX 10.20.

A possible wild card is that the Postgres build I'm using on the Linux
box is compiled for profiling (-pg, no --enable-debug or --enable-cassert)
whereas the HPUX build has --enable-debug and --enable-cassert but no
profiling.  I'm not aware that there's any known memory leakage in
Linux' profiling support, though.

Can anyone else reproduce this, or confirm they don't see it?  What
platform, and what configure options?

			regards, tom lane

Sat, 30 Aug 2003 18:35:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
On Sat, 30 Aug 2003, Tom Lane wrote:

> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> > The interesting thing was that my postmaster needed around 4mb of RAM
> > when I started running my test script using ...
> > After about 2 1/2 hours the backend process already needed 11mb of ram.
>
> Hmm.  I tried
>
> create table t_data (data int4, ts timestamp default now());
>
> followed by many repetitions of
>
> START TRANSACTION ISOLATION LEVEL READ COMMITTED;
> INSERT INTO t_data (data) VALUES ('2500');
> UPDATE t_data SET data = '2500' WHERE data = '2500';
> DELETE FROM t_data WHERE data = '2500';
> COMMIT;
>
> I am seeing a slow but steady growth of the backend process on a Linux
> box (RHL 8.0) --- top shows it growing a few K every few seconds.
>
> But I see *zero* growth with the same test on HPUX 10.20.
>
> A possible wild card is that the Postgres build I'm using on the Linux
> box is compiled for profiling (-pg, no --enable-debug or --enable-cassert)
> whereas the HPUX build has --enable-debug and --enable-cassert but no
> profiling.  I'm not aware that there's any known memory leakage in
> Linux' profiling support, though.
>
> Can anyone else reproduce this, or confirm they don't see it?  What
> platform, and what configure options?

RHL9, --enable-debug, CFLAGS as -O0

Doing the above sequence many times from a script piped into psql, I'm
seeing RSS increasing for the backend as it goes along about the same as
yours it seems.


Sat, 30 Aug 2003 17:42:14 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Is it a memory leak in PostgreSQL 7.4beta? 

On Sat, 30 Aug 2003, Stephan Szabo wrote:

> On Sat, 30 Aug 2003, Tom Lane wrote:
>
> > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> > > The interesting thing was that my postmaster needed around 4mb of RAM
> > > when I started running my test script using ...
> > > After about 2 1/2 hours the backend process already needed 11mb of ram.
> >
> > Hmm.  I tried
> >
> > create table t_data (data int4, ts timestamp default now());
> >
> > followed by many repetitions of
> >
> > START TRANSACTION ISOLATION LEVEL READ COMMITTED;
> > INSERT INTO t_data (data) VALUES ('2500');
> > UPDATE t_data SET data = '2500' WHERE data = '2500';
> > DELETE FROM t_data WHERE data = '2500';
> > COMMIT;
> >
> > I am seeing a slow but steady growth of the backend process on a Linux
> > box (RHL 8.0) --- top shows it growing a few K every few seconds.
> >
> > But I see *zero* growth with the same test on HPUX 10.20.
> >
> > A possible wild card is that the Postgres build I'm using on the Linux
> > box is compiled for profiling (-pg, no --enable-debug or --enable-cassert)
> > whereas the HPUX build has --enable-debug and --enable-cassert but no
> > profiling.  I'm not aware that there's any known memory leakage in
> > Linux' profiling support, though.
> >
> > Can anyone else reproduce this, or confirm they don't see it?  What
> > platform, and what configure options?
>
> RHL9, --enable-debug, CFLAGS as -O0
>
> Doing the above sequence many times from a script piped into psql, I'm
> seeing RSS increasing for the backend as it goes along about the same as
> yours it seems.

I rebuild without debug, and ran just the start/insert/commit sequence
over and over and noticed that on my machine it looked to grow as above
but that if I let it go long enough it seemed to basically stop (or at
least the growth was slow enough to go without notice as compared to the
easily noticable growth before).  I'm running the full sequence now, but
it's going to be a while before it stops or gets up to the place where it
stoped in the s/i/c sequence.



Sat, 30 Aug 2003 19:30:16 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
Stephan Szabo wrote:

>
>I rebuild without debug, and ran just the start/insert/commit sequence
>over and over and noticed that on my machine it looked to grow as above
>but that if I let it go long enough it seemed to basically stop (or at
>least the growth was slow enough to go without notice as compared to the
>easily noticable growth before).  I'm running the full sequence now, but
>it's going to be a while before it stops or gets up to the place where it
>stoped in the s/i/c sequence.
>  
>
This is the Pg backend line from top after about 90 minutes runtime :

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16083 postgres  17   0  9172 9172 8524 R 94.7  2.4  84:59.68 postmaster

No sign of the shared growth stopping at this stage...

Pg built with --disable-debug --without-zlib

Platform is Linux 2.4.21+ xfs (Mandrake 9.2beta)

regards


Mark


Sun, 31 Aug 2003 16:43:50 +1200
Mark Kirkwood <markir@paradise.net.nz>
Re: Is it a memory leak in PostgreSQL 7.4beta?
> This is the Pg backend line from top after about 90 minutes runtime :
> 
> PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 16083 postgres  17   0  9172 9172 8524 R 94.7  2.4  84:59.68 postmaster
> 
> No sign of the shared growth stopping at this stage...
> 
> Pg built with --disable-debug --without-zlib
> 
> Platform is Linux 2.4.21+ xfs (Mandrake 9.2beta)
> 
> regards
> 
> 
> Mark

I can hardly imagine that the backend started working with 9mb of 
memory. what did you do that PostgreSQL needed so much memory from the 
beginning??? are you using the default settings? usually the postmaster 
does not need more than 3mb at startup (in this scenario).

	Regards,

		Hans

-- 
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Mon, 01 Sep 2003 08:07:20 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at>
Re: Is it a memory leak in PostgreSQL 7.4beta?
> I am seeing a slow but steady growth of the backend process on a Linux
> box (RHL 8.0) --- top shows it growing a few K every few seconds.
> 
> But I see *zero* growth with the same test on HPUX 10.20.
> 
> A possible wild card is that the Postgres build I'm using on the Linux
> box is compiled for profiling (-pg, no --enable-debug or --enable-cassert)
> whereas the HPUX build has --enable-debug and --enable-cassert but no
> profiling.  I'm not aware that there's any known memory leakage in
> Linux' profiling support, though.
> 
> Can anyone else reproduce this, or confirm they don't see it?  What
> platform, and what configure options?


I have tried it on our Ultra Sparc 10 running Debian (Woody).
Same problem.
The postmaster starts at around 2.2mb and keeps allocating memory :(
Maybe I can test it on AIX within the next two weeks (still waiting for 
the machine to come).

	Regards,

		Hans


-- 
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Mon, 01 Sep 2003 09:40:20 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at>
Re: Is it a memory leak in PostgreSQL 7.4beta?
Hans-Jï¿½rgen Schï¿½nig wrote:

>
> I can hardly imagine that the backend started working with 9mb of 
> memory. what did you do that PostgreSQL needed so much memory from the 
> beginning??? are you using the default settings? usually the 
> postmaster does not need more than 3mb at startup (in this scenario).

Setup is completely default  - i.e run initdb, and start the server 
after that.
I am running an embedded sql program to do the test, rather than an sql 
script
(see enclosed), not sure why/if that would make any difference.

On the cautionary side, note that I am using a beta Linux distribution too.

regards

Mark
Mon, 01 Sep 2003 23:00:16 +1200
Mark Kirkwood <markir@paradise.net.nz>
Re: Is it a memory leak in PostgreSQL 7.4beta?
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> I can hardly imagine that the backend started working with 9mb of 
> memory. what did you do that PostgreSQL needed so much memory from the 
> beginning???

On some platforms, "top" seems to count the Postgres shared memory block
as part of the memory space of each backend.  If it does so in Mark's
system then that would be a pretty reasonable initial report.

			regards, tom lane

Mon, 01 Sep 2003 08:43:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> > I can hardly imagine that the backend started working with 9mb of 
> > memory. what did you do that PostgreSQL needed so much memory from the 
> > beginning???
> 
> On some platforms, "top" seems to count the Postgres shared memory block
> as part of the memory space of each backend.  If it does so in Mark's
> system then that would be a pretty reasonable initial report.

I'm leaving for vacation so I will not have time to try mpatrol
and see what is going on.

You can find mpatrol here:

http://www.cbmamiga.demon.co.uk/mpatrol/

if some one have time may be usefull try and
see the logs.


Regards
Gaetno Mendola






Mon, 1 Sep 2003 16:19:58 +0200
"Mendola Gaetano" <mendola@bigfoot.com>
Re: Is it a memory leak in PostgreSQL 7.4beta?
> I can hardly imagine that the backend started working with 9mb of
> memory. what did you do that PostgreSQL needed so much memory from the
> beginning??? are you using the default settings? usually the postmaster
> does not need more than 3mb at startup (in this scenario).

On FreeBSD, every one of my backends appears to occupy 20MB.  That's because
the SHM is added to _each_ process.

Chris


Wed, 3 Sep 2003 10:23:27 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Is it a memory leak in PostgreSQL 7.4beta?
Hans,

You are right about the startup memory - here is the top line for a few 
seconds after startup :

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  
COMMAND          
10116 postgres  15   0  3816 3816 3180 R 33.8  1.0   0:01.03 postmaster  

seems that VIRT, RES, SHR all get the increase counted against them as 
time goes on (as Tom suggested, I guess its to do with how top does its 
accounting on this platform).

Hans-Jï¿½rgen Schï¿½nig wrote:

>
> I can hardly imagine that the backend started working with 9mb of 
> memory. what did you do that PostgreSQL needed so much memory from the 
> beginning??? are you using the default settings? usually the 
> postmaster does not need more than 3mb at startup (in this scenario).
>
>


Thu, 04 Sep 2003 21:12:15 +1200
Mark Kirkwood <markir@paradise.net.nz>
Re: Is it a memory leak in PostgreSQL 7.4beta?

Have we determined there _isn't_ a memory leak problem in beta2?

---------------------------------------------------------------------------

Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.at> writes:
> > The interesting thing was that my postmaster needed around 4mb of RAM 
> > when I started running my test script using ...
> > After about 2 1/2 hours the backend process already needed 11mb of ram. 
> 
> Hmm.  I tried
> 
> create table t_data (data int4, ts timestamp default now());
> 
> followed by many repetitions of
> 
> START TRANSACTION ISOLATION LEVEL READ COMMITTED;
> INSERT INTO t_data (data) VALUES ('2500');
> UPDATE t_data SET data = '2500' WHERE data = '2500';
> DELETE FROM t_data WHERE data = '2500';
> COMMIT;
> 
> I am seeing a slow but steady growth of the backend process on a Linux
> box (RHL 8.0) --- top shows it growing a few K every few seconds.
> 
> But I see *zero* growth with the same test on HPUX 10.20.
> 
> A possible wild card is that the Postgres build I'm using on the Linux
> box is compiled for profiling (-pg, no --enable-debug or --enable-cassert)
> whereas the HPUX build has --enable-debug and --enable-cassert but no
> profiling.  I'm not aware that there's any known memory leakage in
> Linux' profiling support, though.
> 
> Can anyone else reproduce this, or confirm they don't see it?  What
> platform, and what configure options?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Sep 2003 00:13:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Have we determined there _isn't_ a memory leak problem in beta2?

I am not sure.  I have a suspicion that there is no real leak, but
rather we are seeing some artifact of the way Linux' top(1) reports
memory usage.  I cannot prove that --- I can only offer the evidence
that the exact same PG sources running the exact same queries on a
different OS (HPUX) show no memory leak.  It would be useful to hear
some more reports of the test case from people with other OSes.

			regards, tom lane

Wed, 10 Sep 2003 00:18:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
On Wed, 10 Sep 2003 00:18:52 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>I have a suspicion that there is no real leak, but
>rather we are seeing some artifact of the way Linux' top(1) reports
>memory usage.

 From my experience I can confirm that.  I have looked a lot at top
output when I benchmarked my heaptuple header changes last year.  ISTM
Linux accounts a shared memory page (for whatever a page is) to the
memory usage of a process as soon as the process has touched that
page.

7.4 starts up with 1000 shared buffers by default, that is 8 MB of
shared memory.  Add some space for FSM, connections and other shared
stuff, and 10 MB of shared memory is quite plausible.  We've seen
nobody complaining that his backend got much bigger than 11 MB.

It's been more than a week since the OP posted his observation.  If
his backend had grown to 100 MB after 24 hours, he would have told us
...

Servus
 Manfred

Wed, 10 Sep 2003 15:11:27 +0200
Manfred Koizar <mkoi-pg@aon.at>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Wed, 10 Sep 2003 00:18:52 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
>> I have a suspicion that there is no real leak, but
>> rather we are seeing some artifact of the way Linux' top(1) reports
>> memory usage.

> From my experience I can confirm that.  I have looked a lot at top
> output when I benchmarked my heaptuple header changes last year.  ISTM
> Linux accounts a shared memory page (for whatever a page is) to the
> memory usage of a process as soon as the process has touched that
> page.

But not otherwise, huh?  Okay, that would explain things.

> 7.4 starts up with 1000 shared buffers by default, that is 8 MB of
> shared memory.  Add some space for FSM, connections and other shared
> stuff, and 10 MB of shared memory is quite plausible.

In fact, according to ipcs, the default shared memory segment size for
CVS tip on Linux (RHL 8.0) is 10436608 bytes.  I see that a freshly
started backend is shown as having SHARE 1896, but a large seqscan query
(which would cause it to touch all the shared buffers in fairly short
order) makes the report jump to 6500.  This doesn't seem to quite square
with your explanation though --- surely the number should go to 8000 and
change?  The man page for top says these numbers are in kilobytes ...
but if they were really measured in, say, 4K pages, then we'd be talking
about 26M of shared memory touched, which might be plausible when you
consider shared libraries.  2K pages would make the numbers even more
plausible, but that seems like an unlikely page size.

			regards, tom lane

Wed, 10 Sep 2003 10:03:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta? 
I said:
> This doesn't seem to quite square
> with your explanation though --- surely the number should go to 8000 and
> change?  The man page for top says these numbers are in kilobytes ...
> but if they were really measured in, say, 4K pages, then we'd be talking
> about 26M of shared memory touched, which might be plausible when you
> consider shared libraries.

Never mind --- further testing shows that top does report in kilobytes.
I made a silly mistake in writing my test query that prevented it from
using as many buffers as I expected.  When I write something that really
does use all 1000 buffers, SHARE goes to 10392, which is right about
what you'd expect.

So I think this mystery is solved.  Back to chasing real bugs ...

			regards, tom lane

Wed, 10 Sep 2003 10:13:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is it a memory leak in PostgreSQL 7.4beta? 