Re: shared_buffers documentation

On Mon, Apr 19, 2010 at 6:06 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Mon, Apr 19, 2010 at 5:36 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> >> I don't actually know what's best. ?I'm just concerned that we have a
>> >> default in postgresql.conf and a tuning guide that says "don't do
>> >> that". ?Maybe the tuning guide needs to be more nuanced, or maybe
>> >> postgresql.conf needs to be changed, but it makes no sense to have
>> >> them saying contradictory things.
>> >
>> > The good news about checkpoint_segments is that you get a log file
>> > warning message if the value should be increased, i.e. you are
>> > checkpointing often than 30 seconds.
>>
>> Yeah.  I get that warning frequently when I'm creating test tables of
>> dummy data for PG devel purposes.  That's actually the main thing that
>> makes me think the default may be too low.
>
> Well, the point is that you are getting it for _unusual_ circumstances.
> Seems it is only when you are getting it for typical workloads that it
> should be increased.

I guess. I am not sure we should consider "doing a large CTAS" to be
an unusual workload, though. Sure, most of us don't do that every
day, but what do we get out of having it be slow when we do decide to
do it? Up until today, I had never heard anyone say that there was
any possible performance trade-off, and...

> However, this is the first time I am hearing that
> battery-backed cache favors the default value.

...if that's as bad as it gets, I'm still not sure we shouldn't
increase the default. Most people will not have their first
experience of PG on a server with a battery-backed RAID controller,
I'm thinking. And people who do have battery-backed RAID controllers
can tune the value down if need be. I have never yet heard anyone
justify why all the values in postgresql.conf should be defined as
"the lowest value that works best for at least 1 user".

Then again, I don't really know what I'm talking about. I think we
should be listening very carefully to people who have spent a lot of
time tuning this and taking their advice on how it should be set by
default.

...Robert