To: Chris <ctlajoie@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: How to troubleshoot high mem usage by postgres? 
In-reply-to: <57033cf81002271429wb0da13am37b3cb6258103ea5@mail.gmail.com> 
References: <57033cf81002271429wb0da13am37b3cb6258103ea5@mail.gmail.com>
Comments: In-reply-to Chris <ctlajoie@gmail.com>
	message dated "Sat, 27 Feb 2010 15:29:13 -0700"
Date: Sat, 27 Feb 2010 17:39:33 -0500
Message-ID: <18568.1267310373@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris <ctlajoie@gmail.com> writes:
> Hi, I'm having an issue where a postgres process is taking too much
> memory when performing many consecutive inserts and updates from a PHP
> script (running on the command line). I would like to know what sort
> of logging I can turn on to help me determine what is causing memory
> to be consumed and not released.

Are you doing all these inserts/updates within a single transaction?

If so, I think the odds are very good that what's eating the memory
is the list of pending trigger actions, resulting from either
user-created triggers or foreign-key check triggers.  The best way
of limiting the problem is to commit more often.

If you want to try to confirm that, what I would do is run the
postmaster under a more restrictive ulimit setting, so that it
runs out of memory sometime before the system starts to swap.
When it does run out of memory, you'll get a memory context map
printed to postmaster stderr, and that will show which context
is eating all the memory.  If it's "AfterTriggerEvents" then my
guess above is correct --- otherwise post the map for inspection.

			regards, tom lane