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.
Volkan YAZICI <yazicivo@ttmail.com> writes:
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.
Are there any foreign keys referencing this table? If so, you're
probably running out of memory for the list of pending trigger events
(to verify that the FK constraint isn't violated by the delete).
Allowing the triggers to fire individually would take forever anyway,
so it might be best to drop the foreign key constraint(s) and then
re-establish them after the delete.
regards, tom lane
Hi,
On Wed, 02 Jul 2008, Tom Lane <tgl@sss.pgh.pa.us> writes:
Are there any foreign keys referencing this table? If so, you're
probably running out of memory for the list of pending trigger events
(to verify that the FK constraint isn't violated by the delete).Allowing the triggers to fire individually would take forever anyway,
so it might be best to drop the foreign key constraint(s) and then
re-establish them after the delete.
Thanks for the warning. Temporarily disabling FKs did the job. I knew
the problem about triggers, but didn't expect same applies for FKs
also. I'm very suprised FKs cause memory exhaustion too.
Regards.