Обсуждение: DELETE eats up all memory and crashes box

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

"Worky Workerson" <worky.workerson@gmail.com> writes:
> 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.

Well, the memory eating is easy to explain: pending-trigger-event list.
System crash sounds like a kernel bug or misconfiguration.  You might
want to make sure you have "strict" memory overcommit mode set, else the
problem might just be an inopportune choice of target by the OOM killer.

(I *think* they fixed the OOM killer to never select init, but there are
still plenty of processes you'd not like it to pick ...)

            regards, tom lane

On 10/6/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Worky Workerson" <worky.workerson@gmail.com> writes:
> > 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.
>
> Well, the memory eating is easy to explain: pending-trigger-event list.
> System crash sounds like a kernel bug or misconfiguration.  You might
> want to make sure you have "strict" memory overcommit mode set, else the
> problem might just be an inopportune choice of target by the OOM killer.

You were right ... had my vm.overcommit_memory set to 0 (default).
Now the process gets killed soon after it starts.

Is there any way to tune PG to execute such a query, or am I forced to
forgo the convenience of the "ON DELETE CASCADE" and manually delete
the records with a subselect?

"Worky Workerson" <worky.workerson@gmail.com> writes:
> On 10/6/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, the memory eating is easy to explain: pending-trigger-event list.

> Is there any way to tune PG to execute such a query, or am I forced to
> forgo the convenience of the "ON DELETE CASCADE" and manually delete
> the records with a subselect?

You'd have to tweak the query to not delete so many records at once.
Note that whether you have CASCADE or not is not the issue --- if you
are doing a delete in a foreign-key-referenced relation at all, you
are going to have a trigger event per deleted row no matter what the
details of the FK are.

We've had a TODO item for awhile to spill the pending-trigger-event list
to disk when it gets too big, but no one's gotten around to it, probably
because once you're in that regime performance is going to suck anyway :-(

            regards, tom lane

> Note that whether you have CASCADE or not is not the issue --- if you
> are doing a delete in a foreign-key-referenced relation at all, you
> are going to have a trigger event per deleted row no matter what the
> details of the FK are.

So the best/fastest* way to do this would be to remove the FK
relationship from the tables, delete all my rows with DELETE ... WHERE
ip IN (SELECT ...) in the previously FK-ed tables,  delete all the
rows in the PK table, then recreate the FK relationships?  I tried
this and it was pretty snappy, assuming that all the indexes are
built.

*note: loading/creating a datawarehouse, guaranteed exclusive access.
Current DW size is about 10 GB.