Re: Why shared_buffers max is 8GB?

> On most machines the limit is higher than you'd ever want to set it. I
> have a set of servers with 1TB RAM and shared buffers on them is set
> to 10G and even that is probably higher than it needs to be. The old
> 1/4 of memory advice comes from the days when db server memory
> was in the 1 to 16GB range and even then it was more of a starting place. It
> has been found through experience and experiment that few setups
> can use more shared buffers than a few gigabytes and get better
> performance.

This is really the core of the issue. You can set shared_buffers to almost any level, into multiple TBs if you really wanted to. Whether or not this is prudent however, is entirely different. There are many considerations at play with shared buffers:

* Shared buffers must (currently) compete with OS inode caches. If this is shared buffers are too high, much of the cached data is already cached by the operating system, and you end up with wasted RAM.
* Checkpoints must commit dirty shared buffers to disk. The larger this is, the more risk you have when checkpoints come, up to and including an unresponsive database. Writing to disks isn't free, and sadly this is still on the slower side unless all of your storage is SSD-based. You don't want to set this too much higher than your disk write cache.
* Performance gains taper off quickly. Most DBAs don't see gains after 4GB, and fewer still see any gains above 8GB. We have ours set at 4GB after a lot of TPS and risk analysis.
* Since shared_buffers is the amount of memory that could potentially remain uncommitted to data files, the larger this is, the longer crash recovery can take. Having this too high could mean the difference between a five-minute outage, and a five-second outage. The checkpoint_* settings control how this is distributed and maintained, but the risk starts here.

With that said, we really need to update the WIKI page to reflect all of this. It's still claiming the 25% memory rule:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

______________________________________________

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