Re: Big Memory Boxes and pgtune

On Wed, Nov 2, 2016 at 5:46 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 10/28/16 2:33 PM, Joshua D. Drake wrote:
>>
>> * A very high shared_buffers (in newer releases, it is not uncommon to
>> have many, many GB of)
>
>
> Keep in mind that you might get very poor results if shared_buffers is
> large, but not large enough to fit the entire database. In that case buffer
> replacement will be *extremely* expensive. Some operations will use a
> different buffer replacement strategy, so you might be OK if some of the
> database doesn't fit in shared buffers; that will depend a lot on your
> access patterns.

This. Especially on machines with fast CPUs / memory and SSDs
underneath, lots of buffers can sometimes just get in the way. The
linux kernel (and most other kernels) has hundreds, even thousands of
man hours put into the file caching code and it's often faster to let
the kernel do that job with the extra memory.

Only a benchmark of a production type load can tell you what to
expect, and only production itself will reveal the absolute truth.
Where I used to work we had 5TB databases on machines with anywhere
from 128GB to 512GB and honesly the extra memory didn't make a huge
difference. They had 8 disk RAID-5 arrays with controller caching
turned off, because it got in the way, and cranking up shared_buffers
didn't not make them faster. I think we settled on something under
10GB on most of them