Memory Leak Utility

[Shawn]

On Fri, 22 Jun 2007 23:36:49 -0400
Kurt Wall <kwall at kurtwerks.com> wrote:

> 
> On Jun 22, 2007, at 11:01 PM, Shawn wrote:
> 
> > Hi guys,
> >
> > i believe I may have found a memory leak in the psql app under
> > PostgreSQL 8.2.4 that was not there in 8.2.3.   But I'd like to be a
> > bit more certain of my facts.  Is there a utility available to look
> > at memory usage and perhaps ways to free up memory in a leak type
> > situation, short of a reboot of course?
> >
> > Slackware 11.0 with updates.  Postgres compiled from source.
> 
> You can use Electric Fence to detect most memory errors, but I  
> believe what you'll have the most luck using is valgrind. It's
> really difficult to "free up memory leaks" because of how they occur:
> an application allocates memory and then fails to free it, usually
> by destroying the reference. Without that reference (pointer) to the  
> starting address of the allocated memory, there's no way for a  
> utility to access the memory. Killing the parent process *might*  
> allow the kernel to reclaim the memory.
> 
> What makes you think it's leaking memory?
> 
> Kurt
>

Hi Guys,

Well, being a bit of a neophyte, I run a lot of my queries using psql.
When I run a query against one of my larger tables, say I am checking
for duplicates between the main table and a holding table used to
import new records, the main table is up over 300 million and the
holding table has 58 million.  The query looks like this:

delete from only log_activity_holding using log_activity where
log_activity.event_at_utc = log_activity_holding.event_at_utc and
log_activity.caller = log_activity_holding.caller and
log_activity.system_id = log_activity_holding.system_id;

This can take several hours to complete, event with indexs for both
table on all the columns being tested.  

The machine is a Dual Quad core 2Ghz machine with 16G of RAM.  

After completing this query on either the server itself or another
remote machine using 8.2.4, the machine is extremely slow doing
anything else and will not perk up until a reboot is performed, then
all is back to normal.  I back rev'd my remote machine to 8.2.3 and run
the same query against the same data on the server, psql is running on
the terminal machine not the server, and I don't seem to get the same
slow down.

I realize my SQL isn't the greatest but I've been using this query
since 7.4.5 without the slowdown occuring.

Shawn