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(at)logi-track(dot)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(at)logi-track(dot)com | www.logi-track.com

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(at)agliodbs(dot)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(at)agliodbs(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)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?"