Re: heavy swapping, not sure why

On Mon, Aug 29, 2011 at 2:38 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Aug 29, 2011 at 3:36 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>> I have several Linux-x68_64 based dedicated PostgreSQL servers where
>> I'm experiencing significant swap usage growth over time.  All of them
>> have fairly substantial amounts of RAM (not including swap), yet the
>> amount of swap that postgres is using ramps up over time and
>> eventually hurts performance badly.  In every case, simply restarting
>> postgresql frees up all the swap in use (until it ramps up again
>> later).  I'm assuming that I have at least one postgresql.conf
>> parameter set wrong, but I'm not sure which.  I read that
>> (max_connections * work_mem) should never exceed physical RAM, and if
>> that's accurate, then I suspect that's the root of my problem on
>> systemA (below).  However, I'd like confirmation before I start
>> tweaking things, as one of these servers is in production, and I can't
>> easily tweak settings to experiment (plus this problem takes a few
>> weeks before swapping gets bad enough to impact performance).
>
> using any C code in the backend? this includes 3rd party libraries
> which link in C, including postgis, pljava, xml2, etc.  Any features
> being used not included in the standard core distribution are
> interesting.

Nope, nothing like that. They're fairly generic setups, with nothing
added that isn't part of the core distribution.

>
> How long do your database connections stay open? forever? If yes, is
> memory distributed semi-evenly across all postgres processes or only
> to particular ones? If no, do you see excessive consumption with the
> non user backends like the stats collector, etc?

Nope, nothing is forever, everything is a fairly brief connection (a
few seconds, tops, with most under 1s). Although I do have pgbouncer
sitting in front of systemA to serve as a connection pooler.

>
> Anything else running on these boxes?  Application server?  Anything
> written in java?

Nothing. They're all 100% dedicated to postgres, and don't run
anything else beyond the basic OS level stuff (crond, etc).