Thread: PostgreSQL calibration

PostgreSQL calibration

From
Chris Gamache
Date:
What's your thought on creating some type of calibration applet that will
divine proper settings for the PostgreSQL performance related variables by
taking into consideration the speed of the machine, amount of RAM, access time
of the filesystem, and the speed of various queries on a standardized (perhaps
non-standardized?) dataset under different conditions?

Is this possible, do-able, or even wanted? Where would one begin constructing
the tests?

I imagine it would automate the tasks that every PostgreSQL administrator goes
through to calibrate his or her installation with the best possible performance
settings...

CG

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

Re: PostgreSQL calibration

From
Bruce Momjian
Date:
Sure, it would be great if we could do it.

---------------------------------------------------------------------------

Chris Gamache wrote:
> What's your thought on creating some type of calibration applet that will
> divine proper settings for the PostgreSQL performance related variables by
> taking into consideration the speed of the machine, amount of RAM, access time
> of the filesystem, and the speed of various queries on a standardized (perhaps
> non-standardized?) dataset under different conditions?
>
> Is this possible, do-able, or even wanted? Where would one begin constructing
> the tests?
>
> I imagine it would automate the tasks that every PostgreSQL administrator goes
> through to calibrate his or her installation with the best possible performance
> settings...
>
> CG
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: PostgreSQL calibration

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Sure, it would be great if we could do it.

If the program actually derives reliable numbers, it would be great.
It could easily do more harm than good if it gives bogus results.
I think it will be very hard to get reliable rather than bogus results
:-( ... but feel free to try.

            regards, tom lane

Re: PostgreSQL calibration

From
matt
Date:
Are there really any performance settings of much interest beyond the
shared and non-shared memory settings?  Beyond those the interactions
get so complex that automation is probably impossible anyway, and
certain options like fsync = false should never be 'recommended'.

On the other hand, a way of empirically deriving some 'correct'
optimizer parameters for a given machine would be very nice :-)


Matt


On Wed, 2003-06-18 at 18:07, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Sure, it would be great if we could do it.
>
> If the program actually derives reliable numbers, it would be great.
> It could easily do more harm than good if it gives bogus results.
> I think it will be very hard to get reliable rather than bogus results
> :-( ... but feel free to try.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: PostgreSQL calibration

From
"scott.marlowe"
Date:
It would be nice to have something that could suggest settings for the
different *cost* options.

On 18 Jun 2003, matt wrote:

> Are there really any performance settings of much interest beyond the
> shared and non-shared memory settings?  Beyond those the interactions
> get so complex that automation is probably impossible anyway, and
> certain options like fsync = false should never be 'recommended'.
>
> On the other hand, a way of empirically deriving some 'correct'
> optimizer parameters for a given machine would be very nice :-)
>
>
> Matt
>
>
> On Wed, 2003-06-18 at 18:07, Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Sure, it would be great if we could do it.
> >
> > If the program actually derives reliable numbers, it would be great.
> > It could easily do more harm than good if it gives bogus results.
> > I think it will be very hard to get reliable rather than bogus results
> > :-( ... but feel free to try.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


Re: PostgreSQL calibration

From
matt
Date:
Precisely.  Any hints from the real gurus out there as to how that might
be accomplished (or alternatively, reasons why it's hopeless)?

On Wed, 2003-06-18 at 18:42, scott.marlowe wrote:
> It would be nice to have something that could suggest settings for the
> different *cost* options.
>
> On 18 Jun 2003, matt wrote:
>
> > Are there really any performance settings of much interest beyond the
> > shared and non-shared memory settings?  Beyond those the interactions
> > get so complex that automation is probably impossible anyway, and
> > certain options like fsync = false should never be 'recommended'.
> >
> > On the other hand, a way of empirically deriving some 'correct'
> > optimizer parameters for a given machine would be very nice :-)
> >
> >
> > Matt
> >
> >
> > On Wed, 2003-06-18 at 18:07, Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Sure, it would be great if we could do it.
> > >
> > > If the program actually derives reliable numbers, it would be great.
> > > It could easily do more harm than good if it gives bogus results.
> > > I think it will be very hard to get reliable rather than bogus results
> > > :-( ... but feel free to try.
> > >
> > >             regards, tom lane
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > >                http://archives.postgresql.org
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> >       joining column's datatypes do not match
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


Re: PostgreSQL calibration

From
Tim Middleton
Date:
On June 18, 2003 12:54 pm, Chris Gamache wrote:
> What's your thought on creating some type of calibration applet that
> will divine proper settings for the PostgreSQL performance related

Have you seen pg_autotune? It's aging... but might be something to play
with.

http://gborg.postgresql.org/project/pgautotune/projdisplay.php

--
Tim Middleton | Cain Gang Ltd | But the trouble was that my hysterical
fit
x@veX.net     | www.Vex.Net   | could not go on for ever. --Dost (NFTU)


Re: PostgreSQL calibration

From
Dani Oderbolz
Date:
Chris Gamache wrote:

>What's your thought on creating some type of calibration applet that will
>divine proper settings for the PostgreSQL performance related variables by
>taking into consideration the speed of the machine, amount of RAM, access time
>of the filesystem, and the speed of various queries on a standardized (perhaps
>non-standardized?) dataset under different conditions?
>
Other RDBMS offer such options.
I know "Oracle Expert", its resultls are not very striking.
But surely, there is a demand for such tools.
Oracle expert considers the following data (among others)
- RAM
- Number of CPUs
- I/O bandwidth
- Current Statments in cache
- You can fed it with Statements your Application generates (can also
be captured)

But I think it is hard to get precise results,
as the program needs a lot of intelligence...

Cheers, Dani