Re: "--tuning" compile and runtime option (?) - Mailing list pgsql-hackers

Hi Bruce,

My thought on this is more for an "overall effect".

Down The Track (i.e. in a few versions or so) I'm thinking, rightly or
wrongly, that PostgreSQL will become Very Good at tuning itself.

It would be a good thing if PostgreSQL could know just how fair it can
play in regards to the server it's working on.

For example, if lets say it's installed on a server in which it's the
only important thing.  i.e. OS + PostgreSQL and thats about it. 
Indicating to the PostgreSQL server that's it's allowed to consume all
the available resources to its maximum benefit would allow possible
future "self-tuning" algorithms to say "well, in these circumstances the
best way to deal with the present load is X".  And it would do things
without regard for other possible services, as it would know that it's
running by itself.  This would be something like a
"--tuning=superserver" compile-time option or run-time flag.

Conversely, the PostgreSQL server may be on a box with several other
services, like Apache, MySQL, FTP daemons, and so forth.  In that case
it would possibly select different algorithms, knowing that it had to
"play fair" with the server's resources.  This may be indicated to it by
a "--tuning=shared" compile-time option or run-time flag.

And similar for embedded systems, where there is a lower or different
resource allocation strategy.

This is a general indication of thoughts I was having last night and
this morning, and I bring it up more as a point of interest and
wondering if others see that it may be of benefit.

Presently we have to benchmark and then hand-tune the servers ourselves,
and thats good.  I'm thinking more about PostgreSQL's internal ways of
dealing with queries and handling of resources though, in a
second-by-second situation.

What do you think?

Regards and best wishes,

Justin Clift

Bruce Momjian wrote:
> 
> My idea was to have PostgreSQL output tips to help performance.  The
> TODO item is:
> 
>         * Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM
>           ANALYZE, and CLUSTER
> 
> I also will be writing an article on performance tuning this month.
> What parameters would these options you suggest control?  I usually
> prefer options that have more concrete effect.
> 
> > Just thinking about the future directions PostgreSQL is taking, and it
> > seems (just a feeling) like most people prefer it to be as self tuning
> > as possible.
> >
> > In trying to think about how it will/would do that I think PostgreSQL
> > will need to know "how much" of the resources of the server its on, it's
> > allowed to take.
> >
> > Can think of three scenario's, 1) Single-purpose PostgreSQL server 2)
> > shared function server (i.e. Apache, Postgres, etc on the same box) 3)
> > Embedded or otherwise resource limited server (Palmtop, etc).
> >
> > When we get around to PostgreSQL's self-tuning ability being actively
> > developed (and I think Bruce has done some of the very start with his
> > monitor program), perhaps having a compile time option to set the
> > default for the server, and a runtime option in case it changes?
> >
> > i.e.
> >
> > --tuning=superserver
> > --tuning=shared
> > --tuning=embedded
> >
> > postmaster -t superserver
> > postmaster -t shared
> > postmaster -t embedded
> >
> > What do people think?
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> > P.S. - I'm not on the Hackers mailing list from this account.  Can
> > anyone responding please include me directly in their replies?
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> >      - Indira Gandhi
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."    - Indira Gandhi