On Thu, Dec 13, 2018 at 11:51 PM Thomas Munro <thomas.munro@xxxxxxxxxxxxxxxx> wrote:
On Fri, Dec 14, 2018 at 2:17 AM Ron <ronljohnsonjr@xxxxxxxxx> 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.
