From postgresql@aranya.com Wed Nov 22 13:32:52 2006 Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 44C249FA463 for ; Wed, 22 Nov 2006 09:32:52 -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 94543-04 for ; Wed, 22 Nov 2006 09:32:37 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 5875D9FA265 for ; Wed, 22 Nov 2006 09:32:46 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so135608ugf for ; Wed, 22 Nov 2006 05:32:44 -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:mime-version:content-type:x-google-sender-auth; b=FhRdUJYVzkSonN2Fn3y2TFn6zWwuLGZjUnWvAknIuriORDJq+9Jp25fsVqM2YvUEs2WrkdMzr0r5ifjHRkfpnDaQKKXIAl6Vbe9jRFE5UYlAh/B3zLZK4H+NwgjyyiuPdFyPAmsj29TFp+MMSmQu4LF0qdTYwZ1Q6XuQhnR212Q= Received: by 10.78.75.15 with SMTP id x15mr7343795hua.1164174588512; Tue, 21 Nov 2006 21:49:48 -0800 (PST) Received: by 10.78.136.6 with HTTP; Tue, 21 Nov 2006 21:49:48 -0800 (PST) Message-ID: Date: Tue, 21 Nov 2006 23:49:48 -0600 From: "Aaron Bono" To: pgsql-admin@postgresql.org Subject: Monitoring PostgreSQL Process MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_54075_20580278.1164174588428" X-Google-Sender-Auth: 73f785517844429e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/193 X-Sequence-Number: 23720 ------=_Part_54075_20580278.1164174588428 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_54075_20580278.1164174588428 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_54075_20580278.1164174588428-- From htomeh@firstam.com Wed Nov 22 18:02:31 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 3AAD09FA22C for ; Wed, 22 Nov 2006 14:02:31 -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 33184-08 for ; Wed, 22 Nov 2006 18:02:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outbound-smtp.firstam.com (outbound-smtp7.firstam.com [69.87.54.12]) by postgresql.org (Postfix) with ESMTP id 740B89F9F27 for ; Wed, 22 Nov 2006 14:02:19 -0400 (AST) Received: from 10.48.129.31 by outbound-smtp.firstam.com with ESMTP ( Hello SMTP Relay); Wed, 22 Nov 2006 10:02:09 -0800 X-Server-Uuid: 784562B0-C2BD-4C7D-8289-6A1E69B096B7 Received: from unknown (HELO PISGSNA01SMMX02.ana.firstamdata.com) ( [172.17.88.44]) by FAEMSNA01SMXS01.firstam.com with ESMTP; 22 Nov 2006 10:02:10 -0800 Received: from pisgsna01sxch21.ana.firstamdata.com (Not Verified[172.17.88.71]) by PISGSNA01SMMX02.ana.firstamdata.com with MailMarshal (v6,1,6,1172) id ; Wed, 22 Nov 2006 10:02:09 -0800 Received: from CITGSNA01SXCH02.ana.firstamdata.com ([172.17.88.30]) by pisgsna01sxch21.ana.firstamdata.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 22 Nov 2006 10:02:09 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: Re: Monitoring PostgreSQL Process Date: Wed, 22 Nov 2006 10:02:08 -0800 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Monitoring PostgreSQL Process Thread-Index: AccOOtB1/TekIC5gRX+Px8EwHrbrzgAJC+nw From: "Tomeh, Husam" To: "Aaron Bono" , pgsql-admin@postgresql.org X-OriginalArrivalTime: 22 Nov 2006 18:02:09.0187 (UTC) FILETIME=[53F9AF30:01C70E60] X-TMWD-Spam-Summary: SEV=1.1; DFV=A2006112208; IFV=2.0.6,4.0-7; RPD=4.00.0004; RPDID=303030312E30413031303230342E34353634384644372E303043303A5343464D413534333432342D462D2F4E4553574B563534472F71554B6D71577A564237673D3D; ENG=IBF; TS=20061122180209; CAT=NONE; CON=NONE; X-WSS-ID: 697A4F2B3NG195558-01-01 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C70E60.53A71BF4" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/194 X-Sequence-Number: 23721 This is a multi-part message in MIME format. ------_=_NextPart_001_01C70E60.53A71BF4 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html =20 Once you capture your SQL statement, you can run "explain" on it to check out its execution plan. =20 -- =20 Husam =20 http://firstdba.googlepages.com =20 ________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Aaron Bono Sent: Tuesday, November 21, 2006 9:50 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Monitoring PostgreSQL Process 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?=20 We are using PostgreSQL 8.1.5 on CentOS Linux. Thanks, Aaron --=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =20 Aaron Bono =20 Aranya Software Technologies, Inc.=20 =20 http://www.aranya.com =20 http://codeelixir.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 ********************************************************************** This message contains confidential information intended only for the use = of the addressee(s) named above and may contain information that is legal= ly privileged. If you are not the addressee, or the person responsible f= or delivering it to the addressee, you are hereby notified that reading, = disseminating, distributing or copying this message is strictly prohibite= d. If you have received this message by mistake, please immediately noti= fy us by replying to the message and delete the original message immediat= ely thereafter. Thank you. =0D =20 FADLD Tag ********************************************************************** ------_=_NextPart_001_01C70E60.53A71BF4 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: quoted-printable
You may=20 query the system view, pg_stat_activity to check out the current SQL stat= ements=20 running. To track down executed SQL statements into a physical log file, = you may=20 want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.ht= ml
 
Once you=20 capture your SQL statement, you can run "explain" on it to check out its = execution plan.
 
--
 =20 Husam
  http://firstdba.googlepages.com<= /A>
http://www.aranya.com
 &nbs= p; http://codeelixir.com
=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20

**********************************************************************=
This=20 message contains confidential information intended only for the use of th= e=20 addressee(s) named above and may contain information that is legally=20 privileged.  If you are not the addressee, or the person responsible= =20for=20 delivering it to the addressee, you are hereby notified that reading,=20 disseminating, distributing or copying this message is strictly=20 prohibited.  If you have received this message by mistake, please=20 immediately notify us by replying to the message and delete the original = message=20 immediately thereafter.

Thank you.

          &nbs= p;            = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =              = FADLD=20 Tag
***********************************************************= ***********

------_=_NextPart_001_01C70E60.53A71BF4-- From postgresql@aranya.com Wed Nov 22 18:46:30 2006 Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 9D7BA9FB1DA for ; Wed, 22 Nov 2006 14:46:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 88681-03 for ; Wed, 22 Nov 2006 14:46:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 09FF19FB1CE for ; Wed, 22 Nov 2006 14:46:26 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so221201ugf for ; Wed, 22 Nov 2006 10:46:23 -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=ATwKJ1Vbed+dSp3wnjNHWv5vuD7Lyk0TRUd/aT1nNpXQjNQbd4vN+OdkNHHYYysjkSaJWJh5EiSSoRqUXphUdBwqNpfWv2UozfWaFCjikMNtld6ufY14k8+SSZtsd/6j4gYrzNyCSLxQ6PQvKAWtebAjtFrWQ7ClbaC7Sosp+Wc= Received: by 10.78.200.3 with SMTP id x3mr8107455huf.1164221182972; Wed, 22 Nov 2006 10:46:22 -0800 (PST) Received: by 10.78.136.6 with HTTP; Wed, 22 Nov 2006 10:46:22 -0800 (PST) Message-ID: Date: Wed, 22 Nov 2006 12:46:22 -0600 From: "Aaron Bono" To: "Tomeh, Husam" Subject: Re: Monitoring PostgreSQL Process Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_62554_2857132.1164221182790" References: X-Google-Sender-Auth: de9de9b188eb9ccd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/195 X-Sequence-Number: 23722 ------=_Part_62554_2857132.1164221182790 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam wrote: > > You may query the system view, pg_stat_activity to check out the current > SQL statements running. To track down executed SQL statements into a > physical log file, you may want to enable statement logging. For more > details on that, check out: > http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html > > Once you capture your SQL statement, you can run "explain" on it to check > out its execution plan. > I think capturing the currently running SQL will be enough for now, thanks. It should show me what queries are running the longest and most frequently. -- > Husam > http://firstdba.googlepages.com > > ------------------------------ > *From:* pgsql-admin-owner@postgresql.org [mailto: > pgsql-admin-owner@postgresql.org] *On Behalf Of *Aaron Bono > *Sent:* Tuesday, November 21, 2006 9:50 PM > *To:* pgsql-admin@postgresql.org > *Subject:* [ADMIN] Monitoring PostgreSQL Process > > 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 > ================================================================== > > ********************************************************************** > This message contains confidential information intended only for the use > of the addressee(s) named above and may contain information that is legally > privileged. If you are not the addressee, or the person responsible for > delivering it to the addressee, you are hereby notified that reading, > disseminating, distributing or copying this message is strictly prohibited. > If you have received this message by mistake, please immediately notify us > by replying to the message and delete the original message immediately > thereafter. > > Thank you. > > > FADLD Tag > ********************************************************************** > -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_62554_2857132.1164221182790 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html
 
