Re: How to troubleshoot high mem usage by postgres?

From: Tom Lane
Subject: Re: How to troubleshoot high mem usage by postgres?
Date: ,
Msg-id: 18568.1267310373@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: How to troubleshoot high mem usage by postgres?  (Chris)
List: pgsql-performance

Tree view

How to troubleshoot high mem usage by postgres?  (Chris, )
 Re: How to troubleshoot high mem usage by postgres?  (Ben Chobot, )
  Re: How to troubleshoot high mem usage by postgres?  (Chris, )
   Re: How to troubleshoot high mem usage by postgres?  (Tom Lane, )
 Re: How to troubleshoot high mem usage by postgres?  (Craig Ringer, )
  Re: How to troubleshoot high mem usage by postgres?  (Chris, )
 Re: How to troubleshoot high mem usage by postgres?  (Tom Lane, )

Chris <> 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


pgsql-performance by date:

From: Ron Mayer
Date:
Subject: Re: SSD + RAID
From: Greg Smith
Date:
Subject: Re: SSD + RAID