massive memory allocation until machine crashes
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
postmasterUntil 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
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 postmasterUntil 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
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.2Upgrade 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 postmasterUntil 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
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.2Upgrade 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 postmasterUntil 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
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.