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@xxxxxxxxxxxxxxx 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
