Thread: massive memory allocation until machine crashes

massive memory allocation until machine crashes

From

Alexander Elgert

Date:

15 February 2007, 16:45:44

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

Re: massive memory allocation until machine crashes

From

Richard Huxton

Date:

15 February 2007, 17:04:05

Alexander Elgert wrote:
> 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

Upgrade to 7.4.16 as soon as is convenient - you're missing 8 sets of
bug-fixes.

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

> 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

Do you have any triggers or foreign keys on this table? If so, each of
those will need to be tracked. There may be a memory-leak in 7.4.8
that's since been fixed, probably worth checking the release notes at
the end of the manual.

--
   Richard Huxton
   Archonet Ltd

Re: massive memory allocation until machine crashes

From

Alexander Elgert

Date:

21 February 2007, 18:35:48

Hello.

Richard Huxton schrieb:
> Alexander Elgert wrote:
>> 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
>
> Upgrade to 7.4.16 as soon as is convenient - you're missing 8 sets of
> bug-fixes.
At the Weekend I turned to 7.4.16, there was no problem, but it does not
help much...
I ran the command:

 delete from visit where date(created_stamp) < date(current_timestamp -
'8 days'::interval);

but at 1.5GB top:SIZE I aborted the query.

So I divided the set of tuples to be deleted into commands to delete all
subsets and it works:

delete from visit where date(created_stamp) < date(current_timestamp -
'360 days'::interval);
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);
delete from visit where date(created_stamp) < date(current_timestamp -
'180 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'120 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'60 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'8 days'::interval);

This queries deleted up to 7 Millions tuples and took up to 1GB of RAM.

>
>> and there is a table "visit" with 26 million tuples using 8 GB of space
>
>> 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
>
> Do you have any triggers or foreign keys on this table? If so, each of
> those will need to be tracked. There may be a memory-leak in 7.4.8
> that's since been fixed, probably worth checking the release notes at
> the end of the manual.
>
Yes, there are five FOREIGN keys in this table:

ofbiz=> \d visit
                    Table "public.visit"
       Column         |           Type           | Modifiers
-----------------------+--------------------------+-----------
visit_id              | character varying(20)    | not null
contact_mech_id       | character varying(20)    |
user_login_id         | character varying(255)   |
party_id              | character varying(20)    |
role_type_id          | character varying(20)    |
user_created          | character(1)             |
session_id            | character varying(255)   |
server_ip_address     | character varying(20)    |
server_host_name      | character varying(255)   |
webapp_name           | character varying(60)    |
initial_locale        | character varying(60)    |
initial_request       | character varying(255)   |
initial_referrer      | character varying(255)   |
initial_user_agent    | character varying(255)   |
user_agent_id         | character varying(20)    |
client_ip_address     | character varying(20)    |
client_host_name      | character varying(255)   |
client_user           | character varying(60)    |
cookie                | character varying(60)    |
from_date             | timestamp with time zone |
thru_date             | timestamp with time zone |
last_updated_stamp    | timestamp with time zone |
last_updated_tx_stamp | timestamp with time zone |
created_stamp         | timestamp with time zone |
created_tx_stamp      | timestamp with time zone |
Indexes:
   "pk_visit" primary key, btree (visit_id)
   "visit_cont_mech" btree (contact_mech_id)
   "visit_party" btree (party_id)
   "visit_party_role" btree (party_id, role_type_id)
   "visit_role_type" btree (role_type_id)
   "visit_thru_idx" btree (thru_date)
   "visit_txcrts" btree (created_tx_stamp)
   "visit_txstmp" btree (last_updated_tx_stamp)
   "visit_user_agnt" btree (user_agent_id)
Foreign-key constraints:
   "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES
contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
   "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id)
DEFERRABLE INITIALLY DEFERRED
   "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES
role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
   "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES
user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
   "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES
party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED

Greetings,
   Alexander

Re: massive memory allocation until machine crashes

From

Martijn van Oosterhout

Date:

21 February 2007, 19:14:03

On Wed, Feb 21, 2007 at 08:35:40PM +0100, Alexander Elgert wrote:
> Yes, there are five FOREIGN keys in this table:

<snip>

There's your problem. You've got a trigger set to run after every
delete, and you've got them set to wait until the end of the
transaction. So postgres has to delete all the tuples while
maintaining a list of the deleted tuples so that at the end it can run
the trigger a few million times.

Possibly something like (not sure about this):

SET ALL CONSTRAINTS IMMEDIATE;

or some such may avoid the memory usage and run the trigger straight
away.

> Foreign-key constraints:
>   "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES
> contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id)
> DEFERRABLE INITIALLY DEFERRED
>   "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES
> role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES
> user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES
> party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

