On Thu, Aug 11, 2011 at 7:27 PM, Waldo Nell <pwnell@xxxxxxxxxxxx> wrote: > > On 2011-08-11, at 17:18 , ktm@xxxxxxxx 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 work from there. To adjust multiple parameters would not give a clear indication as to the benefit of each, as they may cancel each 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
