Thread: Automagic tuning
Hello, Are there any tools that help with postgres/postgis performance tuning? So they measure the acutal tuple costs and cpu power, or suggest optimal values for the index sample counts? I could imagine that some profiling on a typical workload (or realistic simulation thereof) could be automatically converted into hints how to tweak the config file. Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
> Are there any tools that help with postgres/postgis performance tuning? > > So they measure the acutal tuple costs and cpu power, or suggest optimal > values for the index sample counts? Have you turned on the stat_* settings in postgresql.conf and then examined the pg_stat_* system views? Chris
Hi, Cristopher, Christopher Kings-Lynne schrieb: >> Are there any tools that help with postgres/postgis performance tuning? >> >> So they measure the acutal tuple costs and cpu power, or suggest optimal >> values for the index sample counts? > > Have you turned on the stat_* settings in postgresql.conf and then > examined the pg_stat_* system views? As far as I examined, those views only count several things like fetched rows and pages, and cache hits. I would like something that really measures values like random_page_cost or cpu_tuple_cost that are hardware dependent. I assume such thing does not exist? Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
Attachment
Markus, > As far as I examined, those views only count several things like fetched > rows and pages, and cache hits. > > I would like something that really measures values like random_page_cost > or cpu_tuple_cost that are hardware dependent. > > I assume such thing does not exist? Nope. You gotta whip out your calculator and run some queries. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> I would like something that really measures values like random_page_cost
>> or cpu_tuple_cost that are hardware dependent.
>>
>> I assume such thing does not exist?
> Nope. You gotta whip out your calculator and run some queries.
Preferably a whole lot of queries. All the measurement techniques I can
think of are going to have a great deal of noise, so you shouldn't
twiddle these cost settings based on just a few examples.
regards, tom lane
On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > >> I would like something that really measures values like random_page_cost > >> or cpu_tuple_cost that are hardware dependent. > >> > >> I assume such thing does not exist? > > > Nope. You gotta whip out your calculator and run some queries. > > Preferably a whole lot of queries. All the measurement techniques I can > think of are going to have a great deal of noise, so you shouldn't > twiddle these cost settings based on just a few examples. Are there any examples of how you can take numbers from pg_stats_* or explain analize and turn them into configuration settings (such and random page cost)? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes:
> On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote:
>> Preferably a whole lot of queries. All the measurement techniques I can
>> think of are going to have a great deal of noise, so you shouldn't
>> twiddle these cost settings based on just a few examples.
> Are there any examples of how you can take numbers from pg_stats_* or
> explain analize and turn them into configuration settings (such and
> random page cost)?
Well, the basic idea is to adjust random_page_cost so that the ratio of
estimated cost to real elapsed time (as shown by EXPLAIN ANALYZE) is the
same for seqscans and indexscans. What you have to watch out for is
that the estimated cost model is oversimplified and doesn't take into
account a lot of real-world factors, such as the activity of other
concurrent processes. The reason for needing a whole lot of tests is
essentially to try to average out the effects of those unmodeled
factors, so that you have a number that makes sense within the planner's
limited view of reality.
regards, tom lane
On Tue, Feb 01, 2005 at 12:06:27AM -0500, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote: > >> Preferably a whole lot of queries. All the measurement techniques I can > >> think of are going to have a great deal of noise, so you shouldn't > >> twiddle these cost settings based on just a few examples. > > > Are there any examples of how you can take numbers from pg_stats_* or > > explain analize and turn them into configuration settings (such and > > random page cost)? > > Well, the basic idea is to adjust random_page_cost so that the ratio of > estimated cost to real elapsed time (as shown by EXPLAIN ANALYZE) is the > same for seqscans and indexscans. What you have to watch out for is > that the estimated cost model is oversimplified and doesn't take into > account a lot of real-world factors, such as the activity of other > concurrent processes. The reason for needing a whole lot of tests is > essentially to try to average out the effects of those unmodeled > factors, so that you have a number that makes sense within the planner's > limited view of reality. Given that, I guess the next logical question is: what would it take to collect stats on queries so that such an estimate could be made? And would it be possible/make sense to gather stats useful for tuning the other parameters? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"