On Fri, Nov 11, 2016 at 10:03 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> Right, but for other platforms, the recommendation seems to be 25% of
> RAM, can we safely say that for Windows as well? As per test results
> in this thread, it seems the read-write performance degrades when
> shared buffers have increased from 12.5 to 25%. I think as the test
> is done for a short duration so that degradation could be just a run
> to run to run variation, that's why I suggested doing few more tests.
Really, 25% of RAM with no upper limit? I've usually heard 25% of RAM
with a limit of 8GB, or less. I suspect that the performance for
large values of shared_buffers has improved in recent releases, but
there's one huge reason for not going too high: you're going to get
double buffering between shared_buffers and the OS cache, and the more
you raise shared_buffers, the worse that double-buffering is going to
get. Now, on the flip side, on a write-heavy workload, raising
shared_buffers will reduce the rate at which dirty buffers are evicted
which may save substantial amounts of I/O. But if you have, say, a
200GB and 128GB of RAM, would you really set shared_buffers to 32GB?
I wouldn't.
We're not really going to get out from under these issues until we
rewrite the system not to depend on buffered I/O, but I haven't gotten
around to that yet. :-)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company