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(at)2ndQuadrant(dot)com www.2ndQuadrant.us