From: | "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | DELETE eats up all memory and crashes box |
Date: | 2006-10-06 18:23:49 |
Message-ID: | ce4072df0610061123n5ff4f551q8af3f530c74429d5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
When I issue a fairly large DELETE query which has multiple tables
with FOREIGN KEY .... CASCADE on them, Postgres eats up *all* the
memory on my system and the system crashes. I figure that there are
two problems, one is PG eating up all of the memory, the other is the
system crashing and not telling me anything (neither /var/log/messages
nor kernel logging tell me anything). What could cause PG to eat up
all the memory on a system?
Any ideas and/or monitoring methods that I could use to figure out
what is going wrong? I had a top running, and the last thing that I
see is that PG is using up about 22gb of memory and postmaster and
kswapd is working hardest. Perhaps I also set some of my
postgresql.conf numbers incorrectly?
Thanks!
--------------------
Simplified schema:
CREATE TABLE ip_info (
ip IP4R PRIMARY KEY,
country VARCHAR,
...
);
CREATE TABLE flow (
fm_ip IP4R NOT NULL REFERENCES ip_info (ip) ON DELETE CASCADE,
...
);
Offending query: DELETE FROM ip_info WHERE country IN ('Canada',
'Yugoslavia', ...);
Hardware: Quad Dual-core Opteron, 16GB RAM, 8GB swap
Software: PostgreSQL 8.1.3 on RHEL4 x64_64
Purpose: Dedicated PG data-warehouse server
Changed config settings:
shared_buffers = 60000
temp_buffers = 10000
work_mem = 524288
maintenance_work_mem = 524288
max_fsm_pages=2000000
max_fsm_relations=100000
wal_buffers=128
checkpoint_segments=128
checkpoint_timeout=3000
effective_cache_size = 1200000
random_page_cost = 2
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2006-10-06 18:34:05 | Re: server closed the connection unexpectedly |
Previous Message | John D. Burger | 2006-10-06 18:12:23 | Re: Two efficiency questions - clustering and ints |