How to know which queries are to be optimised?

Hello,

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.

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?

TIA

Ulrich

On Wed, Aug 04, 2004 at 14:00:39 +0200,
Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)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.

Hello,

My linux admin left the job. We had a PostgreSQL
installed under his username. He used to maintain it.
Now I am looking at the Linux box and I am just a
super duper newbie in Linux administration.

The previosu admin had a database created under his
name coz PostgreSQL dosnt allow root database.

Now I want to get the data back and use it? I dont
mind if I have to use a different DB?

I dont even know where he isntalled the PostgreSQL
binaries and data?

Where can I all this information?

I am feeling really stupid but thank GOD we dont have
any live databases running?

Regards,
Karam

__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

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

All of the resources you need are at http://www.posgresql.org/

But, a quick note on how to connect to the database, you don't need to
really know where it's installed, just that it's running and accepting
connections. Under Linux "netstat -tapn" will show you all of the open
TCP ports and what's listening to each. For Postgres, the default port
is 5432 and the process is called postmaster. You may need to be logged
in as root to see this.

First thing is that knowing SQL basics is pretty much required before
you can really investigate what he had set up.
Second thing is to connect by using the following command: psql
Without any arguments, it will connect to the local machine and to a
database named for the current user. Once in psql, type \d to see a
list of the user-defined tables. To see a list of databases type \l and
you will be shown a list of databases.

From there, you can explore until your heart's content.

Everything is SQL compliant, so if you know SQL, you shouldn't have any
problems.

As for Linux admin, the most important thing to remember is that
everything is case sensitive. ls != LS

If you're coming from the MS world, take some time to really learn
Linux... I'm sure you'll like it and eventually you'll prefer it. I use
Linux as my only OS at home, and I finally have a job where I use Linux
at work (they adopted it after I submitted a proposal).

hope this helps!
Laura

Karam Chand wrote:

>Hello,
>
>My linux admin left the job. We had a PostgreSQL
>installed under his username. He used to maintain it.
>Now I am looking at the Linux box and I am just a
>super duper newbie in Linux administration.
>
>The previosu admin had a database created under his
>name coz PostgreSQL dosnt allow root database.
>
>Now I want to get the data back and use it? I dont
>mind if I have to use a different DB?
>
>I dont even know where he isntalled the PostgreSQL
>binaries and data?
>
>Where can I all this information?
>
>I am feeling really stupid but thank GOD we dont have
>any live databases running?
>
>Regards,
>Karam
>
>
--
Thanks,
Laura Vance
Systems Engineer
Winfree Academy Charter Schools
6221 Riverside Dr. Ste 110
Irving, Tx 75039
Web: www.winfreeacademy.com

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.