Thread: 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.