Thread: PostgreSQL calibration
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
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
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
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 >
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 >
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 >
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)
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