Lonni J Friedman <netllama@gmail.com> writes:
> 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).
If you're certain that it's restarting *postgres* that does it, and not
restarting your application or pgbouncer or some other code, then it
seems like you must have uncovered a memory leak someplace. We haven't
got nearly enough info here to diagnose it though.
First thing I'd want to know is which process(es) exactly are bloating.
The top output you showed us is unhelpful for that since it just shows
them all as "postmaster" --- you'll need to match up the problem PIDs
with "ps auxww" output. Keep in mind also that top is pretty awful
about distinguishing a process's actual memory use (private memory)
from the portion of PG's shared memory that it happens to have touched.
What you need to pay attention to is RES minus SHR, not either number
alone. With shared buffers set as high as you've got it, you'll
probably not be able to be sure that a process is bloating until it's
eaten hundreds of megs of private space.
Where we go from there will depend on what you find out ... but if
possible, don't restart the server right away, or it'll probably be
another couple weeks before you can do the next round of investigation.
regards, tom lane