On 02/11/2012 07:53 PM, Jeff Janes wrote:
Has it ever been well-characterized what the problem is with>8GB? I've used shared buffers above that size for testing purposes and could never provoke a problem with it.
If anyone ever manages to characterize it well, we might actually make progress on isolating and fixing it. All we have so far are a couple of application level test results suggesting a higher value caused performance to drop. The first public one I remember was from Jignesh; http://archives.postgresql.org/pgsql-performance/2008-02/msg00184.php gives him quoting on where he found the Solaris roll-off was at. What we really need to stomp this one down is someone to find the same thing, then show profiler output in each case. Note that Jignesh's report included significant amount of filesystem level tuning, using things like more direct I/O, and that might be a necessary requirement to run into the exact variant of this limitation he mentioned.
I haven't spent a lot of time looking for this problem myself. What I've heard second-hand from more than one person now is a) larger settings than 8GB can be an improvement for some people still, and b) simple benchmarks don't always have this problem. I have noted that the few public and private reports I've gotten all suggest problems show up on benchmarks of more complicated workloads. I think Jignesh mentioned this being obvious in the more complicated TPC-derived benchmarks, not in simple things like pgbench. I may be misquoting him though. And given that one of the possible causes for this was an excess of some lock contention, it's quite possible this one is already gone from 9.2, given the large number of lock related issues that have been squashed so far in this release.
All of those disclaimers are why I think no one has pushed to put a note about this in the official docs. Right now the only suggested limit is this one:
"The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB."
The most common practical limit I've run into with large shared_buffers settings hits earlier than 8GB: running into checkpoint spike issues. I have installs that started with shared_buffers in the 4 to 8GB range, where we saw badly spiking I/O at checkpoint sync time. Lowering the databases cache can result in smarter writing decisions withing the OS, improving latency--even though total writes are actually higher if you measure what flows from the database to OS. That side of the latency vs. throughput trade-off existing is one of the main reasons I haven't gone chasing after problems with really large shared_buffers settings.
-- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
