Why couldn't Postgres learn for itself what the optimal performance
settings would be? The easy one seems to be the effective_cache_size.
Top shows us this information. Couldn't Postgres read that value from
the same place top reads it instead of relying on a config file value?
Seems like it could even adjust to changing conditions as the cache
size changes.
Wouldn't it be great to set a single parameter in postgresql.conf
like:
learn = on
This would make Postgres run the same queries multiple times with
different settings, trying to find the ones that made the query run the
fastest. Obviously you wouldn't want this on all the time because
Postgres would be doing more work than it needs to satisfy the
applications that are asking it for data. You'd leave it running like
this for as long as you think it would need to get a sampling of real
world use for your specific application.
Something like this could automagically adapt to load, hardware,
schema, and operating system. If you drop another 1GB of RAM into the
machine, just turn the learning option on and let Postgres tune itself
again.
-M@
On Thursday, July 3, 2003, at 04:25 PM, Sean Chittenden wrote:
>> I'm curious how many of the configuration values can be determined
>> automatically, or with the help of some script. It seem like there
>> could be some perl script in contrib that could help figure this out.
>> Possibly you are asked a bunch of questions and then the values are
>> computed based on that. Something like:
>>
>> How many tables will the system have?
>> How much memory will be available to the postmaster?
>> How many backends will there typically be?
>> What is the avg seek time of the drive?
>> What's the transfer rate of the drive?
>>
>> Seems to me that a lot of reasonable default values can be figure out
>> from these basic questions. FSM settings, Sort Mem, Random Page Cost,
>> Effective Cache Size, Shared Memor, etc, etc.
>
> Someone was working on a thing called pg_autotune or some such program
> that'd do exactly what you're thinking of.
>
> http://archives.postgresql.org/pgsql-performance/2002-10/msg00101.php
> http://gborg.postgresql.org/project/pgautotune/projdisplay.php
>
>
> --
> Sean Chittenden
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
