Re: shared_buffers advice

On Tue, May 25, 2010 at 5:58 AM, Konrad Garus <konrad(dot)garus(at)gmail(dot)com> wrote:
> 2010/5/24 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>
>> *) a page fault to disk is a much bigger deal than a fault to pg cache
>> vs os/ cache.
>
> That was my impression. That's why I did not touch our 2/16 GB setting
> right away. I guess that 2 more gigabytes in OS cache is better than 2
> more (duplicated) gigabytes in PG shared_buffers. In our case 2 GB
> shared_buffers appears to be enough to avoid thrashing between OS and
> PG.
>
>> *) shared_buffers is one of the _least_ important performance settings
>> in postgresql.conf
>>
>> Many settings, like work_mem, planner tweaks, commit settings,
>> autovacuum settings
>
> Can you recommend any sources on these parameters, especially commit
> settings and planner tweaks?
>
>
> Thank you so much for the whole answer! Not only it addresses the
> immediate question, but also many of the unasked that I had in the
> back of my head. It's brief and gives a broad view over all the
> performance concerns. It should be part of documentation or the first
> page of performance wiki. Have you copied it from somewhere?

Thank you for your nice comments. This was strictly a brain dump from
yours truly. There is a fairly verbose guide on the wiki
(http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server).
There is a lot of good info there but it's missing a few things
(from_collapse_limit for example).

I would prefer to see the annotated performance oriented .conf
settings to be written in terms of trade offs (too low? X too high? Y
setting in order to get? Z). For example, did you know that if crank
max_locks_per_transaction you also increase the duration of every
query that hits pg_locks() -- well, now you do :-).

merlin