Re: Overhauling GUCS - Mailing list pgsql-hackers

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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Regards,

Paul van den Bogaard

---------------------------------------------------------------------------------------------
Paul van den Bogaard                               Paul.vandenBogaard@sun.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