Обсуждение: Is it a memory leak in PostgreSQL 7.4beta?
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
Hans-Jürgen Schönig <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
On Sat, 30 Aug 2003, Tom Lane wrote: > Hans-Jürgen Schönig <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.
On Sat, 30 Aug 2003, Stephan Szabo wrote: > On Sat, 30 Aug 2003, Tom Lane wrote: > > > Hans-Jürgen Schönig <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.
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
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
Вложения
Hans-Jürgen Schönig <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
"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Hans-Jürgen Schönig <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
> 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
> 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
> 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
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). > >
Have we determined there _isn't_ a memory leak problem in beta2? --------------------------------------------------------------------------- Tom Lane wrote: > Hans-Jürgen Schönig <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, Pennsylvania19073
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
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 ... ServusManfred
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
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