Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 137669F9B9A for ; Tue, 25 Sep 2007 16:59:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 65373-09 for ; Tue, 25 Sep 2007 16:58:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.225]) by postgresql.org (Postfix) with ESMTP id 410E09F9B4F for ; Tue, 25 Sep 2007 16:58:55 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so1139333nzf for ; Tue, 25 Sep 2007 12:58:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=WOcdTcHMJWWOcg0IxYvmIYPibFtY13hPdWQreb1C0ms=; b=Q0EUAfXr6IQGm+YOl0Umtos+OiKRboc6Hx1oeN+qURZ0B8czyGyde7yefZQ59iDJ36SRCATwM0XS7XiUKzE16n9M8FNS1rHtkEoCPwaTzA/LlJbl8sqdw/TRkwyMXvdNQE91FW4Oi5VNiXXtYPdGScD7gBd0almsIqxltNpYpJs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nZamg7IrsW+z3auaM7/ZR9ANoMKqLlPWz7YG3mpCnsH6n0WnXdNmz8p5rMWxwS6yVdHq2gzpz+8rPhR4cJROhy81y+ewJ68/d+9P7vxFpjirrObmV7xdw4MM1dUrwuLQj6KswQ1C4sJe6hwKl6kuAe98hrQEfRtIIcOz0FZHABg= Received: by 10.115.47.1 with SMTP id z1mr7647767waj.1190750333148; Tue, 25 Sep 2007 12:58:53 -0700 (PDT) Received: by 10.114.75.20 with HTTP; Tue, 25 Sep 2007 12:58:53 -0700 (PDT) Message-ID: Date: Tue, 25 Sep 2007 14:58:53 -0500 From: "Bryan Murphy" To: "Kamen Stanev" Subject: Re: query io stats and finding a slow query Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <46F3AC73.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/441 X-Sequence-Number: 26986 We use pgfouine (http://pgfouine.projects.postgresql.org/). I currently have postgres log every query that takes longer than 100ms, roll the log files every 24 hours, and run pgfouine nightly. I check it every couple of mornings and this gives me a pretty good picture of who misbehaved over the last 24 hours. I can't even count the # of times I've come in in the morning and some new query has bubbled to the top. It's very handy. I don't know if it would have helped you identify your problem, but it's saved our butts a few times. Bryan On 9/25/07, Kamen Stanev wrote: > 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 wrote: > > >>> On Thu, Sep 20, 2007 at 4:36 PM, in message > > > , > "Kamen Stanev" > > 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 > > > > > > > > > >