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