Re: massive memory allocation until machine crashes

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