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).
A few examples:
0) system A: 56GB RAM, running postgresql-8.4.8 with the following parameters:
maintenance_work_mem = 96MB
effective_cache_size = 40GB
work_mem = 256MB
wal_buffers = 16MB
shared_buffers = 13GB
max_connections = 300
1) system B: 120GB RAM, running postgresql-9.0.4 with the following parameters:
maintenance_work_mem = 1GB
effective_cache_size = 88GB
work_mem = 576MB
wal_buffers = 4MB
shared_buffers = 28GB
max_connections = 200
thanks