Re: shared_buffers documentation

Kevin Grittner wrote:
> Perhaps, but be aware the current default benchmarked better
> than a larger setting in bulk loads.
>  
> http://archives.postgresql.org/pgsql-hackers/2009-06/msg01382.php
>  
> The apparent reason is that when there were fewer of them the WAL
> files were re-used before the RAID controller flushed them from BBU
> cache, causing an overall reduction in disk writes.  I have little
> doubt that *without* a good BBU cached controller a larger setting
> is better, but it's not universally true that bigger is better on
> this one

After running some tests, I believe what you observed is more universal 
than that, because I've been able to replicate a performance drop from a 
checkpoint_segments increase on a system without a BBWC (laptop with 
write caching turned off) where I really expected it to help.  My 
working theory is that are a broader set of situations where limiting 
the working set of WAL files to a small number in order to decrease 
cache disruption applies than just when you've got hardware caching 
involved.

However, I believe the guidelines to increasing this parameter along 
with shared_buffers still applies.  The real case for wins with more 
segments is when you also have a large buffer cache, because that's 
where the write savings from postponed database writes to often used 
blocks becomes easy to measure.  I've found it difficult today to 
demonstrate a slam-dunk bulk loading improvement through 
checkpoint_segments increase when shared_buffers is fixed at its default 
of ~32MB.  If that keeps up, I might soon have enough data to bust the 
idea that it alone improves bulk loading performance when you haven't 
touched anything else in the default config, which was unexpected to 
me.  Will report back once I've got a full handle on it.

Thanks for reminding me about this counter example, it slipped by in 
that broader thread before and I didn't try doing that myself until 
today, to see that you're onto something there.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us