DELETE eats up all memory and crashes box

Поиск
Список
Период
Сортировка
От Worky Workerson
Тема DELETE eats up all memory and crashes box
Дата
Msg-id ce4072df0610061123n5ff4f551q8af3f530c74429d5@mail.gmail.com
обсуждение исходный текст
Ответы Re: DELETE eats up all memory and crashes box  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "John D. Burger"
Дата:
Сообщение: Re: Two efficiency questions - clustering and ints
Следующее
От: Jeff Davis
Дата:
Сообщение: Re: server closed the connection unexpectedly