DELETE eats up all memory and crashes box

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

Responses

Browse pgsql-general by date

  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