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 GenuineIntel GNU/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 without time zone
OR servers_part.failcount >= 1 AND servers_part.failcount < 5 AND servers_part.lastchange < '2011-12-08 00:00:00'::timestamp without time zone
OR servers_part.failcount >= 5 AND servers_part.failcount < 10 AND servers_part.lastchange < '2011-12-05 00:00:00'::timestamp without time zone
OR servers_part.failcount >= 10 AND servers_part.failcount < 15 AND servers_part.lastchange < '2011-11-28 00:00:00'::timestamp without time zone
OR servers_part.failcount = 15 AND servers_part.lastchange < '2011-10-01 00:00:00'::timestamp without time zone) x) y
WHERE y.rc <= 128;

--
Jochen Erwied | home: jochen(at)erwied(dot)eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)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(at)anarazel(dot)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(at)erwied(dot)eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de +49-173-5404164

On 12.12.2011 08:26, Tom Lane wrote:
> Andres Freund<andres(at)anarazel(dot)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(at)anarazel(dot)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(at)anarazel(dot)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(at)erwied(dot)eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de +49-173-5404164

Hi,

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.

Best regards,
Otto

2011/12/12 Jochen Erwied <jochen(at)pgsql-bugs(dot)erwied(dot)eu>

> 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(at)erwied(dot)eu +49-208-38800-18, FAX:
> -19
> Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX:
> -50
> D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de
> +49-173-5404164
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Hi,

On Friday, December 16, 2011 01:50:03 PM Havasvölgyi Ottó 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 have
discovered a memory leak with a simple scenario as that could you please open
a new bugreport on report your findings there instead of hiding it deep in
other threads.

Thanks,

Andres