Once you capture your SQL statement, you can run "explain" on it to check out its execution plan.

I think capturing the currently running SQL will be enough for now, thanks.  It should show me what queries are running the longest and most frequently.

--
  Husam
 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Aaron Bono
Sent: Tuesday, November 21, 2006 9:50 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Monitoring PostgreSQL Process

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
==================================================================

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged.  If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited.  If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

                                                                                                                         FADLD Tag
**********************************************************************




--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_62554_2857132.1164221182790-- From htomeh@firstam.com Wed Nov 22 23:13:57 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 16B389FB1DA for ; Wed, 22 Nov 2006 19:13:57 -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 06950-02 for ; Wed, 22 Nov 2006 23:13:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outbound-smtp.firstam.com (outbound-smtp6.firstam.com [69.87.54.11]) by postgresql.org (Postfix) with ESMTP id 3318D9FB1CE for ; Wed, 22 Nov 2006 19:13:50 -0400 (AST) Received: from 10.48.129.31 by outbound-smtp.firstam.com with ESMTP ( Hello SMTP Relay); Wed, 22 Nov 2006 13:27:48 -0800 X-Server-Uuid: A2519771-17FD-438B-B41E-237CBEBED851 Received: from unknown (HELO PISGSNA01SMMX02.ana.firstamdata.com) ( [172.17.88.44]) by FAEMSNA01SMXS02.FIRSTAM.COM with ESMTP; 22 Nov 2006 13:27:48 -0800 Received: from PISGSNA01SXCH22.ana.firstamdata.com (Not Verified[172.17.88.79]) by PISGSNA01SMMX02.ana.firstamdata.com with MailMarshal (v6,1,6,1172) id ; Wed, 22 Nov 2006 13:27:48 -0800 Received: from CITGSNA01SXCH02.ana.firstamdata.com ([172.17.88.30]) by PISGSNA01SXCH22.ana.firstamdata.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 22 Nov 2006 13:27:47 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: Re: Monitoring PostgreSQL Process Date: Wed, 22 Nov 2006 13:27:47 -0800 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Monitoring PostgreSQL Process Thread-Index: AccOex+uOhjmUmvoRauNKczybh7tLQAAUOjQ From: "Tomeh, Husam" To: "Aaron Bono" cc: pgsql-admin@postgresql.org X-OriginalArrivalTime: 22 Nov 2006 21:27:47.0792 (UTC) FILETIME=[0E5B6500:01C70E7D] X-TMWD-Spam-Summary: SEV=1.1; DFV=A2006112211; IFV=2.0.6,4.0-7; RPD=4.00.0004; RPDID=303030312E30413031303230332E34353634433030412E303033443A5343464D413534333432342D462D2F4E4553574B563534472F71554B6D71577A564237673D3D; ENG=IBF; TS=20061122212749; CAT=NONE; CON=NONE; X-WSS-ID: 697A1F5E3LO241945-01-01 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C70E7D.0DE556B5" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/199 X-Sequence-Number: 23726 This is a multi-part message in MIME format. ------_=_NextPart_001_01C70E7D.0DE556B5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable This indicates that some stats parameters are not enabled in your "postgresql.conf" file. In particular, make sure that "stats_command_string" parameter is turned ON so that pg_stat_activity can report back the actual SQL statement.=20 =20 Sincerely, =20 -- =20 Husam=20 =20 ________________________________ From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono Sent: Wednesday, November 22, 2006 1:14 PM To: Tomeh, Husam Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Monitoring PostgreSQL Process On 11/22/06, Tomeh, Husam wrote:=20 =09You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html =09=20 =09Once you capture your SQL statement, you can run "explain" on it to check out its execution plan. So I went out to the server and selected on pg_stat_activity and found that stats_command_string had been turned off. I turned it on and gave postgres a reload command. Then I selected on pg_stat_activity again and got this:=20 my_db=3D# select * from pg_stat_activity where procpid in (30960, 30961, 877, 30306, 2830); =20datid | datname | procpid | usesysid | usename | current_query | query_start | backend_start | client_addr | client_port=20 -------+----------------------+---------+----------+----------+--------- ---------------------+-------------------------------+------------------ -------------+-------------+------------- =2029246 | my_db | 30960 | 24114 | usr1 | | | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1 | xxxxx =2029246 | my_db | 30961 | 24114 | usr1 | | | 2006-11-20 07:20:24.072152-06 | 127.0.0.1 | xxxxx =2029246 | my_db | 877 | 24114 | usr1 | | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1 | xxxxx =2029246 | my_db | 2830 | 24114 | usr1 | | | 2006-11-22 10:45:09.528452-06 | 127.0.0.1 | xxxxx=20 =2029246 | my_db | 30306 | 24113 | usr2 | | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1 | xxxxx (5 rows) I cleared out the client ports. The funny thing is that process ID's 30960, 30961 and 2830 are using a lot of the CPU but according to pg_stat_activity the has not been updated. Doesn't this suggest that those processes are NOT running anything on the database, or at least anything new?=20 Here is what I get from "ps axuf": postgres 25635 0.0 0.2 34864 5592 ? S Oct01 0:02 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 25637 0.0 0.0 11220 768 ? S Oct01 1:07 \_ postgres: logger process=20 postgres 25639 0.0 0.8 35100 17624 ? S Oct01 5:03 \_ postgres: writer process postgres 25640 0.0 0.0 12220 1568 ? S Oct01 0:50 \_ postgres: stats buffer process postgres 25641 0.0 0.0 11908 1224 ? S Oct01 1:03 | \_ postgres: stats collector process postgres 30950 2.2 1.1 38728 23000 ? S Nov20 74:23 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30951 2.1 1.1 38752 23328 ? S Nov20 70:22 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30960 21.3 1.1 39840 24740 ? S Nov20 715:38 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 30961 17.2 1.1 39496 24356 ? S Nov20 578:08 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 30962 9.5 1.1 39864 24588 ? R Nov20 320:22 \_ postgres: user1 my_db 127.0.0.1(xxx) PARSE postgres 31068 0.0 0.2 36584 4940 ? S Nov20 0:00 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31069 0.6 0.9 36724 20268 ? S Nov20 22:09 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31070 0.0 0.2 35792 4548 ? S Nov20 0:00 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31199 1.8 1.1 39320 23708 ? S Nov20 62:14 \_ postgres: user2 my_db 127.0.0.1(xxx) idle postgres 31359 0.6 1.0 37092 21308 ? S Nov20 21:11 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 31361 0.0 0.2 36720 5000 ? S Nov20 0:00 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 31362 1.5 1.0 38132 21384 ? S Nov20 53:26 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 32030 5.1 1.1 39816 24740 ? S Nov20 171:42 \_ postgres: user2 my_db 127.0.0.1(xxx) idle postgres 877 14.6 1.1 39812 24676 ? S Nov20 481:37 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 3008 4.9 1.1 39404 24460 ? S Nov20 158:17 \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction postgres 3009 1.7 1.1 39012 23804 ? S Nov20 56:44 \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction postgres 17186 1.2 1.1 38804 23204 ? S Nov20 31:19 \_ postgres: user3 my_db 127.0.0.1 (xxx) idle postgres 19966 2.7 1.1 38596 23056 ? S Nov21 25:08 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30306 19.4 1.1 39448 23908 ? S 07:43 86:45 \_ postgres: user2 my_db 127.0.0.1 (xxx) idle postgres 2830 15.4 1.0 38992 22068 ? S 10:45 40:40 \_ postgres: user1 my_db 127.0.0.1(xxx) idle Anyone have any clue why these processes are eating up so much CPU time? Thanks, Aaron --=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =20 Aaron Bono =20 Aranya Software Technologies, Inc. =20 http://www.aranya.com =20 http://codeelixir.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 ********************************************************************** This message contains confidential information intended only for the use = of the addressee(s) named above and may contain information that is legal= ly privileged. If you are not the addressee, or the person responsible f= or delivering it to the addressee, you are hereby notified that reading, = disseminating, distributing or copying this message is strictly prohibite= d. If you have received this message by mistake, please immediately noti= fy us by replying to the message and delete the original message immediat= ely thereafter. Thank you. =0D =20 FADLD Tag ********************************************************************** ------_=_NextPart_001_01C70E7D.0DE556B5 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: quoted-printable
This=20 indicates that some stats parameters are not enabled in your "postgresql.= conf"=20 file. In particular, make sure that "stats_command_string" parameter = ;is=20 turned ON so that pg_stat_activity can report back the actual SQL stateme= nt.=20
 
