On Thu, Aug 11, 2011 at 7:27 PM, Waldo Nell <pwnell@telkomsa.net> wrote:
>
> On 2011-08-11, at 17:18 , ktm@rice.edu wrote:
>
>> One guess is that you are using the defaults for other costing parameters and they
>> do not accurately reflect your system. This means that it will be a crap shoot as
>> to whether a plan is faster or slower and what will affect the timing.
>
> Ok, but I thought the way to best optimise PostgreSQL is to start with the parameters having the biggest impact and
workfrom there. To adjust multiple parameters would not give a clear indication as to the benefit of each, as they may
canceleach other out.
A couple points:
*) shared buffers is a highly nuanced setting that is very workload
dependent. it mainly affects write heavy loads, and the pattern of
writing is very important in terms of the benefits you may or may not
see. it also changes checkpoint behavior -- this will typically
manifest as a negative change with raising buffers but this can be
mitigated. if your i/o becomes very bursty after raising this setting
it's a red flag that more tuning is required.
*) fsync = off: throw the book out on traditional tuning advice. with
this setting (dangerously) set, the o/s is essentially responsible for
i/o patterns so you should focus your tuning efforts there. the
benefits of raising shared buffers don't play as much in this case.
> To test your theory, what other parameters should I be looking at? Here are some more with their current values:
>
> random_page_cost = 4.0
> effective_cache_size = 128MB
*) these settings affect query plans. changing them could have no
affect or dramatic effect depending on the specific queries you have
and if they or chosen badly due to overly default conservative
settings. the postgresql planner has gotten pretty accurate over the
years in the sense that you will want to tune these to be as close to
reality as possible.
In my opinion before looking at postgresql.conf you need to make sure
your queries and their plans are good. fire up pgfouine and see where
those 60 minutes are gettings spent. maybe you have a problem query
that demands optimization.
merlin
