Hello John,
>> we see up to 10x performance increase with bigger shared_buffers in case
>> of this database. Main database entities are about 20GB in size and we see
>> that performance drops considerably when running with smaller
>> shared_buffers smaller then that.
>>
>>
> do you adjust effective_cache_size accordingly? with the smaller
> shared_buffers, we typically find at least half or more of physical memory
> is available as OS level disk cache, as shown by the 'cached' output of
> 'free' or whatever after the system has been running long enough to fully
> populate its disk cache. this parameter has a significant performance
> impact on the planner's estimation of the best way of executing given
> queries. also, especially if you're executing queries that process a lot
> of rows and have to do sorts and such, increasing work_mem is quite helpful.
>
>
Yes, the effective_cache_size is set to the the 50% of the RAM = 64GB, but
as I mentioned already, we are measuring considerable performance increase
when increasing shared_buffers to the values, when it fits most important
tables completely.
Regards,
-- Valentine