Sincerely,
 
--
 =20 Husam
 


From: aaron.bono@gmail.com=20 [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent:<= /B>=20 Wednesday, November 22, 2006 1:14 PM
To: Tomeh, Husam
Cc:= =20 pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Monitoring Post= greSQL=20 Process

On 11/22/06, Tomeh, Husam <= htomeh@firstam.com> wrote:
=20
=20
You may query the sys= tem view,=20 =20 pg_stat_activity to check out the current SQL statements running. To = track=20 =20 down executed SQL statements into a physical log file, you may want t= o enable=20 =20 statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-con= fig-logging.html
=20
 <= /DIV> =20
Once you capture your= =20SQL=20 =20 statement, you can run "explain" on it to check out its execution=20 =20 plan.

So I went out t= o the=20 server and selected on pg_stat_activity and found that stats_command_stri= ng had=20 been turned off.  I turned it on and gave postgres a reload command.=  =20 Then I selected on pg_stat_activity again and got this:

my_db=3D#= =20select *=20 from pg_stat_activity where procpid in (30960, 30961, 877, 30306,=20 2830);
 datid |      =20 datname        | procpid | usesysid | = usename  |       =20 current_query        =20 |         =20 query_start         =20 |        =20 backend_start         | client_ad= dr |=20 client_port=20
-------+----------------------+---------+----------+----------+------= ------------------------+-------------------------------+----------------= ---------------+-------------+-------------
 29246=20 | my_db |   30960 |    24114 | usr1 | <comman= d=20 string not enabled>=20 |            =             &= nbsp;     =20 | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1  =20 |       xxxxx
 29246 | my_db | = ; =20 30961 |    24114 | usr1 | <command string not enabled&g= t;=20 |            =             &= nbsp;     =20 | 2006-11-20 07:20:24.072152-06 | 127.0.0.1  =20 |       xxxxx
 29246 | my_db=20 |     877 |    24114 | usr1 |=20 <IDLE>          &= nbsp;           =20 | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1  =20 |       xxxxx
 29246 | my_db=20 |    2830 |    24114 | usr1 | <command s= tring=20 not enabled>=20 |            =             &= nbsp;     =20 | 2006-11-22 10:45:09.528452-06 | 127.0.0.1  =20 |       xxxxx
 29246 | my_db=20 |   30306 |    24113 | usr2  |=20 <BIND>          &= nbsp;           =20 | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1  =20 |       xxxxx
(5 rows)

I=20 cleared out the client ports.

The funny thing is that process ID's= =2030960,=20 30961 and 2830 are using a lot of the CPU but according to pg_stat_activi= ty the=20 <command string not enabled> has not been updated.  Doesn't th= is=20 suggest that those processes are NOT running anything on the database, or= =20at=20 least anything new?

Here is what I get from "ps axuf":

pos= tgres=20 25635  0.0  0.2 34864 5592 ?      = ; =20 S    Oct01   0:02 /usr/bin/postmaster -p 5432 -D= =20 /var/lib/pgsql/data
postgres 25637  0.0  0.0 11220  768= =20 ?        S   =20 Oct01   1:07  \_ postgres: logger process
postgres=20 25639  0.0  0.8 35100 17624 ?     &nbs= p;=20 S    Oct01   5:03  \_ postgres: writer=20 process
postgres 25640  0.0  0.0 12220 1568=20 ?        S   =20 Oct01   0:50  \_ postgres: stats buffer process
postgre= s=20 25641  0.0  0.0 11908 1224 ?      = ; =20 S    Oct01   1:03  |   \_ postgre= s:=20 stats collector process
postgres 30950  2.2  1.1 38728 23000= =20 ?       S    Nov20 =20 74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 3095= 1 =20 2.1  1.1 38752 23328 ?      =20 S    Nov20  70:22  \_ postgres: user3 my_db=20 127.0.0.1(xxx) idle
postgres 30960 21.3  1.1 39840 24740=20 ?       S    Nov20 715:38&nb= sp; \_=20 postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30961 17.2  1.= 1 39496=20 24356 ?       S    Nov20=20 578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres=20 30962  9.5  1.1 39864 24588 ?     &nbs= p;=20 R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1= (xxx)=20 PARSE
postgres 31068  0.0   0.2 36584 4940=20 ?        S   =20 Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx)= =20 idle
postgres 31069  0.6  0.9 36724 20268=20 ?       S    Nov20 =20 22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres= =20 31070  0.0  0.2 35792 4548 ?      = ; =20 S    Nov20   0:00  \_ postgres: user1 my_te= st_db=20 127.0.0.1(xxx) idle
postgres 31199  1.8  1.1 39320 23708=20 ?       S    Nov20 =20 62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres 3135= 9 =20 0.6  1.0 37092 21308 ?      =20 S    Nov20  21:11  \_ postgres: user2 my_test_db= =20 127.0.0.1(xxx) idle
postgres 31361  0.0  0.2 36720 5000=20 ?        S   =20 Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx)= =20 idle
postgres 31362  1.5  1.0 38132 21384=20 ?       S    Nov20 =20 53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres= =20 32030  5.1  1.1 39816 24740 ?     &nbs= p;=20 S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1= (xxx)=20 idle
postgres   877 14.6  1.1 39812 24676=20 ?       S    Nov20 481:37&nb= sp; \_=20 postgres: user1 my_db 127.0.0.1(xxx) idle
postgres  3008  4.= 9 =20 1.1 39404 24460 ?       S   = =20Nov20=20 158:17  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in=20 transaction
postgres  3009  1.7  1.1 39012 23804=20 ?       S    Nov20 =20 56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in=20 transaction
postgres 17186  1.2  1.1 38804 23204=20 ?       S    Nov20 =20 31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle
postgres=20 19966  2.7  1.1 38596 23056 ?     &nbs= p;=20 S    Nov21  25:08  \_ postgres: user3 my_db=20 127.0.0.1(xxx) idle
postgres 30306 19.4  1.1 39448 23908=20 ?       S    07:43 =20 86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle
postgres&nbs= p; 2830=20 15.4  1.0 38992 22068 ?      =20 S    10:45  40:40  \_ postgres: user1 my_db=20 127.0.0.1(xxx) idle

Anyone have any clue why these processes are e= ating=20 up so much CPU time?

Thanks,
Aaron

--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
  =20 Aaron Bono
   Aranya Software Technologies, Inc.
 &n= bsp; http://www.aranya.com
   = http://codeelixir.com
=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20

**********************************************************************=
This=20 message contains confidential information intended only for the use of th= e=20 addressee(s) named above and may contain information that is legally=20 privileged.  If you are not the addressee, or the person responsible= =20for=20 delivering it to the addressee, you are hereby notified that reading,=20 disseminating, distributing or copying this message is strictly=20 prohibited.  If you have received this message by mistake, please=20 immediately notify us by replying to the message and delete the original = message=20 immediately thereafter.

Thank you.

