IMNSHO that's
tackling things from the wrong end. If 12GB of shared
buffers drive
your 48GB dedicated OLTP postgres server into swapping out
actively
used pages, the problem isn't the 12GB of shared buffers, but
that
you require so much memory for other things. That needs to be
fixed.
But!
We haven't even established that swapping is an actual problem
here.
The ~2GB of swapped out memory could just as well be the java raid
controller
management monstrosity or something similar. Those pages
won't ever
be used and thus can better be used to buffer IO.
You can check
what's actually swapped out using:
grep ^VmSwap
/proc/[0-9]*/status|grep -v '0 kB'
For swapping to be actually
harmful you need to have pages that are
regularly swapped in. vmstat
will tell.
In a concurrent OLTP workload (~450 established
connections do suggest
that) with a fair amount of data keeping the
hot data set in
shared_buffers can significantly reduce problems.
Constantly searching
for victim buffers isn't a nice thing, and that
will happen if your most
frequently used data doesn't fit into s_b.
On the other hand, if your
data set is so large that even the hottest
part doesn't fit into memory
(perhaps because there's no hottest
part as there's no locality at all),
a smaller shared buffers can
make things more efficient, because the
search for replacement
buffers is cheaper with a smaller shared buffers
setting.
Greetings,
Andres
Freund
Here's the
problem with a large shared_buffers on a machine that's
getting
pushed into swap. It starts to swap BUFFERs. Once buffers
start
getting swapped you're not just losing performance, that huge
shared_buffers
is now working against you because what you THINK are
buffers in RAM
to make things faster are in fact blocks on a hard
drive being
swapped in and out during reads. It's the exact opposite
of fast. :)
That imo
doesn't really have anything to do with it. The primary benefit
of a
BBU with writeback caching is accelerating (near-)synchronous
writes.
Like the WAL. But, besides influencing the default for
wal_buffers, a
larger shared_buffers doesn't change the amount of
synchronous
writes.
Greetings,
Andres Freund
On Sun, Mar 15, 2015 at 8:20 PM, Andres Freund <andres@xxxxxxxxxxxxxxx> wrote:
On 2015-03-15 11:09:34 -0600, Scott Marlowe wrote:
shared_mem of 12G is almost always too large. I'd drop it down to ~1G or so.
I think that's a outdated wisdom, i.e. not generally true.
Quite agreed. With note, that proper configured controller with BBU is needed.
A new enough kernel, a sane filesystem
(i.e. not ext3) and sane checkpoint configuration takes care of most of
the other disadvantages.
Most likely. And better to be sure that filesystem mounted without barrier.
And I agree with Scott - 64MB work mem AND max_connections = 500 is a
recipe for disaster. The problem could be in session mode of
pgbouncer. If you can work with transaction mode - do it.
Best regards,
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@xxxxxxxxxxxxxxxxxxxxxxxxx
I think that's a
outdated wisdom, i.e. not generally true. I've now seen
a
significant number of systems where a larger shared_buffers can help
quite
massively. The primary case where it can, in my experience, go
bad
are write mostly database where every buffer acquiration has to
write
out dirty data while holding locks. Especially during relation
extension
that's bad. A new enough kernel, a sane filesystem
(i.e. not ext3)
and sane checkpoint configuration takes care of most of
the other
disadvantages.
Greetings,
Andres Freund
