On Mon, Aug 29, 2011 at 2:38 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Aug 29, 2011 at 3:36 PM, Lonni J Friedman <netllama@gmail.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).
