Re: shared_buffers on Big RAM systems

On Thu, Dec 13, 2018 at 11:51 PM Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Fri, Dec 14, 2018 at 2:17 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
> > https://www.postgresql.org/docs/9.6/runtime-config-resource.html
> >
> > The docs say, "If you have a dedicated database server with 1GB or more
> of
> > RAM, a reasonable starting value for shared_buffers is 25%".
> >
> > But that's pretty archaic in 2018. What if the dedicated database server
> > has 128GB RAM?
>
> I agree, we might as well drop the words "with 1GB of more of RAM".
> That's the size of the very smallest cloud instances available these
> days, available for free or up to a few bucks a month, and for
> physical servers I wonder if you can still get DIMMs that small.
>

AWS still has some with 512MB. Although it can be a challenge to get
anything to compile in that amount of memory if there is anything else
running.

But I don't think I would recommend starting at 25% of RAM larger server.
Is that really good advice? I would usually start out at 1GB even if the
server has 128GB, and increase it only if there was evidence it needed to
be increased. Due to double buffering between shared_buffers and OS cache,
25% seems like a lot of wasted space. You need shared_buffers as a cooling
off tank where dirty data can wait for their corresponding WAL to get
flushed in the background before they get written out themselves. I think
1GB is enough for this, even if you have 128GB of RAM.

If your entire database (or the active portion of it) fits in RAM, then it
probably makes sense to set shared_buffers high enough to hold your entire
database. But if it doesn't fit in RAM, then I don't see a reason to
devote even 25% of a large server to shared_buffers.