Re: 2GB or not 2GB

["Jignesh K. Shah" <J.K.Shah@xxxxxxx>]

Josh Berkus wrote:
> Folks,
>
> Subsequent to my presentation of the new annotated.conf at pgCon last week, 
> there's been some argument about the utility of certain memory settings 
> above 2GB.  I'd like to hash those out on this list so that we can make 
> some concrete recomendations to users.
>
> shared_buffers:  according to witnesses, Greg Smith presented at East that 
> based on PostgreSQL's buffer algorithms, buffers above 2GB would not 
> really receive significant use.  However, Jignesh Shah has tested that on 
> workloads with large numbers of connections, allocating up to 10GB 
> improves performance. 
>   
I have certainly seen improvements in performance upto 10GB using 
EAStress. The delicate balance is between file system cache and shared 
buffers. I think the initial ones are more beneficial at shared buffers 
level and after that file system cache.
I am trying to remember Greg's presentation where I think he suggested 
more like 50% of available RAM (eg in 4GB system used just for 
PostgreSQL, it may not help setting more than 2GB since you need memory 
for other stuff also).. Right Greg?

But if you have 32GB RAM .. I dont mind allocating 10GB to PostgreSQL 
beyond which I find lots of other things that begin to impact..

BTW I am really +1 for just setting AvailRAM tunable for PostgreSQL 
(example that you showed in tutorials) and do default derivations for 
all other settings unless overridden manually.  So people dont forget to 
bump up wal_buffers  or one of them while bumping the rest and trying to 
fight why the hell they are not seeing what they are expecting.

-Jignesh