Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 299D09FB215 for ; Wed, 22 Nov 2006 22:33:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 72028-05 for ; Thu, 23 Nov 2006 02:33:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 4A5019FB20D for ; Wed, 22 Nov 2006 22:33:30 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so300465ugf for ; Wed, 22 Nov 2006 18:33:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=XyOCngiSSw26Xx/u73N75Lj9vDei82QKw2r/A6dNPMmYgnM5QEtOgZTGIzuPUqWLYzR+bSV9btFsgwwkuFXYQ8wQzW6zNF5ogAepeglwLYluQobD6DrF/vXpVidpdF8GqcFKrpUUSLJgQU/fQX0ou5AJwvPnC+81IDfwTZIGqC4= Received: by 10.78.127.2 with SMTP id z2mr8574014huc.1164249209009; Wed, 22 Nov 2006 18:33:29 -0800 (PST) Received: by 10.78.136.6 with HTTP; Wed, 22 Nov 2006 18:33:28 -0800 (PST) Message-ID: Date: Wed, 22 Nov 2006 20:33:28 -0600 From: "Aaron Bono" To: adey Subject: Re: Monitoring PostgreSQL Process Cc: pgsql-admin@postgresql.org In-Reply-To: <1c66bda80611221602w4a2766a1l3dff7b05eedac574@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_66256_19368765.1164249208732" References: <1c66bda80611221602w4a2766a1l3dff7b05eedac574@mail.gmail.com> X-Google-Sender-Auth: a4538534bc931ec6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/204 X-Sequence-Number: 23731 ------=_Part_66256_19368765.1164249208732 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I queried pg_stat_activity a bunch of times and was able to tell what tables were being queried. I finally honed in on the section of code causing the problem and fix it and all is good on the server now - over 90% idle. I think the reason some of the processes were showing high CPU usage was that they are not reporting real time usage but some kind of average. My guess is that some of the processes that showed high usage were not the sources of the CPU problems I was currently having. Thanks all. On 11/22/06, adey wrote: > > The following query with the stats_command_string parameter turned on will > give you some of the SQL per transaction:- > > > SELECT > datid, > datname as "DB Name", > substr(procpid,1,6) as "Procpid", > substr(usesysid,1,5) as "UseSysid", > usename, > current_query as "SQL", > query_start > FROM > pg_stat_activity > order by > procpid; > > > On 11/22/06, Aaron Bono wrote: > > > > I have a couple processes/connections to one of our databases that > > appears to be eating up most of the CPU and we are trying to determine what > > these processes are doing that is taking so much CPU time. > > > > Is there a way to monitor the SQL being run for a specific > > connection/process? > > > > We are using PostgreSQL 8.1.5 on CentOS Linux. > > > > -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_66256_19368765.1164249208732 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I queried pg_stat_activity a bunch of times and was able to tell what tables were being queried.  I finally honed in on the section of code causing the problem and fix it and all is good on the server now - over 90% idle.

I think the reason some of the processes were showing high CPU usage was that they are not reporting real time usage but some kind of average.  My guess is that some of the processes that showed high usage were not the sources of the CPU problems I was currently having.

Thanks all.

On 11/22/06, adey <adey11@gmail.com> wrote:
The following query with the stats_command_string parameter turned on will give you some of the SQL per transaction:-
 

SELECT
 datid,
 datname as "DB Name",
 substr(procpid,1,6) as "Procpid",
 substr(usesysid,1,5) as "UseSysid",
 usename,
 current_query as "SQL",
 query_start
FROM
 pg_stat_activity
order by
 procpid;

 
On 11/22/06, Aaron Bono <postgresql@aranya.com > wrote:
I have a couple processes/connections to one of our databases that appears to be eating up most of the CPU and we are trying to determine what these processes are doing that is taking so much CPU time.

Is there a way to monitor the SQL being run for a specific connection/process?

We are using PostgreSQL 8.1.5 on CentOS Linux.



--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_66256_19368765.1164249208732--