Re: massive memory allocation until machine crashes - Mailing list pgsql-general

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