------_=_NextPart_001_01C70E7D.0DE556B5-- From postgresql@aranya.com Wed Nov 22 22:47:05 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 175D89FB1DA for ; Wed, 22 Nov 2006 18:47:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77437-08 for ; Wed, 22 Nov 2006 18:46:59 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 8A4E89F9EC9 for ; Wed, 22 Nov 2006 18:46:58 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so275431ugf for ; Wed, 22 Nov 2006 14:46:57 -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=KRb+urHf1epy/88+5V2ZCO8MzQpcUG8iHHUDB/kQSIIcT5UsPUPoa4i5+nrUaRhI628xeeN7RmVqdUZJ66gvHCzZfkk0suxrdsrFy2Krtpngv2kfS7HU+34qG15XvSTr/onFDGM58bHW9Rc3kSL9qafabyD7sG6w6ApydwsBuHQ= Received: by 10.78.134.12 with SMTP id h12mr8321492hud.1164231977133; Wed, 22 Nov 2006 13:46:17 -0800 (PST) Received: by 10.78.136.6 with HTTP; Wed, 22 Nov 2006 13:46:16 -0800 (PST) Message-ID: Date: Wed, 22 Nov 2006 15:46:16 -0600 From: "Aaron Bono" To: "Tomeh, Husam" Subject: Re: Monitoring PostgreSQL Process Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_64103_18347219.1164231976572" References: X-Google-Sender-Auth: 88924e7b8c755e6f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/197 X-Sequence-Number: 23724 ------=_Part_64103_18347219.1164231976572 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam wrote: > > This indicates that some stats parameters are not enabled in your " > postgresql.conf" file. In particular, make sure that > "stats_command_string" parameter is turned ON so that pg_stat_activity can > report back the actual SQL statement. > Right, as I mentioned, I had stats_command_string turned off but turned it on. What I expected to find is that the would remain until another query was run by that process. But since these processes are eating up so many CPU cycles, I thought they would show something other than pretty quickly. Since they did not show something new, why are they using so much of the CPU? Could the be caught in some kind of massive , seemingly endless query? In fact 30961 and 2830 are still not showing anything new and 30960 and 30306 show Idle most of the time. I know the application is causing the problem, I am just trying to figure out what part of the application is taking postgres so much. Thanks, Aaron ------------------------------ > *From:* aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] *On Behalf Of *Aaron > Bono > *Sent:* Wednesday, November 22, 2006 1:14 PM > *To:* Tomeh, Husam > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process > > On 11/22/06, Tomeh, Husam wrote: > > > > You may query the system view, pg_stat_activity to check out the > > current SQL statements running. To track down executed SQL statements into a > > physical log file, you may want to enable statement logging. For more > > details on that, check out: > > http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html > > > > Once you capture your SQL statement, you can run "explain" on it to > > check out its execution plan. > > > > So I went out to the server and selected on pg_stat_activity and found > that stats_command_string had been turned off. I turned it on and gave > postgres a reload command. Then I selected on pg_stat_activity again and > got this: > > my_db=# select * from pg_stat_activity where procpid in (30960, 30961, > 877, 30306, 2830); > datid | datname | procpid | usesysid | usename | > current_query | query_start | > backend_start | client_addr | client_port > > -------+----------------------+---------+----------+----------+------------------------------+-------------------------------+-------------------------------+-------------+------------- > 29246 | my_db | 30960 | 24114 | usr1 | > | | 2006-11-20 07:20: 24.068052-06 | > 127.0.0.1 | xxxxx > 29246 | my_db | 30961 | 24114 | usr1 | > | | 2006-11-20 07:20:24.072152-06 | > 127.0.0.1 | xxxxx > 29246 | my_db | 877 | 24114 | usr1 | > | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | > 127.0.0.1 | xxxxx > 29246 | my_db | 2830 | 24114 | usr1 | > | | 2006-11-22 10:45:09.528452-06 | > 127.0.0.1 | xxxxx > 29246 | my_db | 30306 | 24113 | usr2 | > | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | > 127.0.0.1 | xxxxx > (5 rows) > > I cleared out the client ports. > > The funny thing is that process ID's 30960, 30961 and 2830 are using a lot > of the CPU but according to pg_stat_activity the enabled> has not been updated. Doesn't this suggest that those processes > are NOT running anything on the database, or at least anything new? > > Here is what I get from "ps axuf": > > postgres 25635 0.0 0.2 34864 5592 ? S Oct01 0:02 > /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data > postgres 25637 0.0 0.0 11220 768 ? S Oct01 1:07 \_ > postgres: logger process > postgres 25639 0.0 0.8 35100 17624 ? S Oct01 5:03 \_ > postgres: writer process > postgres 25640 0.0 0.0 12220 1568 ? S Oct01 0:50 \_ > postgres: stats buffer process > postgres 25641 0.0 0.0 11908 1224 ? S Oct01 1:03 | \_ > postgres: stats collector process > postgres 30950 2.2 1.1 38728 23000 ? S Nov20 74:23 \_ > postgres: user3 my_db 127.0.0.1(xxx) idle > postgres 30951 2.1 1.1 38752 23328 ? S Nov20 70:22 \_ > postgres: user3 my_db 127.0.0.1(xxx) idle > postgres 30960 21.3 1.1 39840 24740 ? S Nov20 715:38 \_ > postgres: user1 my_db 127.0.0.1(xxx) idle > postgres 30961 17.2 1.1 39496 24356 ? S Nov20 578:08 \_ > postgres: user1 my_db 127.0.0.1(xxx) idle > postgres 30962 9.5 1.1 39864 24588 ? R Nov20 320:22 \_ > postgres: user1 my_db 127.0.0.1(xxx) PARSE > postgres 31068 0.0 0.2 36584 4940 ? S Nov20 0:00 \_ > postgres: user1 my_test_db 127.0.0.1(xxx) idle > postgres 31069 0.6 0.9 36724 20268 ? S Nov20 22:09 \_ > postgres: user1 my_test_db 127.0.0.1(xxx) idle > postgres 31070 0.0 0.2 35792 4548 ? S Nov20 0:00 \_ > postgres: user1 my_test_db 127.0.0.1(xxx) idle > postgres 31199 1.8 1.1 39320 23708 ? S Nov20 62:14 \_ > postgres: user2 my_db 127.0.0.1(xxx) idle > postgres 31359 0.6 1.0 37092 21308 ? S Nov20 21:11 \_ > postgres: user2 my_test_db 127.0.0.1(xxx) idle > postgres 31361 0.0 0.2 36720 5000 ? S Nov20 0:00 \_ > postgres: user2 my_test_db 127.0.0.1(xxx) idle > postgres 31362 1.5 1.0 38132 21384 ? S Nov20 53:26 \_ > postgres: user2 my_test_db 127.0.0.1(xxx) idle > postgres 32030 5.1 1.1 39816 24740 ? S Nov20 171:42 \_ > postgres: user2 my_db 127.0.0.1(xxx) idle > postgres 877 14.6 1.1 39812 24676 ? S Nov20 481:37 \_ > postgres: user1 my_db 127.0.0.1(xxx) idle > postgres 3008 4.9 1.1 39404 24460 ? S Nov20 158:17 \_ > postgres: user2 my_db 127.0.0.1(xxx) idle in transaction > postgres 3009 1.7 1.1 39012 23804 ? S Nov20 56:44 \_ > postgres: user2 my_db 127.0.0.1(xxx) idle in transaction > postgres 17186 1.2 1.1 38804 23204 ? S Nov20 31:19 \_ > postgres: user3 my_db 127.0.0.1 (xxx) idle > postgres 19966 2.7 1.1 38596 23056 ? S Nov21 25:08 \_ > postgres: user3 my_db 127.0.0.1(xxx) idle > postgres 30306 19.4 1.1 39448 23908 ? S 07:43 86:45 \_ > postgres: user2 my_db 127.0.0.1 (xxx) idle > postgres 2830 15.4 1.0 38992 22068 ? S 10:45 40:40 \_ > postgres: user1 my_db 127.0.0.1(xxx) idle > > Anyone have any clue why these processes are eating up so much CPU time? > > Thanks, > Aaron > > -- > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > http://codeelixir.com > ================================================================== > > ********************************************************************** > This message contains confidential information intended only for the use > of the addressee(s) named above and may contain information that is legally > privileged. If you are not the addressee, or the person responsible for > delivering it to the addressee, you are hereby notified that reading, > disseminating, distributing or copying this message is strictly prohibited. > If you have received this message by mistake, please immediately notify us > by replying to the message and delete the original message immediately > thereafter. > > Thank you. > > > FADLD Tag > ********************************************************************** > -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_64103_18347219.1164231976572 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
This indicates that some stats parameters are not enabled in your "postgresql.conf" file. In particular, make sure that "stats_command_string" parameter is turned ON so that pg_stat_activity can report back the actual SQL statement.

Right, as I mentioned, I had stats_command_string turned off but turned it on.  What I expected to find is that the <command string not enabled> would remain until another query was run by that process.  But since these processes are eating up so many CPU cycles, I thought they would show something other than <command string not enabled> pretty quickly.  Since they did not show something new, why are they using so much of the CPU?  Could the be caught in some kind of massive , seemingly endless query?

In fact 30961 and 2830 are still not showing anything new and 30960 and 30306 show Idle most of the time.

I know the application is causing the problem, I am just trying to figure out what part of the application is taking postgres so much.

Thanks,
Aaron


From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Wednesday, November 22, 2006 1:14 PM
To: Tomeh, Husam
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Monitoring PostgreSQL Process

On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html
 
Once you capture your SQL statement, you can run "explain" on it to check out its execution plan.

So I went out to the server and selected on pg_stat_activity and found that stats_command_string had been turned off.  I turned it on and gave postgres a reload command.  Then I selected on pg_stat_activity again and got this:

my_db=# select * from pg_stat_activity where procpid in (30960, 30961, 877, 30306, 2830);
 datid |       datname        | procpid | usesysid | usename  |        current_query         |          query_start          |         backend_start         | client_addr | client_port
-------+----------------------+---------+----------+----------+------------------------------+-------------------------------+-------------------------------+-------------+-------------
 29246 | my_db |   30960 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |   30961 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-20 07:20:24.072152-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |     877 |    24114 | usr1 | <IDLE>                       | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |    2830 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-22 10:45:09.528452-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |   30306 |    24113 | usr2  | <BIND>                       | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1   |       xxxxx
(5 rows)

I cleared out the client ports.

The funny thing is that process ID's 30960, 30961 and 2830 are using a lot of the CPU but according to pg_stat_activity the <command string not enabled> has not been updated.  Doesn't this suggest that those processes are NOT running anything on the database, or at least anything new?

Here is what I get from "ps axuf":

postgres 25635  0.0  0.2 34864 5592 ?        S    Oct01   0:02 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 25637  0.0  0.0 11220  768 ?        S    Oct01   1:07  \_ postgres: logger process
postgres 25639  0.0  0.8 35100 17624 ?       S    Oct01   5:03  \_ postgres: writer process
postgres 25640  0.0  0.0 12220 1568 ?        S    Oct01   0:50  \_ postgres: stats buffer process
postgres 25641  0.0  0.0 11908 1224 ?        S    Oct01   1:03  |   \_ postgres: stats collector process
postgres 30950  2.2  1.1 38728 23000 ?       S    Nov20  74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30951  2.1  1.1 38752 23328 ?       S    Nov20  70:22  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30960 21.3  1.1 39840 24740 ?       S    Nov20 715:38  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30961 17.2  1.1 39496 24356 ?       S    Nov20 578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30962  9.5  1.1 39864 24588 ?       R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1(xxx) PARSE
postgres 31068  0.0   0.2 36584 4940 ?        S    Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31069  0.6  0.9 36724 20268 ?       S    Nov20  22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31070  0.0  0.2 35792 4548 ?        S    Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31199  1.8  1.1 39320 23708 ?       S    Nov20  62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres 31359  0.6  1.0 37092 21308 ?       S    Nov20  21:11  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 31361  0.0  0.2 36720 5000 ?        S    Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 31362  1.5  1.0 38132 21384 ?       S    Nov20  53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 32030  5.1  1.1 39816 24740 ?       S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres   877 14.6  1.1 39812 24676 ?       S    Nov20 481:37  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres  3008  4.9  1.1 39404 24460 ?       S    Nov20 158:17  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction
postgres  3009  1.7  1.1 39012 23804 ?       S    Nov20  56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction
postgres 17186  1.2  1.1 38804 23204 ?       S    Nov20  31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle
postgres 19966  2.7  1.1 38596 23056 ?       S    Nov21  25:08  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30306 19.4  1.1 39448 23908 ?       S    07:43  86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle
postgres  2830 15.4  1.0 38992 22068 ?       S    10:45  40:40  \_ postgres: user1 my_db 127.0.0.1(xxx) idle

Anyone have any clue why these processes are eating up so much CPU time?

Thanks,
Aaron

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

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged.  If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited.  If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

                                                                                                                         FADLD Tag
**********************************************************************




--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_64103_18347219.1164231976572-- From htomeh@firstam.com Wed Nov 22 22:48:58 2006 Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 68D959FB1DA for ; Wed, 22 Nov 2006 18:48:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 45588-04 for ; Wed, 22 Nov 2006 18:48:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outbound-smtp.firstam.com (outbound-smtp3.firstam.com [69.87.54.8]) by postgresql.org (Postfix) with ESMTP id 4F8279FB1DD for ; Wed, 22 Nov 2006 18:48:55 -0400 (AST) Received: from 10.48.129.31 by outbound-smtp.firstam.com with ESMTP ( Hello SMTP Relay); Wed, 22 Nov 2006 13:53:21 -0800 X-Server-Uuid: A20F5341-692A-45C7-975F-AE726A98C579 Received: from unknown (HELO pisgsna01smmx01.ana.firstamdata.com) ( [172.17.88.35]) by FAEMSNA01SMXS02.FIRSTAM.COM with ESMTP; 22 Nov 2006 13:53:22 -0800 Received: from pisgsna01sxch21.ana.firstamdata.com (Not Verified[172.17.88.71]) by pisgsna01smmx01.ana.firstamdata.com with MailMarshal (v6,1,6,1172) id ; Wed, 22 Nov 2006 13:53:20 -0800 Received: from CITGSNA01SXCH02.ana.firstamdata.com ([172.17.88.30]) by pisgsna01sxch21.ana.firstamdata.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 22 Nov 2006 13:53:20 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: Re: Monitoring PostgreSQL Process Date: Wed, 22 Nov 2006 13:53:20 -0800 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Monitoring PostgreSQL Process Thread-Index: AccOf7TkjgGexZnfR76xbp5lBDS/HwAAJ5bA From: "Tomeh, Husam" To: "Aaron Bono" cc: pgsql-admin@postgresql.org X-OriginalArrivalTime: 22 Nov 2006 21:53:20.0770 (UTC) FILETIME=[A0154620:01C70E80] X-TMWD-Spam-Summary: SEV=1.1; DFV=A2006112211; IFV=2.0.6,4.0-7; RPD=4.00.0004; RPDID=303030312E30413031303230342E34353634433630362E303045363A5343464D413534333432342D462D2F4E4553574B563534472F71554B6D71577A564237673D3D; ENG=IBF; TS=20061122215321; CAT=NONE; CON=NONE; X-WSS-ID: 697A195B3EG842879-01-01 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C70E80.9FC1D88E" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/198 X-Sequence-Number: 23725 This is a multi-part message in MIME format. ------_=_NextPart_001_01C70E80.9FC1D88E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable If you're not logged in as a superuser, you won't be able to see other user's queries. Try to login as a superuser (ei, postgres or whatever you superuser account is). =20 Sincerely, =20 -- Husam=20 =20 ________________________________ From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono Sent: Wednesday, November 22, 2006 1:46 PM To: Tomeh, Husam Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Monitoring PostgreSQL Process On 11/22/06, Tomeh, Husam wrote:=20 This indicates that some stats parameters are not enabled in your "postgresql.conf" file. In particular, make sure that "stats_command_string" parameter is turned ON so that pg_stat_activity can report back the actual SQL statement.=20 Right, as I mentioned, I had stats_command_string turned off but turned it on. What I expected to find is that the would remain until another query was run by that process. But since these processes are eating up so many CPU cycles, I thought they would show something other than pretty quickly. Since they did not show something new, why are they using so much of the CPU? Could the be caught in some kind of massive , seemingly endless query? In fact 30961 and 2830 are still not showing anything new and 30960 and 30306 show Idle most of the time.=20 I know the application is causing the problem, I am just trying to figure out what part of the application is taking postgres so much. Thanks, Aaron ________________________________ From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono Sent: Wednesday, November 22, 2006 1:14 PM To: Tomeh, Husam Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Monitoring PostgreSQL Process =09 =09 =09 On 11/22/06, Tomeh, Husam wrote:=20 =09 You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html=20 =20 Once you capture your SQL statement, you can run "explain" on it to check out its execution plan. So I went out to the server and selected on pg_stat_activity and found that stats_command_string had been turned off. I turned it on and gave postgres a reload command. Then I selected on pg_stat_activity again and got this:=20 =09 my_db=3D# select * from pg_stat_activity where procpid in (30960, 30961, 877, 30306, 2830); datid | datname | procpid | usesysid | usename | current_query | query_start | backend_start | client_addr | client_port=20 =09 -------+----------------------+---------+----------+----------+--------- ---------------------+-------------------------------+------------------ -------------+-------------+------------- 29246 | my_db | 30960 | 24114 | usr1 | | | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1 | xxxxx 29246 | my_db | 30961 | 24114 | usr1 | | | 2006-11-20 07:20:24.072152-06 | 127.0.0.1 | xxxxx 29246 | my_db | 877 | 24114 | usr1 | | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1 | xxxxx 29246 | my_db | 2830 | 24114 | usr1 | | | 2006-11-22 10:45:09.528452-06 | 127.0.0.1 | xxxxx=20 29246 | my_db | 30306 | 24113 | usr2 | | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1 | xxxxx (5 rows) =09 I cleared out the client ports. =09 The funny thing is that process ID's 30960, 30961 and 2830 are using a lot of the CPU but according to pg_stat_activity the has not been updated. Doesn't this suggest that those processes are NOT running anything on the database, or at least anything new?=20 =09 Here is what I get from "ps axuf": =09 postgres 25635 0.0 0.2 34864 5592 ? S Oct01 0:02 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 25637 0.0 0.0 11220 768 ? S Oct01 1:07 \_ postgres: logger process=20 postgres 25639 0.0 0.8 35100 17624 ? S Oct01 5:03 \_ postgres: writer process postgres 25640 0.0 0.0 12220 1568 ? S Oct01 0:50 \_ postgres: stats buffer process postgres 25641 0.0 0.0 11908 1224 ? S Oct01 1:03 | \_ postgres: stats collector process postgres 30950 2.2 1.1 38728 23000 ? S Nov20 74:23 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30951 2.1 1.1 38752 23328 ? S Nov20 70:22 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30960 21.3 1.1 39840 24740 ? S Nov20 715:38 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 30961 17.2 1.1 39496 24356 ? S Nov20 578:08 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 30962 9.5 1.1 39864 24588 ? R Nov20 320:22 \_ postgres: user1 my_db 127.0.0.1(xxx) PARSE postgres 31068 0.0 0.2 36584 4940 ? S Nov20 0:00 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31069 0.6 0.9 36724 20268 ? S Nov20 22:09 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31070 0.0 0.2 35792 4548 ? S Nov20 0:00 \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle postgres 31199 1.8 1.1 39320 23708 ? S Nov20 62:14 \_ postgres: user2 my_db 127.0.0.1(xxx) idle postgres 31359 0.6 1.0 37092 21308 ? S Nov20 21:11 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 31361 0.0 0.2 36720 5000 ? S Nov20 0:00 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 31362 1.5 1.0 38132 21384 ? S Nov20 53:26 \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle postgres 32030 5.1 1.1 39816 24740 ? S Nov20 171:42 \_ postgres: user2 my_db 127.0.0.1(xxx) idle postgres 877 14.6 1.1 39812 24676 ? S Nov20 481:37 \_ postgres: user1 my_db 127.0.0.1(xxx) idle postgres 3008 4.9 1.1 39404 24460 ? S Nov20 158:17 \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction postgres 3009 1.7 1.1 39012 23804 ? S Nov20 56:44 \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction postgres 17186 1.2 1.1 38804 23204 ? S Nov20 31:19 \_ postgres: user3 my_db 127.0.0.1 (xxx) idle postgres 19966 2.7 1.1 38596 23056 ? S Nov21 25:08 \_ postgres: user3 my_db 127.0.0.1(xxx) idle postgres 30306 19.4 1.1 39448 23908 ? S 07:43 86:45 \_ postgres: user2 my_db 127.0.0.1 (xxx) idle postgres 2830 15.4 1.0 38992 22068 ? S 10:45 40:40 \_ postgres: user1 my_db 127.0.0.1(xxx) idle =09 Anyone have any clue why these processes are eating up so much CPU time? =09 Thanks, Aaron =09 --=20 =09 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com =09 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 =09 ********************************************************************** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. =09 FADLD Tag =09 ********************************************************************** --=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com=20 http://codeelixir.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 ------_=_NextPart_001_01C70E80.9FC1D88E Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: quoted-printable
If you're=20 not logged in as a superuser, you won't be able to see other user's = queries. Try=20 to login as a superuser (ei, postgres or whatever you superuser account=20 is).
 
Sincerely,
 
--
 =20 Husam
 


From: aaron.bono@gmail.com=20 [mailto:aaron.bono@gmail.com] On Behalf Of Aaron = Bono
Sent:=20 Wednesday, November 22, 2006 1:46 PM
To: Tomeh, = Husam
Cc:=20 pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Monitoring = PostgreSQL=20 Process

On 11/22/06, Tomeh, Husam = <htomeh@firstam.com> wrote:

          &nbs= p;            = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =              = FADLD=20 Tag
***********************************************************= ***********

This indicates that = some stats=20 parameters are not enabled in your "postgresql.conf" file. In = particular, make=20 sure that "stats_command_string" parameter is turned ON so that=20 pg_stat_activity can report back the actual SQL statement.=20

Right, as I mentioned, I had stats_command_string turned off = but turned=20 it on.  What I expected to find is that the <command string not enabled> would = remain until=20 another query was run by that process.  But since these processes = are=20 eating up so many CPU cycles, I thought they would show something other = than=20 <command string not = enabled>=20 pretty quickly.  Since they did not show something new, why are = they using=20 so much of the CPU?  Could the be caught in some kind of massive ,=20 seemingly endless query?

In fact 30961 and 2830 are still not = showing=20 anything new and 30960 and 30306 show Idle most of the time.

I = know the=20 application is causing the problem, I am just trying to figure out what = part of=20 the application is taking postgres so=20 much.

Thanks,
Aaron


From: aaron.bono@gmail.com=20 [mailto:aaron.bono@gmail.com]=20 On Behalf Of Aaron Bono
Sent: Wednesday, November 22, = 2006=20 1:14 PM
To: Tomeh, Husam
Cc: pgsql-admin@postgresql.org
Subject: Re: = [ADMIN]=20 Monitoring PostgreSQL Process

On 11/22/06, Tomeh, Husam = <htomeh@firstam.com>=20 wrote:=20
You may query the = system view,=20 pg_stat_activity to check out the current SQL statements running. To = track=20 down executed SQL statements into a physical log file, you may want = to=20 enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-= logging.html=20
 
Once you capture = your SQL=20 statement, you can run "explain" on it to check out its execution=20 plan.

So I went out = to the=20 server and selected on pg_stat_activity and found that = stats_command_string=20 had been turned off.  I turned it on and gave postgres a reload=20 command.  Then I selected on pg_stat_activity again and got this: =

my_db=3D# select * from pg_stat_activity where procpid in = (30960, 30961,=20 877, 30306, 2830);
 datid = |      =20 datname        | procpid | usesysid = |=20 usename  |       =20 current_query        =20 |         =20 query_start         =20 |        =20 backend_start         | = client_addr |=20 client_port=20 =
-------+----------------------+---------+----------+----------+------= ------------------------+-------------------------------+----------------= ---------------+-------------+-------------
 29246=20 | my_db |   30960 |    24114 | usr1 | = <command=20 string not enabled>=20 = |            =             &= nbsp;     =20 | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1   = |      =20 xxxxx
 29246 | my_db |   30961 |    = 24114 |=20 usr1 | <command string not enabled>=20 = |            =             &= nbsp;     =20 | 2006-11-20 07:20:24.072152-06 | 127.0.0.1   = |      =20 xxxxx
 29246 | my_db |     877 = |   =20 24114 | usr1 |=20 = <IDLE>          &= nbsp;           =20 | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1   = |      =20 xxxxx
 29246 | my_db |    2830 = |   =20 24114 | usr1 | <command string not enabled>=20 = |            =             &= nbsp;     =20 | 2006-11-22 10:45:09.528452-06 | 127.0.0.1   = |      =20 xxxxx
 29246 | my_db |   30306 |    = 24113 |=20 usr2  |=20 = <BIND>          &= nbsp;           =20 | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1   = |      =20 xxxxx
(5 rows)

I cleared out the client = ports.

The=20 funny thing is that process ID's 30960, 30961 and 2830 are using a lot = of the=20 CPU but according to pg_stat_activity the <command string not = enabled>=20 has not been updated.  Doesn't this suggest that those processes = are NOT=20 running anything on the database, or at least anything new? =

Here is=20 what I get from "ps axuf":

postgres 25635  0.0  0.2 = 34864=20 5592 ?        S   =20 Oct01   0:02 /usr/bin/postmaster -p 5432 -D=20 /var/lib/pgsql/data
postgres 25637  0.0  0.0 11220  = 768=20 ?        S   =20 Oct01   1:07  \_ postgres: logger process
postgres=20 25639  0.0  0.8 35100 17624 = ?      =20 S    Oct01   5:03  \_ postgres: writer=20 process
postgres 25640  0.0  0.0 12220 1568=20 ?        S   =20 Oct01   0:50  \_ postgres: stats buffer = process
postgres=20 25641  0.0  0.0 11908 1224=20 ?        S   =20 Oct01   1:03  |   \_ postgres: stats = collector=20 process
postgres 30950  2.2  1.1 38728 23000=20 ?       S    Nov20 =20 74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres=20 30951  2.1  1.1 38752 23328 = ?      =20 S    Nov20  70:22  \_ postgres: user3 my_db=20 127.0.0.1(xxx) idle
postgres 30960 21.3  1.1 39840 24740=20 ?       S    Nov20 = 715:38 =20 \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30961 = 17.2  1.1=20 39496 24356 ?       S    = Nov20=20 578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres=20 30962  9.5  1.1 39864 24588 = ?      =20 R    Nov20 320:22  \_ postgres: user1 my_db = 127.0.0.1(xxx)=20 PARSE
postgres 31068  0.0   0.2 36584 4940=20 ?        S   =20 Nov20   0:00  \_ postgres: user1 my_test_db = 127.0.0.1(xxx)=20 idle
postgres 31069  0.6  0.9 36724 20268=20 ?       S    Nov20 =20 22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) = idle
postgres=20 31070  0.0  0.2 35792 4548=20 ?        S   =20 Nov20   0:00  \_ postgres: user1 my_test_db = 127.0.0.1(xxx)=20 idle
postgres 31199  1.8  1.1 39320 23708=20 ?       S    Nov20 =20 62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres=20 31359  0.6  1.0 37092 21308 = ?      =20 S    Nov20  21:11  \_ postgres: user2 = my_test_db=20 127.0.0.1(xxx) idle
postgres 31361  0.0  0.2 36720 5000=20 ?        S   =20 Nov20   0:00  \_ postgres: user2 my_test_db = 127.0.0.1(xxx)=20 idle
postgres 31362  1.5  1.0 38132 21384=20 ?       S    Nov20 =20 53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) = idle
postgres=20 32030  5.1  1.1 39816 24740 = ?      =20 S    Nov20 171:42  \_ postgres: user2 my_db = 127.0.0.1(xxx)=20 idle
postgres   877 14.6  1.1 39812 24676=20 ?       S    Nov20 = 481:37 =20 \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres  = 3008 =20 4.9  1.1 39404 24460 ?      =20 S    Nov20 158:17  \_ postgres: user2 my_db = 127.0.0.1(xxx)=20 idle in transaction
postgres  3009  1.7  1.1 39012 = 23804=20 ?       S    Nov20 =20 56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in=20 transaction
postgres 17186  1.2  1.1 38804 23204=20 ?       S    Nov20 =20 31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle
postgres 19966  = 2.7  1.1=20 38596 23056 ?       S   =20 Nov21  25:08  \_ postgres: user3 my_db 127.0.0.1(xxx)=20 idle
postgres 30306 19.4  1.1 39448 23908=20 ?       S    07:43 =20 86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle
postgres  2830 = 15.4  1.0=20 38992 22068 ?       S   =20 10:45  40:40  \_ postgres: user1 my_db 127.0.0.1(xxx)=20 idle

