From: Chris <ctlajoie@gmail.com>
To: pgsql-performance@postgresql.org
Subject: How to troubleshoot high mem usage by postgres?
Date: Sat, 27 Feb 2010 15:29:13 -0700
Message-ID: <57033cf81002271429wb0da13am37b3cb6258103ea5@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ctlajoie@gmail.com
Subject: Re: How to troubleshoot high mem usage by postgres?
In-Reply-To: <57033cf81002271429wb0da13am37b3cb6258103ea5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox