Re: query io stats and finding a slow query

Thanks for the reply.

Here is what I found about my problem. When i set the
log_min_duration_statement and in the moments when the server performance is
degrading I can see that almost all queries run very slowly (10-100 times
slower). At first I thought that there is exclusive lock on one of the
tables but there wasn't any.

The information from the log files becomes useless when almost every query
on you server is logged and when you can't tell which query after which. So
I finally wrote a script to process the log file and graphically represent
the timing of each query from the log (something like a gantt chart), and
that way I found out what was the reason for the slowdowns. There was a
query which actually reads all the data from one of the big tables and while
it is running and some time after it finished the server is slowing down to
death. I couldn't find it just looking at the log because it was not even
the slowest query. After I examined the chart it was very clear what was
happening. As I understand it, while this table was scanned all the disk i/o
operations were slowed down, and maybe the data from that table was stored
in the os cache, and hence all the other queries were so slow? After I
removed the big query everything runs normally.

However, I was wondering if there are any tools for such log analysis. I'm
ready to provide my script if somebody is interested? I think it is very
useful, but maybe someone has already done something better?

Regards,
Kamen

On 9/21/07, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
> >>> On Thu, Sep 20, 2007 at 4:36 PM, in message
> <de5063670709201436y5cbff0d0k70ade289a4c68199(at)mail(dot)gmail(dot)com>, "Kamen
> Stanev"
> <hambai(at)gmail(dot)com> wrote:
> >
> > Is there a way to find which query is doing large io operations and/or
> which
> > is using cached data and which is reading from disk.
>
> A big part of your cache is normally in the OS, which makes that tough.
>
> > please share your experience on how do you decide which
> > queries to optimize and how to reorganize your database?
>
> We base this on two things -- query metrics from our application framework
> and user complaints about performance.
>
> > Is there any tools that you use to profile your database.
>
> Many people set log_min_duration_statement to get a look at long-running
> queries.
>
> When you identify a problem query, running it with EXPLAIN ANALYZE in
> front
> will show you the plan with estimated versus actual counts, costs, and
> time.
> This does actually execute the query (unlike EXPLAIN without ANALYZE).
>
> -Kevin
>
>
>
>