Re: massive memory allocation until machine crashes

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