Memory Problem

Hi,

We have an IBM System x3850 machine running on RHEL 4.5 Cluster Suite
with high-availability enabled. During a huge delete process, PostgreSQL
(8.3.1) exhausts available memory and receives an OOM kill.

$ /srv/usr/bin/psql -e -f ~/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql 1_5_1_0_20080625
...
DELETE FROM mudailyreportlog
WHERE NOT EXISTS (SELECT 1
FROM mobileunit
WHERE mobileunit.muid = mudailyreportlog.muid);
DELETE 0
DELETE FROM mudistancelog
WHERE NOT EXISTS (SELECT 1
FROM mobileunit
WHERE mobileunit.muid = mudistancelog.muid);
psql:/home/postgres/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql:16: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:/home/postgres/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql:16: connection to server was lost

Above "DELETE FROM mudistancelog ..." query runs for nearly 1 hour and
then causes PostgreSQL to receive an OOM kill. mudistancelog is a table
of size ~11GiG. And below is the execution plan of the DELETE statement.

Seq Scan on mudistancelog (cost=0.00..1730580560.83 rows=104441936 width=6)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using pk_mobileunit_muid on mobileunit (cost=0.00..8.27 rows=1 width=0)
Index Cond: (muid = $0)

Here are the related PostgreSQL log lines.

2008-07-02 10:36:09.032 EEST LOG: server process (PID 22391) was terminated by signal 9: Killed
2008-07-02 10:36:09.032 EEST LOG: terminating any other active server processes
...
test_1_5_1_0 emove 2008-07-02 10:36:09.151 EEST FATAL: the database system is in recovery mode
test_1_5_1_0 emove 2008-07-02 10:36:09.152 EEST FATAL: the database system is in recovery mode
test_1_5_1_0 emove 2008-07-02 10:36:09.159 EEST FATAL: the database system is in recovery mode
...
test_1_5_1_0 emove 2008-07-02 10:36:09.209 EEST FATAL: the database system is in recovery mode
2008-07-02 10:36:09.210 EEST LOG: all server processes terminated; reinitializing
test_1_5_1_0 emove 2008-07-02 10:36:09.528 EEST FATAL: the database system is in recovery mode
...
test_1_5_0_0 postgres 2008-07-02 10:36:09.537 EEST FATAL: the database system is in recovery mode
2008-07-02 10:36:09.540 EEST LOG: database system was interrupted; last known up at 2008-07-02 10:12:57 EEST
test_1_5_1_0 emove 2008-07-02 10:36:09.542 EEST FATAL: the database system is in recovery mode
...
test_1_5_1_0 emove 2008-07-02 10:36:09.567 EEST FATAL: the database system is in recovery mode
2008-07-02 10:36:09.567 EEST LOG: database system was not properly shut down; automatic recovery in progress
test_1_5_1_0 emove 2008-07-02 10:36:09.572 EEST FATAL: the database system is in recovery mode
test_1_5_1_0 emove 2008-07-02 10:36:09.574 EEST FATAL: the database system is in recovery mode
test_1_5_1_0 emove 2008-07-02 10:36:09.575 EEST FATAL: the database system is in recovery mode
test_1_5_1_0 emove 2008-07-02 10:36:09.577 EEST FATAL: the database system is in recovery mode
2008-07-02 10:36:09.578 EEST LOG: redo starts at 4F/2600EFF0
test_1_5_1_0 emove 2008-07-02 10:36:09.578 EEST FATAL: the database system is in recovery mode
2008-07-02 10:37:09.073 EEST LOG: autovacuum launcher started
2008-07-02 10:37:09.074 EEST LOG: database system is ready to accept connections

I've attached my postgresql.conf and related /var/log/messages
parts. (Server has a memory and swap space of size 8GiG.) What might be
causing this problem? How can I configure postgresql.conf to avoid such
situations? Any kind of help will be really appreciated.

Regards.