Anyone have any clue why these processes are eating up so = much CPU=20 time?

Thanks,
Aaron

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

**********************************************************************=
This=20 message contains confidential information intended only for the use of = the=20 addressee(s) named above and may contain information that is legally=20 privileged.  If you are not the addressee, or the person = responsible for=20 delivering it to the addressee, you are hereby notified that reading,=20 disseminating, distributing or copying this message is strictly=20 prohibited.  If you have received this message by mistake, please = immediately notify us by replying to the message and delete the = original=20 message immediately thereafter.

Thank you.

          &nbs= p;            = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =              = FADLD=20 = Tag
***********************************************************= ***********



--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
  =20 Aaron Bono
   Aranya Software Technologies, = Inc.
   http://www.aranya.com =
   http://codeelixir.com
=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 ------_=_NextPart_001_01C70E80.9FC1D88E-- From postgresql@aranya.com Wed Nov 22 22:41:18 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4B3059FB1DD for ; Wed, 22 Nov 2006 18:41:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77231-05 for ; Wed, 22 Nov 2006 18:41:12 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 532CB9FB1CE for ; Wed, 22 Nov 2006 18:41:10 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so274497ugf for ; Wed, 22 Nov 2006 14:41:08 -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=cjt4sl9AC0pRxCki/dKiCJ6mE83ND0Q2JGPm1HMsIqNuM1MDRTHb7qlUAZ9nckWxDIZsLVtdXWBqVGOBc3djlGkMdrjvEughvgU7QbyNBN+EdglDSLw38kudHNC5Rc9nGQj19DjDuxNHCBw0WsN9wpIrtm2VTvtgMaHytfaJ66c= Received: by 10.78.75.15 with SMTP id x15mr8295834hua.1164233529216; Wed, 22 Nov 2006 14:12:09 -0800 (PST) Received: by 10.78.136.6 with HTTP; Wed, 22 Nov 2006 14:12:08 -0800 (PST) Message-ID: Date: Wed, 22 Nov 2006 16:12:08 -0600 From: "Aaron Bono" To: "Tomeh, Husam" Subject: Re: Monitoring PostgreSQL Process Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_64243_23941252.1164233528995" References: X-Google-Sender-Auth: eb71d64d06fbdc65 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/196 X-Sequence-Number: 23723 ------=_Part_64243_23941252.1164233528995 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam wrote: > > If you're not logged in as a superuser, you won't be able to see other > user's queries. Try to login as a superuser (ei, postgres or whatever you > superuser account is). > I am logging in as postgres when I check these tables. ------------------------------ > *From:* aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] *On Behalf Of *Aaron > Bono > *Sent:* Wednesday, November 22, 2006 1:46 PM > *To:* Tomeh, Husam > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process > > On 11/22/06, Tomeh, Husam wrote: > > > > This indicates that some stats parameters are not enabled in your " > > postgresql.conf" file. In particular, make sure that > > "stats_command_string" parameter is turned ON so that pg_stat_activity can > > report back the actual SQL statement. > > > > Right, as I mentioned, I had stats_command_string turned off but turned it > on. What I expected to find is that the > would remain until another query was run by that process. But since these > processes are eating up so many CPU cycles, I thought they would show > something other than pretty quickly. Since > they did not show something new, why are they using so much of the CPU? > Could the be caught in some kind of massive , seemingly endless query? > > In fact 30961 and 2830 are still not showing anything new and 30960 and > 30306 show Idle most of the time. > > I know the application is causing the problem, I am just trying to figure > out what part of the application is taking postgres so much. > > Thanks, > Aaron > > ------------------------------ > > *From:* aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] *On Behalf Of > > *Aaron Bono > > *Sent:* Wednesday, November 22, 2006 1:14 PM > > *To:* Tomeh, Husam > > *Cc:* pgsql-admin@postgresql.org > > *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process > > > > On 11/22/06, Tomeh, Husam wrote: > > > > > > You may query the system view, pg_stat_activity to check out the > > > current SQL statements running. To track down executed SQL statements into a > > > physical log file, you may want to enable statement logging. For more > > > details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html > > > > > > > > > Once you capture your SQL statement, you can run "explain" on it to > > > check out its execution plan. > > > > > > > So I went out to the server and selected on pg_stat_activity and found > > that stats_command_string had been turned off. I turned it on and gave > > postgres a reload command. Then I selected on pg_stat_activity again and > > got this: > > > > my_db=# select * from pg_stat_activity where procpid in (30960, 30961, > > 877, 30306, 2830); > > datid | datname | procpid | usesysid | usename | > > current_query | query_start | > > backend_start | client_addr | client_port > > > > -------+----------------------+---------+----------+----------+------------------------------+-------------------------------+-------------------------------+-------------+------------- > > 29246 | my_db | 30960 | 24114 | usr1 | > enabled> | | 2006-11-20 07:20: > > 24.068052-06 | 127.0.0.1 | xxxxx > > 29246 | my_db | 30961 | 24114 | usr1 | > enabled> | | 2006-11-20 07:20:24.072152-06| > > 127.0.0.1 | xxxxx > > 29246 | my_db | 877 | 24114 | usr1 | > > | 2006-11-22 15:04:42.230017-06 | > > 2006-11-20 08:20:35.765786-06 | 127.0.0.1 | xxxxx > > 29246 | my_db | 2830 | 24114 | usr1 | > enabled> | | 2006-11-22 10:45:09.528452-06| > > 127.0.0.1 | xxxxx > > 29246 | my_db | 30306 | 24113 | usr2 | > > | 2006-11-22 15:04:42.408911-06 | > > 2006-11-22 07:43:08.825445-06 | 127.0.0.1 | xxxxx > > (5 rows) > > > > I cleared out the client ports. > > > > The funny thing is that process ID's 30960, 30961 and 2830 are using a > > lot of the CPU but according to pg_stat_activity the > enabled> has not been updated. Doesn't this suggest that those processes > > are NOT running anything on the database, or at least anything new? > > > > Here is what I get from "ps axuf": > > > > postgres 25635 0.0 0.2 34864 5592 ? S Oct01 0:02 > > /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data > > postgres 25637 0.0 0.0 11220 768 ? S Oct01 1:07 \_ > > postgres: logger process > > postgres 25639 0.0 0.8 35100 17624 ? S Oct01 5:03 \_ > > postgres: writer process > > postgres 25640 0.0 0.0 12220 1568 ? S Oct01 0:50 \_ > > postgres: stats buffer process > > postgres 25641 0.0 0.0 11908 1224 ? S Oct01 1:03 | \_ > > postgres: stats collector process > > postgres 30950 2.2 1.1 38728 23000 ? S Nov20 74:23 \_ > > postgres: user3 my_db 127.0.0.1(xxx) idle > > postgres 30951 2.1 1.1 38752 23328 ? S Nov20 70:22 \_ > > postgres: user3 my_db 127.0.0.1(xxx) idle > > postgres 30960 21.3 1.1 39840 24740 ? S Nov20 715:38 \_ > > postgres: user1 my_db 127.0.0.1(xxx) idle > > postgres 30961 17.2 1.1 39496 24356 ? S Nov20 578:08 \_ > > postgres: user1 my_db 127.0.0.1(xxx) idle > > postgres 30962 9.5 1.1 39864 24588 ? R Nov20 320:22 \_ > > postgres: user1 my_db 127.0.0.1(xxx) PARSE > > postgres 31068 0.0 0.2 36584 4940 ? S Nov20 0:00 \_ > > postgres: user1 my_test_db 127.0.0.1(xxx) idle > > postgres 31069 0.6 0.9 36724 20268 ? S Nov20 22:09 \_ > > postgres: user1 my_test_db 127.0.0.1(xxx) idle > > postgres 31070 0.0 0.2 35792 4548 ? S Nov20 0:00 \_ > > postgres: user1 my_test_db 127.0.0.1(xxx) idle > > postgres 31199 1.8 1.1 39320 23708 ? S Nov20 62:14 \_ > > postgres: user2 my_db 127.0.0.1(xxx) idle > > postgres 31359 0.6 1.0 37092 21308 ? S Nov20 21:11 \_ > > postgres: user2 my_test_db 127.0.0.1(xxx) idle > > postgres 31361 0.0 0.2 36720 5000 ? S Nov20 0:00 \_ > > postgres: user2 my_test_db 127.0.0.1(xxx) idle > > postgres 31362 1.5 1.0 38132 21384 ? S Nov20 53:26 \_ > > postgres: user2 my_test_db 127.0.0.1(xxx) idle > > postgres 32030 5.1 1.1 39816 24740 ? S Nov20 171:42 \_ > > postgres: user2 my_db 127.0.0.1(xxx) idle > > postgres 877 14.6 1.1 39812 24676 ? S Nov20 481:37 \_ > > postgres: user1 my_db 127.0.0.1(xxx) idle > > postgres 3008 4.9 1.1 39404 24460 ? S Nov20 158:17 \_ > > postgres: user2 my_db 127.0.0.1(xxx) idle in transaction > > postgres 3009 1.7 1.1 39012 23804 ? S Nov20 56:44 \_ > > postgres: user2 my_db 127.0.0.1(xxx) idle in transaction > > postgres 17186 1.2 1.1 38804 23204 ? S Nov20 31:19 \_ > > postgres: user3 my_db 127.0.0.1 (xxx) idle > > postgres 19966 2.7 1.1 38596 23056 ? S Nov21 25:08 \_ > > postgres: user3 my_db 127.0.0.1(xxx) idle > > postgres 30306 19.4 1.1 39448 23908 ? S 07:43 86:45 \_ > > postgres: user2 my_db 127.0.0.1 (xxx) idle > > postgres 2830 15.4 1.0 38992 22068 ? S 10:45 40:40 \_ > > postgres: user1 my_db 127.0.0.1(xxx) idle > > > > Anyone have any clue why these processes are eating up so much CPU time? > > > > Thanks, > > Aaron > > > > -- > > ================================================================== > > Aaron Bono > > Aranya Software Technologies, Inc. > > http://www.aranya.com > > http://codeelixir.com > > ================================================================== > > > > ********************************************************************** > > This message contains confidential information intended only for the use > > of the addressee(s) named above and may contain information that is legally > > privileged. If you are not the addressee, or the person responsible for > > delivering it to the addressee, you are hereby notified that reading, > > disseminating, distributing or copying this message is strictly prohibited. > > If you have received this message by mistake, please immediately notify us > > by replying to the message and delete the original message immediately > > thereafter. > > > > Thank you. > > > > > > FADLD Tag > > ********************************************************************** > > > > > > -- > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > http://codeelixir.com > ================================================================== > -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_64243_23941252.1164233528995 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
If you're not logged in as a superuser, you won't be able to see other user's queries. Try to login as a superuser (ei, postgres or whatever you superuser account is).

