DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=n+QF3G4Wim5SevxeoM4/Ax0PtMX35IBDugetb7KVkuEf8C7CZyGt2qMj5GTKVywg5H
	gTEuCsli9hbUZjfVZgTQKwJWRWIHQ2CptABMxR4zGfTaEJeenwKGcfJIv5Ql62m+OxtR
	3ptHKn07Zm7uzjUv3iWzsCO3or/TuTdTpw0OU=
MIME-Version: 1.0
Date: Sat, 27 Feb 2010 15:29:13 -0700
Message-ID: <57033cf81002271429wb0da13am37b3cb6258103ea5@mail.gmail.com>
Subject: How to troubleshoot high mem usage by postgres?
From: Chris <ctlajoie@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8

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.

Most PHP scripts are not long-running and properly releasing the
resources using the provided functions in the pgsql PHP extension is
not necessary. However since I do have a long-running script, I have
taken steps to ensure everything is being properly released when it is
no longer needed (I am calling the functions provided, but I don't
know if the pgsql extension is doing the right thing). In spite of
this, the longer the script runs and processes records, the more
memory increases. It increases to the point that system memory is
exhausted and it starts swapping. I killed the process at this point.

I monitored the memory with top. here are the results.. the first is
10 seconds after my script started running. The second is about 26
seconds.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  DATA COMMAND
17461 postgres  16   0  572m 405m  14m S 20.0 10.7   0:10.65 422m postmaster
17460 root      15   0  136m  14m 4632 S 10.6  0.4   0:06.16  10m php
17462 postgres  15   0  193m  46m 3936 D  3.3  1.2   0:01.77  43m postmaster


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  DATA COMMAND
17461 postgres  16   0 1196m 980m  17m S 19.0 26.0   0:25.72 1.0g postmaster
17460 root      15   0  136m  14m 4632 R 10.3  0.4   0:14.31  10m php
17462 postgres  16   0  255m 107m 3984 R  3.0  2.9   0:04.19 105m postmaster


If I am indeed doing everything I can to release the resources (and
I'm 95% sure I am) then it looks like the pgsql extension is at fault
here.
Regardless of who/what is at fault, I need to fix it. And to do that I
need to find out what isn't getting released properly. How would I go
about that?

Thanks,
Chris