Thread: Problem with large select - PostgreSQL starts eating memory/disk
Problem with large select - PostgreSQL starts eating memory/disk
From
calmarty@hotmail.com (Martin)
Date:
I am having a problem with an INSERT into a table from a SELECT which joins over 15 tables none of which have over 3K tuples. I run it multiple times with different values as part of my application and it randomly locks up when executed with different parameters. When I say lock up, I mean that it never finishes executing and that memory usage creeps up to the maximum (according to "top" - 1 GB from ~120 MB during normal operations) and it starts taking up the disk space for the partition with the data files - /var/lib/pgsql (goes from ~100 MB to ~12 GB over 45 minutes). It finally dies when there is no disk space left. If I drop the database and recreate, this goes back to 120 MB. We are running PostgreSQL 7.1.3 compiled on Red Hat 7.1 which runs on a dual Pentium III 1 GHz box with a RAID 5 setup. I would suspect that it is a problem with our query but the odd part is that it will succeed for a few iterations (from 2 to 20) before locking up. We have done all the normal optimizations/tuning including creating indices on all the columns in the join and setting the OS parameters according to the PostgreSQL documentation. I looked every where for other examples of this behavior and could not find anything. I wondered if it was an issue with PostgreSQL splitting processes over the CPUs but have found numerous examples of success running PostgreSQL on multiple CPU servers and there should be no reason why a database couldn't split its processes over multiple CPUs. Has anyone ever come across this problem?
Re: Problem with large select - PostgreSQL starts eating memory/disk
From
Martijn van Oosterhout
Date:
On Thu, Aug 30, 2001 at 05:51:14PM -0700, Martin wrote: > I am having a problem with an INSERT into a table from a SELECT which > joins over 15 tables none of which have over 3K tuples. I run it > multiple times with different values as part of my application and it > randomly locks up when executed with different parameters. When I say > lock up, I mean that it never finishes executing and that memory usage > creeps up to the maximum (according to "top" - 1 GB from ~120 MB > during normal operations) and it starts taking up the disk space for > the partition with the data files - /var/lib/pgsql (goes from ~100 MB > to ~12 GB over 45 minutes). It finally dies when there is no disk > space left. If I drop the database and recreate, this goes back to 120 > MB. Have you running VACUUM ANALYZE recently? When you do an EXPLAIN on the query, how long does it think it will take? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.