Thread: Postgresql 9.1.2 - abnormal memory usage
After upgrading from 9.1.1 to 9.1.2, memory usage for some queries increased to the amount that the OOM-killer on Linux terminated the server. Further investigation show the problem to occur under multiple circumstances: - copying data from an external file into a table with indexes - creating indexes - complex queries using row_number() and 'partition over' There might be other cirumstances, the above are the ones I could reproduce. Information about the testsystem: Linux gentoo-test 3.1.5-gentoo #1 SMP Sat Dec 10 22:42:53 CET 2011 i686 Intel(R) Core(TM)2 Duo CPU T8100 @ 2.10GHz GenuineIntelGNU/Linux PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by i686-pc-linux-gnu-gcc (Gentoo 4.4.4-r2 p1.2, pie-0.4.5) 4.4.4, 32-bit Running in a virtual machine (VMWare Workstation), 2GB of memory, and 512 MB of swap. Problem was initially observed on a 64bit-system. Downgrading Postgres from 9.1.2 back to 9.1.1 fixed the problem. The other system is running (after downgrading): Linux joker 3.1.1-gentoo #2 SMP Thu Nov 24 22:59:20 CET 2011 x86_64 Intel(R) Xeon(R) CPU 5110 @ 1.60GHz GenuineIntel GNU/Linux PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.5.3-r1 p1.0, pie-0.4.5) 4.5.3, 64-bit Postgres was compiled from source on both machines. Configuration on the testing machine is mostly unchanged, modified settings are: maintenance_work_mem = 256MB shared_buffers = 128MB fsync = off synchronous_commit = off The following script can be used to show the behaviour: create database pgtest; \c pgtest create table test(var inet); insert into test(var) '127.0.0.0'::inet+generate_series(0,256*256*256-1); create index test_ix on test(var); The postgres process uses approx. 1000MB during the insert, generating the index approx. 400MB using Postgres 9.1.1, monitored using top, column VIRT Same operation with Postgres 9.1.2 uses about the same memory for filling the table, but generating the index exhausts all available memory, and the machine slows to a crawl due to swapping. To me this looks a bit like temporary tables are not correctly used in 9.1.2. The problem originally was observed with the following tabledefinition and query: create table servers_part (ip inet not null primary key, failcount smallint default -1, lastchange timestamp without time zone default now(), cidr smallint default 0); create index servers_part_fclc on servers_part(failcount,lastchange); The data for this table contains 2^24 rows, and can be downloaded from http://www.erwied.eu/~transfer/217.dump.bz2 (116 MB) The following query shows the same behaviour as index creation (memory exhaustion): SELECT y.ip, y.failcount, y.network FROM ( SELECT x.ip, x.failcount, x.network, row_number() OVER (PARTITION BY x.network ORDER BY x.lastchange) AS rc FROM ( SELECT servers_part.ip, servers_part.failcount, set_masklen(servers_part.ip::cidr, COALESCE(servers_part.cidr::integer,8)) AS network, servers_part.lastchange FROM servers_part WHERE servers_part.failcount = 0 AND servers_part.lastchange < '2011-11-11 00:00:00'::timestamp withouttime zone OR servers_part.failcount >= 1 AND servers_part.failcount < 5 AND servers_part.lastchange < '2011-12-0800:00:00'::timestamp without time zone OR servers_part.failcount >= 5 AND servers_part.failcount < 10 AND servers_part.lastchange < '2011-12-0500:00:00'::timestamp without time zone OR servers_part.failcount >= 10 AND servers_part.failcount < 15 AND servers_part.lastchange < '2011-11-2800:00:00'::timestamp without time zone OR servers_part.failcount = 15 AND servers_part.lastchange < '2011-10-01 00:00:00'::timestamp withouttime zone) x) y WHERE y.rc <= 128; -- Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
Hi,
On Sunday, December 11, 2011 10:15:29 PM Jochen Erwied wrote:
> The following script can be used to show the behaviour:
>
> create database pgtest;
> \c pgtest
> create table test(var inet);
> insert into test(var) '127.0.0.0'::inet+generate_series(0,256*256*256-1);
> create index test_ix on test(var);
I can reproduce the issue. The memory is being spent at only one place:
TopMemoryContext: 68688 total in 10 blocks; 2736 free (10 chunks); 65952 used
TopTransactionContext: 8192 total in 1 blocks; 4856 free (0 chunks); 3336
used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 15360 total in 4 blocks; 10640 free (17 chunks); 4720
used
TupleSort: 6283429096 total in 723 blocks; 1424704 free (0 chunks);
6282004392 used
Looking.
Andres
Hi, On Monday, December 12, 2011 12:45:23 AM Andres Freund wrote: > On Sunday, December 11, 2011 10:15:29 PM Jochen Erwied wrote: > > The following script can be used to show the behaviour: > > > > create database pgtest; > > \c pgtest > > create table test(var inet); > > insert into test(var) '127.0.0.0'::inet+generate_series(0,256*256*256-1); > > create index test_ix on test(var); > > I can reproduce the issue. The memory is being spent at only one place: > > TopMemoryContext: 68688 total in 10 blocks; 2736 free (10 chunks); 65952 > used TopTransactionContext: 8192 total in 1 blocks; 4856 free (0 chunks); > 3336 used > PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used > PortalHeapMemory: 15360 total in 4 blocks; 10640 free (17 chunks); 4720 > used > TupleSort: 6283429096 total in 723 blocks; 1424704 free (0 chunks); > 6282004392 used In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP unpack toasted values. Unfortunately the btree support functions for the inet type didn't free memory which they have to do in contrast to about everything else. I fixed a few more functions than strictly necessary but I guess thats ok. Jochen, could you try the attached patch? Andres
Andres Freund <andres@anarazel.de> writes:
> In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP unpack
> toasted values. Unfortunately the btree support functions for the inet type
> didn't free memory which they have to do in contrast to about everything else.
> I fixed a few more functions than strictly necessary but I guess thats ok.
Seems like the correct fix is to revert these functions to the former
behavior, ie they should be using the PP macros not the unpacking ones.
regards, tom lane
Monday, December 12, 2011, 1:45:42 AM you wrote: > Jochen, could you try the attached patch? Patch is missing a variable define for 'r' in network_sup(). Fixed patch attached. Memory usage for insert ~1087MB - no change Memory usage for index creation ~415MB - fixed. Memory usage for select ~150MB - no increase, also fixed The patch works for me, I'll leave the discussion if it's correct way to do it to the developers :-) -- Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
Attachment
On 12.12.2011 08:26, Tom Lane wrote: > Andres Freund<andres@anarazel.de> writes: >> In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP unpack >> toasted values. Unfortunately the btree support functions for the inet type >> didn't free memory which they have to do in contrast to about everything else. > >> I fixed a few more functions than strictly necessary but I guess thats ok. > > Seems like the correct fix is to revert these functions to the former > behavior, ie they should be using the PP macros not the unpacking ones. Agreed, there's no need to unpack here. Fixed, thanks for the report! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi Heikki, On 12/12/2011 09:29, Heikki Linnakangas wrote: > On 12.12.2011 08:26, Tom Lane wrote: >> Andres Freund<andres@anarazel.de> writes: >>> In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP >>> unpack >>> toasted values. Unfortunately the btree support functions for the >>> inet type >>> didn't free memory which they have to do in contrast to about >>> everything else. >> >>> I fixed a few more functions than strictly necessary but I guess >>> thats ok. >> >> Seems like the correct fix is to revert these functions to the former >> behavior, ie they should be using the PP macros not the unpacking ones. > > Agreed, there's no need to unpack here. Fixed, thanks for the report! Just to clarify, am I correct assuming that the issue does not affect tables which have non-indexed inet fields? Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
On 12.12.2011 13:16, Matteo Beccati wrote: > On 12/12/2011 09:29, Heikki Linnakangas wrote: >> On 12.12.2011 08:26, Tom Lane wrote: >>> Andres Freund<andres@anarazel.de> writes: >>>> In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP >>>> unpack >>>> toasted values. Unfortunately the btree support functions for the >>>> inet type >>>> didn't free memory which they have to do in contrast to about >>>> everything else. >>> >>>> I fixed a few more functions than strictly necessary but I guess >>>> thats ok. >>> >>> Seems like the correct fix is to revert these functions to the former >>> behavior, ie they should be using the PP macros not the unpacking ones. >> >> Agreed, there's no need to unpack here. Fixed, thanks for the report! > > Just to clarify, am I correct assuming that the issue does not affect > tables which have non-indexed inet fields? Hmm, I think it might also affect queries that do large merge joins on inet fields. Not sure about that. In any case, it's a memory leak that gets cleared at the end of statement, so it can only become a problem when dealing with large data sets. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Monday, December 12, 2011, 12:33:03 PM you wrote: > On 12.12.2011 13:16, Matteo Beccati wrote: >> Just to clarify, am I correct assuming that the issue does not affect >> tables which have non-indexed inet fields? > Hmm, I think it might also affect queries that do large merge joins on > inet fields. Not sure about that. In any case, it's a memory leak that > gets cleared at the end of statement, so it can only become a problem > when dealing with large data sets. The second test case I originally sent uses inet-functions in a complicated query, and it also shows the described behaviour. So the problem is not limited to indexes, but also will appear in queries! -- Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
Hi,<br /><br />A simple query "insert into mytable default values" on a single serial-column table also leaks. It can alsoproduced by pgbench. On Windows and Linux too.<br /><br />Best regards,<br />Otto<br /><br /><br /><div class="gmail_quote">2011/12/12 Jochen Erwied <span dir="ltr"><<a href="mailto:jochen@pgsql-bugs.erwied.eu">jochen@pgsql-bugs.erwied.eu</a>></span><br/><blockquote class="gmail_quote"style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="im">Monday,December 12, 2011, 12:33:03 PM you wrote:<br /><br /> > On 12.12.2011 13:16, Matteo Beccati wrote:<br/><br /></div><div class="im">>> Just to clarify, am I correct assuming that the issue does not affect<br/> >> tables which have non-indexed inet fields?<br /><br /> > Hmm, I think it might also affect queriesthat do large merge joins on<br /> > inet fields. Not sure about that. In any case, it's a memory leak that<br/> > gets cleared at the end of statement, so it can only become a problem<br /> > when dealing with large datasets.<br /><br /></div>The second test case I originally sent uses inet-functions in a complicated<br /> query, and italso shows the described behaviour.<br /><br /> So the problem is not limited to indexes, but also will appear in queries!<br/><div class="im HOEnZb"><br /> --<br /> Jochen Erwied | home: <a href="mailto:jochen@erwied.eu">jochen@erwied.eu</a> <a href="tel:%2B49-208-38800-18" value="+492083880018">+49-208-38800-18</a>,FAX: -19<br /> Sauerbruchstr. 17 | work: <a href="mailto:joe@mbs-software.de">joe@mbs-software.de</a> <a href="tel:%2B49-2151-7294-24" value="+492151729424">+49-2151-7294-24</a>,FAX: -50<br /> D-45470 Muelheim | mobile: <a href="mailto:jochen.erwied@vodafone.de">jochen.erwied@vodafone.de</a> <a href="tel:%2B49-173-5404164" value="+491735404164">+49-173-5404164</a><br/><br /><br /></div><div class="HOEnZb"><div class="h5">--<br /> Sent via pgsql-bugsmailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<br /> To make changes toyour subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-bugs" target="_blank">http://www.postgresql.org/mailpref/pgsql-bugs</a><br/></div></div></blockquote></div><br />
Hi, On Friday, December 16, 2011 01:50:03 PM Havasv=C3=B6lgyi Ott=C3=B3 wrote: > A simple query "insert into mytable default values" on a single > serial-column table also leaks. It can also produced by pgbench. On > Windows and Linux too. Youve mentioned that in several threads now. If you really think that you h= ave=20 discovered a memory leak with a simple scenario as that could you please op= en=20 a new bugreport on report your findings there instead of hiding it deep in= =20 other threads. Thanks, Andres