Re: How does PG know if data is in memory?

Samuel Gendler wrote:
> As to your question about increasing shared_buffers to be some
> significant proportion of available RAM - apparently, that is not a
> good idea. I've seen advice that said you shouldn't go above 8GB for
> shared_buffers and I've also seen 12GB suggested as an upper limit,
> too. On my host with 48GB of RAM, I didn't see much difference
> between 8GB and 12GB on a fairly wide variety of tests, so mine is set
> at 8GB with an efective_cache_size of 36GB.

The publicly discussed tests done at Sun suggested 10GB was the
effective upper limit on Solaris before performance started dropping
instead of increasing on some of their internal benchmarks. And I've
heard privately from two people who have done similar experiments on
Linux and found closer to 8GB to be the point where performance started
to drop. I'm hoping to get some hardware capable of providing some more
public results in this area, and some improvements if we can get better
data about what causes this drop in efficiency.

Given that some write-heavy workloads start to suffer considerable
checkpoint issues when shared_buffers is set to a really high value,
there's at least two reasons to be conservative here. The big win is
going from the tiny default to hundreds of megabytes. Performance keeps
going up for many people into the low gigabytes range, but the odds of
hitting a downside increase too. Since PostgreSQL uses the OS cache,
too, I see some sytems with a whole lot of RAM where the 512MB - 1GB
range still ends up being optimal, just in terms of balancing the
improvements you get from things being in the cache vs. the downsides of
heavy checkpoint writes.

--
Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book