Обсуждение: Re: [GENERAL] How to know which queries are to be optimised?

On Wed, Aug 04, 2004 at 14:00:39 +0200,
  Ulrich Wisser <ulrich.wisser@relevanttraffic.se> wrote:

This topic really belongs on the performance list. I have copied that
list and set followups to go there and copy you.

>
> my web application grows slower and slower over time. After some
> profiling I came to the conclusion that my SQL queries are the biggest
> time spenders (25 seconds). Obviously I need to optimise my queries and
> maybe introduce some new indexes.

This sounds like you aren't doing proper maintainance. You need to be
vacuuming with a large enough FSM setting.

> The problem is, that my application uses dynamic queries. I therefor can
> not determine what are the most common queries.
>
> I have used the postgresql logging ption before. Is there a tool to
> analyze the logfile for the most common and/or most time consuming queries?

You can log queries that run for at least a specified amount of time.
This will be useful in finding what the long running queries are.
You can then use explain analyse to see why they are long running.

Hi Bruno,

>>my web application grows slower and slower over time. After some
>>profiling I came to the conclusion that my SQL queries are the biggest
>>time spenders (25 seconds). Obviously I need to optimise my queries and
>>maybe introduce some new indexes.
>
> This sounds like you aren't doing proper maintainance. You need to be
> vacuuming with a large enough FSM setting.

I do a vacuum full analyze every night.
How can I see if my FSM setting is appropriate?

>>The problem is, that my application uses dynamic queries. I therefor can
>>not determine what are the most common queries.
>>
>>I have used the postgresql logging ption before. Is there a tool to
>>analyze the logfile for the most common and/or most time consuming queries?
>
>
> You can log queries that run for at least a specified amount of time.
> This will be useful in finding what the long running queries are.
> You can then use explain analyse to see why they are long running.

But is there a tool that could compile a summary out of the log? The log
grows awefully big after a short time.

Thanks

/Ulrich

Ulrich Wisser wrote:
>> You can log queries that run for at least a specified amount of time.
>> This will be useful in finding what the long running queries are.
>> You can then use explain analyse to see why they are long running.
>
> But is there a tool that could compile a summary out of the log? The log
> grows awefully big after a short time.

You might want to look at the "Practical Query Analyser" - haven't used
it myself yet, but it seems a sensible idea.

http://pqa.projects.postgresql.org/

--
   Richard Huxton
   Archonet Ltd

> I do a vacuum full analyze every night.
> How can I see if my FSM setting is appropriate?

On a busy website, run vacuum analyze once an hour, or even better, use
contrib/pg_autovacuum

Chris

> But is there a tool that could compile a summary out of the log? The log
> grows awefully big after a short time.

Actually, yes there is.  Check out www.pgfoundry.org.  I think it's called
pqa or postgres query analyzer or somethign.

Chris

Hi,

>> But is there a tool that could compile a summary out of the log? The
>> log grows awefully big after a short time.

There's also pg_analyzer to check out.

http://www.samse.fr/GPL/pg_analyzer/

Some of it's features are: written in Perl and produces HTML output.

> You might want to look at the "Practical Query Analyser" - haven't used
> it myself yet, but it seems a sensible idea.
>
> http://pqa.projects.postgresql.org/


Cheers,
Rudi.