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.Grittner@xxxxxxxxxxxx> wrote:
>>> On Thu, Sep 20, 2007 at 4:36 PM, in message
<de5063670709201436y5cbff0d0k70ade289a4c68199@xxxxxxxxxxxxxx>, "Kamen Stanev"
<hambai@xxxxxxxxx> 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
