From: | Volkan YAZICI <yazicivo(at)ttmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Memory Problem |
Date: | 2008-07-02 07:52:10 |
Message-ID: | 87abh0radx.fsf@alamut.mobiliz.com.tr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | A B | 2008-07-02 09:58:19 | Insert into ... returning ... before 8.2? |
Previous Message | Craig Ringer | 2008-07-02 07:24:38 | Re: Target lists can have at most 1664 entries? |