> 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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to