Re: Overhauling GUCS

to add some complexity to this topic :-) Please note I admit upfront
I am not familiar with every parameter out there, but during my quest
in finding bottleneck while stressing the back-end I find many GUC
parameters with names that show they should be interesting.
I read the comments, the docs (that I know of), go into the source to
learn the parameters and their units. And wondering if altering a
setting would do miracles in my case.

Indeed how can I measure the effect of a new setting? Enhanced
throughput? I learned in the past that fiddling with parameter A in
a database that was not properly setup/used --and there were many of
these--, had side effects in other parts of the engine. Yes, increased
throughput was observed. A new hype created. In the end it turned out
the parameter A was not set correctly at all. That parameter B, once
set to a sane value, cured the wrong behavior, and parameter A was not
optimal at all after the cure. We were just side tracked because we
did not know. Incorrect "knowledge" was borne (parameter A
setting). Throughout the years this database product has matured,
many more parameter realized and much, much more instrumentation been
implemented. It still is quite a challenge to understand what is
happening. But proper analysis is possible indeed. The black box is
much more open now.

One current example: wal_writer_delay. In my team there is an advise
to set this parameter to 100. However, after implementing a counter
(home grown instrumentation) I now know that the background log flush
routine is never called when stressing the database. Therefore I now
think the best setting is 10000 (its maximum) since it does not do
useful work (in my context) and therefore should wake up as little
times as possible. Without this instrumentation I can only guess about
the usability of this parameter and spend many tests in order to get
an impression of its validity to me.

So overhauling the GUC parameters is one step, but adding proper
instrumentation in order to really measure the impact of the new
setting is necessary too. Especially when looking in the direction of
auto tuning. Proper measurement is crucial to enable correct analysis.

Of course I am in favor of doing this with DTrace, however not all
platforms can benefit in that case :-)

--Paul

On 2 jun 2008, at 20:06, Josh Berkus wr
> Greg,
>
>> Like some of the other GUC simplification ideas that show up
>> sometimes
>> (unifying all I/O and limiting background processes based on that
>> total
>> is another), this is hard to do internally. Josh's proposal has a
>> fair
>> amount of work involved, but the code itself doesn't need to be
>> clever
>> or too intrusive. Unifying all the memory settings would require
>> being
>> both clever and intrusive, and I doubt you'll find anybody who could
>> pull it off who isn't already overtasked with more important
>> improvements for the 8.4 timeframe.
>
> Plus, I'm a big fan of "enable an API" rather than "write a
> feature". I
> think that there are people & companies out there who can write better
> autotuning tools than I can, and I'd rather give them a place to plug
> those tools in than trying to write autotuning into the postmaster.
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Regards,

Paul van den Bogaard

---------------------------------------------------------------------------------------------
Paul van den Bogaard Paul(dot)vandenBogaard(at)sun(dot)com
ISV-E -- ISV Engineering, Opensource Engineering group

Sun Microsystems, Inc phone: +31
334 515 918
Saturnus 1 extentsion:
x (70)15918
3824 ME Amersfoort mobile: +31
651 913 354
The Netherlands fax:
+31 334 515 001