massive memory allocation until machine crashes

From: Alexander Elgert <alexander_elgert(at)adiva(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: massive memory allocation until machine crashes
Date: 2007-02-14 18:14:34
Message-ID: 45D3518A.9020305@adiva.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

given is a postgres database in version
------------------------------------------------------------------------
PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2

and there is a table "visit" with 26 million tuples using 8 GB of space

SELECT relname, reltuples, relpages*8/1024 as relpages_in_MB FROM pg_class
ORDER BY relpages DESC limit 10;
relname | reltuples | relpages_in_mb
------------------+------------+----------------
visit | 2.6348e+07 | 7673

The table is from ofbiz and for logging accesses to the webapplication.
Running a delete command which deletes all but a few tuples causes the
postmaster to allocate memory:
--- 10903 postgres 25 0 214M 213M 10412 R 95.3 10.5 6:07
postmaster

Until all memory and swap is gone - that was 1.4GB of top:SIZE
--- delete from visit where date(created_stamp) <
date(current_timestamp - '7 days'::interval);

I just do not know why it needs allocating so much memory.

I solved the problem in dividing the affected tuples in parts and
deleting it part by part.
--- delete from visit where date(created_stamp) <
date(current_timestamp - '300 days'::interval);
--- delete from visit where date(created_stamp) <
date(current_timestamp - '240 days'::interval);
--- ...

Why does the postmaster need so much memory to delete tuples?

Thanks in advance.
Alexander Elgert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wilton 2007-02-14 18:24:00 Protect PL/PGSQL source
Previous Message Scott Marlowe 2007-02-14 18:14:13 Re: backup database by cloning itself