Re: How to troubleshoot high mem usage by postgres?

On Sat, Feb 27, 2010 at 3:38 PM, Ben Chobot <bench(at)silentmedia(dot)com> wrote:
> In your postgresql.conf file, what are the settings for work_mem and
> shared_buffers?

I have not done any tuning on this db yet (it is a dev box). It is
using defaults.
shared_buffers = 32MB
#work_mem = 1MB

I do appreciate the several quick responses and I will work on
responding to the them.

@Craig Ringer:
select version() reports:
PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit
The system has 4GB of RAM.
The postgres log currently does not show any useful information. Only
thing in there for today is an "Unexpected EOF on client connection"
because I killed the process after it started swapping.

The test input for my PHP script is a csv file with about 450,000
records in it. The php script processes the each csv record in a
transaction, and on average it executes 2 insert or update statements
per record. I don't think the specific statements executed are
relevant (they are just basic INSERT and UPDATE statements).

I will try to come up with a short script that reproduces the problem.

@Tom Lane:
As I mentioned above I am not doing everything in a single
transaction. However I do want to try your suggestion regarding
getting a "memory context map". But I'm afraid I don't know how to do
what you are describing. How can I set the ulimit of postmaster? And
does the postmaster stderr output go to the postgres log file? If not,
where can I find it?

Thanks again,
Chris