signature.asc

Re: massive memory allocation until machine crashes

From

Alexander Elgert

Date:

22 February 2007, 21:32:30

Hello,

thank you for the information, but it seems my messages are hold for
moderator approval.
A few of them seems to be dropped - I don't know.

Richard Huxton schrieb:
> Alexander Elgert wrote:
>> 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
>
> Upgrade to 7.4.16 as soon as is convenient - you're missing 8 sets of
> bug-fixes.
At the Weekend I turned to 7.4.16, there was no problem, but it does not
help much...
I ran the command:

  delete from visit where date(created_stamp) < date(current_timestamp -
'8 days'::interval);

but at 1.5GB top:SIZE I aborted the query.

So I divided the set of tuples to be deleted into commands to delete all
subsets and it works:

delete from visit where date(created_stamp) < date(current_timestamp -
'360 days'::interval);
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);
delete from visit where date(created_stamp) < date(current_timestamp -
'180 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'120 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'60 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp -
'8 days'::interval);

This queries deleted up to 7 Millions tuples and took up to 1GB of RAM.

>
>> and there is a table "visit" with 26 million tuples using 8 GB of space
>
>> 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
>
> Do you have any triggers or foreign keys on this table? If so, each of
> those will need to be tracked. There may be a memory-leak in 7.4.8
> that's since been fixed, probably worth checking the release notes at
> the end of the manual.
>
Yes, there are triggers, please do not blame me for the structure, it
was not mine:

ofbiz=> \d visit
                     Table "public.visit"
        Column         |           Type           | Modifiers
-----------------------+--------------------------+-----------
 visit_id              | character varying(20)    | not null
 contact_mech_id       | character varying(20)    |
 user_login_id         | character varying(255)   |
 party_id              | character varying(20)    |
 role_type_id          | character varying(20)    |
 user_created          | character(1)             |
 session_id            | character varying(255)   |
 server_ip_address     | character varying(20)    |
 server_host_name      | character varying(255)   |
 webapp_name           | character varying(60)    |
 initial_locale        | character varying(60)    |
 initial_request       | character varying(255)   |
 initial_referrer      | character varying(255)   |
 initial_user_agent    | character varying(255)   |
 user_agent_id         | character varying(20)    |
 client_ip_address     | character varying(20)    |
 client_host_name      | character varying(255)   |
 client_user           | character varying(60)    |
 cookie                | character varying(60)    |
 from_date             | timestamp with time zone |
 thru_date             | timestamp with time zone |
 last_updated_stamp    | timestamp with time zone |
 last_updated_tx_stamp | timestamp with time zone |
 created_stamp         | timestamp with time zone |
 created_tx_stamp      | timestamp with time zone |
Indexes:
    "pk_visit" primary key, btree (visit_id)
    "visit_cont_mech" btree (contact_mech_id)
    "visit_party" btree (party_id)
    "visit_party_role" btree (party_id, role_type_id)
    "visit_role_type" btree (role_type_id)
    "visit_thru_idx" btree (thru_date)
    "visit_txcrts" btree (created_tx_stamp)
    "visit_txstmp" btree (last_updated_tx_stamp)
    "visit_user_agnt" btree (user_agent_id)
Foreign-key constraints:
    "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES
contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
    "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id)
DEFERRABLE INITIALLY DEFERRED
    "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES
role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
    "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES
user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
    "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES
party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED

Greetings,
    Alexander