Re: BUG #7494: WAL replay speed depends heavily on the shared_buffers size

On 08/15/12 9:02 AM, Valentine Gogichashvili wrote:
>
> On Wed, Aug 15, 2012 at 3:40 PM, John R Pierce <pierce(at)hogranch(dot)com
> <mailto:pierce(at)hogranch(dot)com>> wrote:
>
> On 08/15/12 6:36 AM, Bruce Momjian wrote:
>
> We warn against making shared buffers > 8GB, and this is
> perhaps another
> good reason.
>
>
> I generally keep it at no more than 2gb as I've never found any
> performance improvements going higher, on systems with 48gb ram,
> and had more than a few performance degradations with larger
> shared buffers.
>
>
> 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.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast