Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 24A399FB209 for ; Wed, 22 Nov 2006 20:02:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 75000-01 for ; Wed, 22 Nov 2006 20:02:12 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 3B64A9FB207 for ; Wed, 22 Nov 2006 20:02:15 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so716629nfb for ; Wed, 22 Nov 2006 16:02:14 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nTKCR9ypEa0IkLXITcHnFhZN4uqeDMzvtH1j1aqmzGVIfCnror0KQtPuJgusdY0vxub+qBYX2Nf2fMgV9RUn1EaWDYxrLR1Pw5Kcdh1LgzUqq+Dz+/bYj3GmRrD8zpyJRLdlPYCCpp9pRZl30AunxKw0zQv2QtO3nzC91QTG/zU= Received: by 10.82.126.5 with SMTP id y5mr1336904buc.1164240133961; Wed, 22 Nov 2006 16:02:13 -0800 (PST) Received: by 10.82.173.15 with HTTP; Wed, 22 Nov 2006 16:02:13 -0800 (PST) Message-ID: <1c66bda80611221602w4a2766a1l3dff7b05eedac574@mail.gmail.com> Date: Thu, 23 Nov 2006 13:02:13 +1300 From: adey To: "Aaron Bono" Subject: Re: Monitoring PostgreSQL Process Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_38511_30598640.1164240133871" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/200 X-Sequence-Number: 23727 ------=_Part_38511_30598640.1164240133871 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. > > Thanks, > Aaron > > -- > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > http://codeelixir.com > ================================================================== ------=_Part_38511_30598640.1164240133871 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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.

Thanks,
Aaron

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

------=_Part_38511_30598640.1164240133871--