Re: pgtune and massive shared_buffers recommendation

On 05/21/2014 09:39 AM, Stuart Bishop wrote:

> I've got some boxes with 128GB of RAM and up to 750 connections, just
> upgraded to 9.3 so I'm revising my tuning. I'm getting a
> recommendation from pgtune to bump my shared_buffers up to 30GB and
> work_mem to 80MB. Is a shared_buffers this high now sane?

The pgtune utility from Greg Smith, so it can generally be considered
sane. However it's based very much on "rule of thumb" calculations that
aren't really valid for recent hardware. It was designed back in the
days when a 32GB machine was "big". It has since been updated, but
you'll notice it hasn't seen a significant update since PostgreSQL 9.1.

It basically boils down to this:

1. The previous rule of "8GB is the max" was simply a limitation.
2. With the limitation removed, people found greater values didn't
necessarily increase performance.
3. Larger values can be dangerous due to increased demand on
checkpoints, and more potential for dirty memory to cause write IO storms.
4. The OS is usually much better about managing cached pages.
5. PG can not protect itself from double-buffering currently, so every
block in shared_buffers may *also* be present in the OS page cache.

Given all of that, I could not personally recommend anything over 8GB
for all but the edgiest of edge-cases. The only real way to tell for
sure is with simulated and real-world tests. I'd start at 4GB, and try
adding 1GB at a time until benchmarks stop increasing with your working
data size. I suspect your results will top out before you even hit 8GB
anyway.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email