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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