I am logging in as postgres when I check these tables.


From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Wednesday, November 22, 2006 1:46 PM

To: Tomeh, Husam
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Monitoring PostgreSQL Process

On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
This indicates that some stats parameters are not enabled in your "postgresql.conf" file. In particular, make sure that "stats_command_string" parameter is turned ON so that pg_stat_activity can report back the actual SQL statement.

Right, as I mentioned, I had stats_command_string turned off but turned it on.  What I expected to find is that the <command string not enabled> would remain until another query was run by that process.  But since these processes are eating up so many CPU cycles, I thought they would show something other than <command string not enabled> pretty quickly.  Since they did not show something new, why are they using so much of the CPU?  Could the be caught in some kind of massive , seemingly endless query?

In fact 30961 and 2830 are still not showing anything new and 30960 and 30306 show Idle most of the time.

I know the application is causing the problem, I am just trying to figure out what part of the application is taking postgres so much.

Thanks,
Aaron


From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono
Sent: Wednesday, November 22, 2006 1:14 PM
To: Tomeh, Husam
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Monitoring PostgreSQL Process

On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
You may query the system view, pg_stat_activity to check out the current SQL statements running. To track down executed SQL statements into a physical log file, you may want to enable statement logging. For more details on that, check out: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html
 
Once you capture your SQL statement, you can run "explain" on it to check out its execution plan.

So I went out to the server and selected on pg_stat_activity and found that stats_command_string had been turned off.  I turned it on and gave postgres a reload command.  Then I selected on pg_stat_activity again and got this:

my_db=# select * from pg_stat_activity where procpid in (30960, 30961, 877, 30306, 2830);
 datid |       datname        | procpid | usesysid | usename  |        current_query         |          query_start          |         backend_start         | client_addr | client_port
-------+----------------------+---------+----------+----------+------------------------------+-------------------------------+-------------------------------+-------------+-------------
 29246 | my_db |   30960 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-20 07:20: 24.068052-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |   30961 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-20 07:20:24.072152-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |     877 |    24114 | usr1 | <IDLE>                       | 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |    2830 |    24114 | usr1 | <command string not enabled> |                               | 2006-11-22 10:45:09.528452-06 | 127.0.0.1   |       xxxxx
 29246 | my_db |   30306 |    24113 | usr2  | <BIND>                       | 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | 127.0.0.1   |       xxxxx
(5 rows)

I cleared out the client ports.

The funny thing is that process ID's 30960, 30961 and 2830 are using a lot of the CPU but according to pg_stat_activity the <command string not enabled> has not been updated.  Doesn't this suggest that those processes are NOT running anything on the database, or at least anything new?

Here is what I get from "ps axuf":

postgres 25635  0.0  0.2 34864 5592 ?        S    Oct01   0:02 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 25637  0.0  0.0 11220  768 ?        S    Oct01   1:07  \_ postgres: logger process
postgres 25639  0.0  0.8 35100 17624 ?       S    Oct01   5:03  \_ postgres: writer process
postgres 25640  0.0  0.0 12220 1568 ?        S    Oct01   0:50  \_ postgres: stats buffer process
postgres 25641  0.0  0.0 11908 1224 ?        S    Oct01   1:03  |   \_ postgres: stats collector process
postgres 30950  2.2  1.1 38728 23000 ?       S    Nov20  74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30951  2.1  1.1 38752 23328 ?       S    Nov20  70:22  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30960 21.3  1.1 39840 24740 ?       S    Nov20 715:38  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30961 17.2  1.1 39496 24356 ?       S    Nov20 578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres 30962  9.5  1.1 39864 24588 ?       R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1(xxx) PARSE
postgres 31068  0.0   0.2 36584 4940 ?        S    Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31069  0.6  0.9 36724 20268 ?       S    Nov20  22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31070  0.0  0.2 35792 4548 ?        S    Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle
postgres 31199  1.8  1.1 39320 23708 ?       S    Nov20  62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres 31359  0.6  1.0 37092 21308 ?       S    Nov20  21:11  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 31361  0.0  0.2 36720 5000 ?        S    Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 31362  1.5  1.0 38132 21384 ?       S    Nov20  53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle
postgres 32030  5.1  1.1 39816 24740 ?       S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1(xxx) idle
postgres   877 14.6  1.1 39812 24676 ?       S    Nov20 481:37  \_ postgres: user1 my_db 127.0.0.1(xxx) idle
postgres  3008  4.9  1.1 39404 24460 ?       S    Nov20 158:17  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction
postgres  3009  1.7  1.1 39012 23804 ?       S    Nov20  56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in transaction
postgres 17186  1.2  1.1 38804 23204 ?       S    Nov20  31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle
postgres 19966  2.7  1.1 38596 23056 ?       S    Nov21  25:08  \_ postgres: user3 my_db 127.0.0.1(xxx) idle
postgres 30306 19.4  1.1 39448 23908 ?       S    07:43  86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle
postgres  2830 15.4  1.0 38992 22068 ?       S    10:45  40:40  \_ postgres: user1 my_db 127.0.0.1(xxx) idle

Anyone have any clue why these processes are eating up so much CPU time?

Thanks,
Aaron

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

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged.  If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited.  If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

                                                                                                                         FADLD Tag
**********************************************************************




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



--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_64243_23941252.1164233528995-- From adey11@gmail.com Thu Nov 23 00:02:19 2006 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-- From postgresql@aranya.com Thu Nov 23 02:33:52 2006 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--