Re: Moving postgresql.conf tunables into 2003...

От: Matthew Hixson
Тема: Re: Moving postgresql.conf tunables into 2003...
Дата: ,
Msg-id: 5B88D580-B04B-11D7-96EE-000393669C1A@poindextrose.org
(см: обсуждение, исходный текст)
Ответ на: Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden)
Список: pgsql-performance

Скрыть дерево обсуждения

Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  (Rod Taylor, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  ("scott.marlowe", )
  Re: Moving postgresql.conf tunables into 2003...  (Brian Hirt, )
   Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
    Re: Moving postgresql.conf tunables into 2003...  (Matthew Hixson, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
 Re: Moving postgresql.conf tunables into 2003...  ("Michael Mattox", )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
   Re: Moving postgresql.conf tunables into 2003...  (Bruce Momjian, )
 Re: Moving postgresql.conf tunables into 2003...  (Ron, )
 Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Rod Taylor, )
   Re: Moving postgresql.conf tunables into 2003...  (Manfred Koizar, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
     Re: Moving postgresql.conf tunables into 2003...  (Manfred Koizar, )
      Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
       Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
        Index correlation (was: Moving postgresql.conf tunables into 2003... )  (Manfred Koizar, )
     Use of multipart index with "IN"  (Rob Messer, )
      Re: Use of multipart index with "IN"  (Tom Lane, )
 Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
  Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
   Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
    Re: Moving postgresql.conf tunables into 2003...  (Sean Chittenden, )
     Re: Moving postgresql.conf tunables into 2003...  (Josh Berkus, )
   Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )
    Re: Moving postgresql.conf tunables into 2003...  (Michael Pohl, )
     Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
     Re: Moving postgresql.conf tunables into 2003...  (Bruce Momjian, )
    Re: Moving postgresql.conf tunables into 2003...  (Chris Travers, )
     Re: Moving postgresql.conf tunables into 2003...  ("Jim C. Nasby", )
   Re: Moving postgresql.conf tunables into 2003...  (Andrew Sullivan, )
    Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )
     Re: Moving postgresql.conf tunables into 2003...  ("Michael Mattox", )
     Re: Moving postgresql.conf tunables into 2003...  (Tom Lane, )
      Re: Moving postgresql.conf tunables into 2003...  (Kaarel, )
       Re: Moving postgresql.conf tunables into 2003...  ("scott.marlowe", )
        Re: Moving postgresql.conf tunables into 2003...  (Martin Foster, )
  Re: Moving postgresql.conf tunables into 2003...  ("Matthew Nuzum", )

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
>



В списке pgsql-performance по дате сообщения:

От: "Matthew Nuzum"
Дата:
Сообщение: Re: Moving postgresql.conf tunables into 2003...
От: "Michael Mattox"
Дата:
Сообщение: Re: Moving postgresql.conf tunables into 2003...