Thread: 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.