Monitoring PostgreSQL Process

------=_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.<br><br>Is there a way to monitor the SQL being run for a specific connection/process?
<br><br>We are using PostgreSQL 8.1.5 on CentOS Linux.<br><br>Thanks,<br>Aaron<br clear="all"><br>-- <br> ============================================================ ======<br>   Aaron Bono<br>   Aranya Software Technologies, Inc.
<br>   http://www.aranya.com<br>   http://codeelixir.com<br> ============================================================ ======

------=_Part_54075_20580278.1164174588428--
Aaron Bono [ Mi, 22 November 2006 06:49 ] [ ID #1544257 ]

Re: Monitoring PostgreSQL Process

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-log ging.html

Once you capture your SQL statement, you can run "explain" on it to
check out its execution plan.

--
Husam
http://firstdba.googlepages.com

________________________________

From: pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of Aaron Bono
Sent: Tuesday, November 21, 2006 9:50 PM
To: pgsql-admin [at] 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

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

************************************************************ **********
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
FADLD Tag
************************************************************ **********

------_=_NextPart_001_01C70E60.53A71BF4
Content-Type: text/html;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"=
>
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D971385217-22112006><FONT face=3D"Trebuchet MS" size=3D=
2>You may
query the system view, pg_stat_activity to check out the current SQL stat=
ements
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: <A=

href=3D"http://www.postgresql.org/docs/8.1/static/runtime-co nfig-logging.=
html">http://www.postgresql.org/docs/8.1/static/runtime-conf ig-logging.ht=
ml</A></FONT></SPAN></DIV>
<DIV><SPAN class=3D971385217-22112006><FONT face=3D"Trebuchet MS"
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D971385217-22112006><FONT face=3D"Trebuchet MS" size=3D=
2>Once you
capture your SQL statement, you can run "explain" on it to check out its =

execution plan.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3D"Trebuchet MS" size=3D2>--</FONT=
></DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial size=3D2><FONT face=3D"Tre=
buchet MS"> 
Husam</FONT></FONT></DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial><FONT size=3D2><SPAN
class=3D971385217-22112006><FONT face=3D"Trebuchet MS">  <A
href=3D"http://firstdba.googlepages.com">http://firstdba.goo glepages.com<=
/A></FONT></SPAN></FONT></FONT></DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial><FONT face=3D"Trebuchet MS=
" size=3D2><SPAN
class=3D971385217-22112006></SPAN></FONT></FONT> </DIV>
<DIV dir=3Dltr align=3Dleft>
<HR tabIndex=3D-1>
<FONT face=3DTahoma size=3D2><B>From:</B> pgsql-admin-owner [at] postgresql.or=
g
[mailto:pgsql-admin-owner [at] postgresql.org] <B>On Behalf Of </B>Aaron
Bono<BR><B>Sent:</B> Tuesday, November 21, 2006 9:50 PM<BR><B>To:</B>
pgsql-admin [at] postgresql.org<BR><B>Subject:</B> [ADMIN] Monitoring PostgreS=
QL
Process<BR></FONT><BR></DIV>
<DIV></DIV>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 wh=
at
these processes are doing that is taking so much CPU time.<BR><BR>Is ther=
e a way
to monitor the SQL being run for a specific connection/process? <BR><BR>W=
e are
using PostgreSQL 8.1.5 on CentOS Linux.<BR><BR>Thanks,<BR>Aaron<BR
clear=3Dall><BR>--
<BR> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<BR>  
Aaron Bono<BR>   Aranya Software Technologies, Inc. <BR> &=
nbsp;
<A href=3D"http://www.aranya.com">http://www.aranya.com</A><BR> &nbs=
p; <A
href=3D"http://codeelixir.com">http://codeelixir.com</A><BR>=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

<P> ************************************************************ **********=
<BR>This
message contains confidential information intended only for the use of th=
e
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.</P>
<P>Thank you.</P>
<P><FONT
size=3D1>          &nbs=
p;            =
;            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;            =
;            =
             =

FADLD
Tag</FONT><BR> ***********************************************************=
***********</P>
</BODY></HTML>

------_=_NextPart_001_01C70E60.53A71BF4--
htomeh [ Mi, 22 November 2006 19:02 ] [ ID #1544258 ]

Re: Monitoring PostgreSQL Process

------=_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 <htomeh [at] 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-log ging.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 [at] postgresql.org [mailto:
> pgsql-admin-owner [at] postgresql.org] *On Behalf Of *Aaron Bono
> *Sent:* Tuesday, November 21, 2006 9:50 PM
> *To:* pgsql-admin [at] 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, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">




<div>
<div><span><font face="Trebuchet MS" size="2">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: <a href="http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://www.postgresql.org/docs/8.1/static/runtime-config-log ging.html</a></font></span></div>
<div><span><font face="Trebuchet MS" size="2"></font></span> </div>
<div><span><font face="Trebuchet MS" size="2">Once you
capture your SQL statement, you can run "explain" on it to check out its
execution plan.</font></span></div></div></blockquote><div><br>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.<br></div>
<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div><div dir="ltr" align="left"><font face="Trebuchet MS" size="2">--</font></div>
<div dir="ltr" align="left"><font face="Arial" size="2"><font face="Trebuchet MS"> 
Husam</font></font></div>
<div dir="ltr" align="left"><font face="Arial"><font size="2"><span><font face="Trebuchet MS">  <a href="http://firstdba.googlepages.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://firstdba.googlepages.com
</a></font></span></font></font></div>
<div dir="ltr" align="left"><font face="Arial"><font face="Trebuchet MS" size="2"><span></span></font></font> </div>
<div dir="ltr" align="left">
<hr>
<font face="Tahoma" size="2"><b>From:</b> pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] <b>On Behalf Of </b>Aaron
Bono<br><b>Sent:</b> Tuesday, November 21, 2006 9:50 PM<br><b>To:</b>
pgsql-admin [at] postgresql.org<br><b>Subject:</b> [ADMIN] Monitoring PostgreSQL
Process<br></font><br></div><div><span class="e" id="q_10f10d5355ea9e8c_1">
<div></div>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.<br><br>Is there a way
to monitor the SQL being run for a specific connection/process? <br><br>We are
using PostgreSQL 8.1.5 on CentOS Linux.<br><br>Thanks,<br>Aaron<br clear="all"><br>--
<br> ============================================================ ======<br>  
Aaron Bono<br>   Aranya Software Technologies, Inc. <br>  
http://www.aranya.com<br>   <a href="http://codeelixir.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://codeelixir.com</a><br> ============================================================ ======

</span></div><p> ************************************************************ **********<br>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.</p>
<p>Thank you.</p>
<p><font size="1">                                                                                                                         
FADLD
Tag</font><br> ************************************************************ **********</p>
</div>

</blockquote></div><br><br clear="all"><br>-- <br> ============================================================ ======<br>   Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   <a href="http://www.aranya.com">http://www.aranya.com
</a><br>   http://codeelixir.com<br> ============================================================ ======

------=_Part_62554_2857132.1164221182790--
Aaron Bono [ Mi, 22 November 2006 19:46 ] [ ID #1544259 ]

Re: Monitoring PostgreSQL Process

------=_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 <htomeh [at] 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 [at] gmail.com [mailto:aaron.bono [at] gmail.com] *On Behalf Of *Aaron
> Bono
> *Sent:* Wednesday, November 22, 2006 1:46 PM
> *To:* Tomeh, Husam
> *Cc:* pgsql-admin [at] postgresql.org
> *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process
>
> On 11/22/06, Tomeh, Husam <htomeh [at] 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 [at] gmail.com [mailto:aaron.bono [at] gmail.com] *On Behalf Of
> > *Aaron Bono
> > *Sent:* Wednesday, November 22, 2006 1:14 PM
> > *To:* Tomeh, Husam
> > *Cc:* pgsql-admin [at] postgresql.org
> > *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process
> >
> > On 11/22/06, Tomeh, Husam <htomeh [at] 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-log ging.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
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/22/06, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">




<div>
<div><span><font face="Trebuchet MS" size="2">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).</font></span></div></div></blockquote><div><br>I am logging in as postgres when I check these tables. <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div dir="ltr" align="left" lang="en-us"><hr>
<font face="Tahoma" size="2"><span class="q"><b>From:</b> aaron.bono [at] gmail.com
[mailto:aaron.bono [at] gmail.com] <b>On Behalf Of </b>Aaron Bono<br></span><b>Sent:</b>
Wednesday, November 22, 2006 1:46 PM</font><div><span class="e" id="q_10f11a8cbcc1d517_3"><font face="Tahoma" size="2"><br><b>To:</b> Tomeh, Husam<br><b>Cc:</b>
pgsql-admin [at] postgresql.org<br><b>Subject:</b> Re: [ADMIN] Monitoring PostgreSQL
Process<br></font></span></div><br></div><div><span class="e" id="q_10f11a8cbcc1d517_5">
<div></div>On 11/22/06, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com> wrote:
<div><span class="gmail_quote"></span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div><span><font face="Trebuchet MS" size="2">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.
</font></span></div></div></blockquote>
<div><br>Right, as I mentioned, I had stats_command_string turned off but turned
it on.  What I expected to find is that the <span><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
</span><span><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?<br><br>In fact 30961 and 2830 are still not showing
anything new and 30960 and 30306 show Idle most of the time. <br><br>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.<br><br>Thanks,<br>Aaron<br></span></div><br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left" lang="en-us">
<hr>
<font face="Tahoma" size="2"><b>From:</b> aaron.bono [at] gmail.com
[mailto:aaron.bono [at] gmail.com]
<b>On Behalf Of </b>Aaron Bono<br><b>Sent:</b> Wednesday, November 22, 2006
1:14 PM<br><b>To:</b> Tomeh, Husam<br><b>Cc:</b> pgsql-admin [at] postgresql.org<br><b>Subject:</b> Re: [ADMIN]
Monitoring PostgreSQL Process<br></font><br></div>
<div><span>
<div></div>On 11/22/06, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com>
wrote:
<div><span class="gmail_quote"></span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div><span><font face="Trebuchet MS" size="2">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: <a href="http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)"> http://www.postgresql.org/docs/8.1/static/runtime-config-log ging.html
</a></font></span></div>
<div><span><font face="Trebuchet MS" size="2"></font></span> </div>
<div><span><font face="Trebuchet MS" size="2">Once you capture your SQL
statement, you can run "explain" on it to check out its execution
plan.</font></span></div></div></blockquote></div><br>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:
<br><br>my_db=# select * from pg_stat_activity where procpid in (30960, 30961,
877, 30306, 2830);<br> datid |      
datname        | procpid | usesysid |
usename  |       
current_query        
|         
query_start         
|        
backend_start         | client_addr |
client_port
<br> -------+----------------------+---------+----------+-------- --+------------------------------+-------------------------- -----+-------------------------------+-------------+-------- -----<br> 29246
| my_db |   30960 |    24114 | usr1 | <command
string not enabled>
|                              
| 2006-11-20 07:20: 24.068052-06 | 127.0.0.1   |      
xxxxx<br> 29246 | my_db |   30961 |    24114 |
usr1 | <command string not enabled>
|                              
| 2006-11-20 07:20:24.072152-06 | 127.0.0.1   |      
xxxxx<br> 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<br> 29246 | my_db |    2830 |   
24114 | usr1 | <command string not enabled>
|                              
| 2006-11-22 10:45:09.528452-06 | 127.0.0.1   |      
xxxxx <br> 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<br>(5 rows)<br><br clear="all">I cleared out the client ports.<br><br>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? <br><br>Here is
what I get from "ps axuf":<br><br>postgres 25635  0.0  0.2 34864
5592 ?        S   
Oct01   0:02 /usr/bin/postmaster -p 5432 -D
/var/lib/pgsql/data<br>postgres 25637  0.0  0.0 11220  768
?        S   
Oct01   1:07  \_ postgres: logger process <br>postgres
25639  0.0  0.8 35100 17624 ?      
S    Oct01   5:03  \_ postgres: writer
process<br>postgres 25640  0.0  0.0 12220 1568
?        S   
Oct01   0:50  \_ postgres: stats buffer process<br>postgres
25641  0.0  0.0 11908 1224
?        S   
Oct01   1:03  |   \_ postgres: stats collector
process<br>postgres 30950  2.2  1.1 38728 23000
?       S    Nov20 
74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle<br>postgres
30951  2.1  1.1 38752 23328 ?      
S    Nov20  70:22  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<br>postgres 30960 21.3  1.1 39840 24740
?       S    Nov20 715:38 
\_ postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres 30961 17.2  1.1
39496 24356 ?       S    Nov20
578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres
30962  9.5  1.1 39864 24588 ?      
R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1(xxx)
PARSE<br>postgres 31068  0.0   0.2 36584 4940
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx)
idle<br>postgres 31069  0.6  0.9 36724 20268
?       S    Nov20 
22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle<br>postgres
31070  0.0  0.2 35792 4548
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx)
idle<br>postgres 31199  1.8  1.1 39320 23708
?       S    Nov20 
62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle<br>postgres
31359  0.6  1.0 37092 21308 ?      
S    Nov20  21:11  \_ postgres: user2 my_test_db
127.0.0.1(xxx) idle<br>postgres 31361  0.0  0.2 36720 5000
?        S   
Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx)
idle<br>postgres 31362  1.5  1.0 38132 21384
?       S    Nov20 
53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle<br>postgres
32030  5.1  1.1 39816 24740 ?      
S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1(xxx)
idle<br>postgres   877 14.6  1.1 39812 24676
?       S    Nov20 481:37 
\_ postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres  3008 
4.9  1.1 39404 24460 ?      
S    Nov20 158:17  \_ postgres: user2 my_db 127.0.0.1(xxx)
idle in transaction<br>postgres  3009  1.7  1.1 39012 23804
?       S    Nov20 
56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<br>postgres 17186  1.2  1.1 38804 23204
?       S    Nov20 
31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle<br>postgres 19966  2.7  1.1
38596 23056 ?       S   
Nov21  25:08  \_ postgres: user3 my_db 127.0.0.1(xxx)
idle<br>postgres 30306 19.4  1.1 39448 23908
?       S    07:43 
86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle<br>postgres  2830 15.4  1.0
38992 22068 ?       S   
10:45  40:40  \_ postgres: user1 my_db 127.0.0.1(xxx)
idle<br><br>Anyone have any clue why these processes are eating up so much CPU
time?<br><br>Thanks,<br>Aaron<br><br>--
<br> ============================================================ ======<br>  
Aaron Bono<br>   Aranya Software Technologies, Inc.<br>  
http://www.aranya.com<br>   <a href="http://codeelixir.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://codeelixir.com</a><br> ============================================================ ======
</span></div><span>
<p> ************************************************************ **********<br>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.</p>
<p>Thank you.</p>
<p><font size="1">                                                                                                                         
FADLD
Tag</font><br> ************************************************************ **********</p></span></div></blockquote></div><br><br clear="all"><br>--
<br> ============================================================ ======<br>  
Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   http://www.aranya.com <br>   <a href="http://codeelixir.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://codeelixir.com</a><br> ============================================================ ======
</span></div></div>

</blockquote></div><br><br clear="all"><br>-- <br> ============================================================ ======<br>   Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   <a href="http://www.aranya.com">http://www.aranya.com
</a><br>   http://codeelixir.com<br> ============================================================ ======

------=_Part_64243_23941252.1164233528995--
Aaron Bono [ Mi, 22 November 2006 23:12 ] [ ID #1544260 ]

Re: Monitoring PostgreSQL Process

------=_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 <htomeh [at] 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 [at] gmail.com [mailto:aaron.bono [at] gmail.com] *On Behalf Of *Aaron
> Bono
> *Sent:* Wednesday, November 22, 2006 1:14 PM
> *To:* Tomeh, Husam
> *Cc:* pgsql-admin [at] postgresql.org
> *Subject:* Re: [ADMIN] Monitoring PostgreSQL Process
>
> On 11/22/06, Tomeh, Husam <htomeh [at] 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-log ging.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
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/22/06, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">




<div>
<div><span><font face="Trebuchet MS" size="2">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.
</font></span></div></div></blockquote><div><br>Right, as I mentioned, I had stats_command_string turned off but turned it on.  What I expected to find is that the <span class="e" id="q_10f1191549e0624e_1"><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 </span><span class="e" id="q_10f1191549e0624e_1">
<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?<br><br>In fact 30961 and 2830 are still not showing anything new and 30960 and 30306 show Idle most of the time.
<br><br>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.<br><br>Thanks,<br>Aaron<br></span></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div dir="ltr" align="left" lang="en-us"><hr>
<font face="Tahoma" size="2"><b>From:</b> aaron.bono [at] gmail.com
[mailto:aaron.bono [at] gmail.com] <b>On Behalf Of </b>Aaron Bono<br><b>Sent:</b>
Wednesday, November 22, 2006 1:14 PM<br><b>To:</b> Tomeh, Husam<br><b>Cc:</b>
pgsql-admin [at] postgresql.org<br><b>Subject:</b> Re: [ADMIN] Monitoring PostgreSQL
Process<br></font><br></div><div><span class="e" id="q_10f1191549e0624e_1">
<div></div>On 11/22/06, <b class="gmail_sendername">Tomeh, Husam</b> <htomeh [at] firstam.com> wrote:
<div><span class="gmail_quote"></span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div><span><font face="Trebuchet MS" size="2">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: <a href="http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)"> http://www.postgresql.org/docs/8.1/static/runtime-config-log ging.html
</a></font></span></div>
<div><span><font face="Trebuchet MS" size="2"></font></span> </div>
<div><span><font face="Trebuchet MS" size="2">Once you capture your SQL
statement, you can run "explain" on it to check out its execution
plan.</font></span></div></div></blockquote></div><br>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: <br><br>my_db=# select *
from pg_stat_activity where procpid in (30960, 30961, 877, 30306,
2830);<br> datid |      
datname        | procpid | usesysid |
usename  |       
current_query        
|         
query_start         
|        
backend_start         | client_addr |
client_port
<br> -------+----------------------+---------+----------+-------- --+------------------------------+-------------------------- -----+-------------------------------+-------------+-------- -----<br> 29246
| my_db |   30960 |    24114 | usr1 | <command
string not enabled>
|                              
| 2006-11-20 07:20: 24.068052-06 | 127.0.0.1  
|       xxxxx<br> 29246 | my_db |  
30961 |    24114 | usr1 | <command string not enabled>
|                              
| 2006-11-20 07:20:24.072152-06 | 127.0.0.1  
|       xxxxx<br> 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<br> 29246 | my_db
|    2830 |    24114 | usr1 | <command string
not enabled>
|                              
| 2006-11-22 10:45:09.528452-06 | 127.0.0.1  
|       xxxxx <br> 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<br>(5 rows)<br><br clear="all">I
cleared out the client ports.<br><br>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? <br><br>Here is what I get from "ps axuf":<br><br>postgres
25635  0.0  0.2 34864 5592 ?       
S    Oct01   0:02 /usr/bin/postmaster -p 5432 -D
/var/lib/pgsql/data<br>postgres 25637  0.0  0.0 11220  768
?        S   
Oct01   1:07  \_ postgres: logger process <br>postgres
25639  0.0  0.8 35100 17624 ?      
S    Oct01   5:03  \_ postgres: writer
process<br>postgres 25640  0.0  0.0 12220 1568
?        S   
Oct01   0:50  \_ postgres: stats buffer process<br>postgres
25641  0.0  0.0 11908 1224 ?       
S    Oct01   1:03  |   \_ postgres:
stats collector process<br>postgres 30950  2.2  1.1 38728 23000
?       S    Nov20 
74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle<br>postgres 30951 
2.1  1.1 38752 23328 ?      
S    Nov20  70:22  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<br>postgres 30960 21.3  1.1 39840 24740
?       S    Nov20 715:38  \_
postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres 30961 17.2  1.1 39496
24356 ?       S    Nov20
578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres
30962  9.5  1.1 39864 24588 ?      
R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1(xxx)
PARSE<br>postgres 31068  0.0   0.2 36584 4940
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx)
idle<br>postgres 31069  0.6  0.9 36724 20268
?       S    Nov20 
22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle<br>postgres
31070  0.0  0.2 35792 4548 ?       
S    Nov20   0:00  \_ postgres: user1 my_test_db
127.0.0.1(xxx) idle<br>postgres 31199  1.8  1.1 39320 23708
?       S    Nov20 
62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle<br>postgres 31359 
0.6  1.0 37092 21308 ?      
S    Nov20  21:11  \_ postgres: user2 my_test_db
127.0.0.1(xxx) idle<br>postgres 31361  0.0  0.2 36720 5000
?        S   
Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx)
idle<br>postgres 31362  1.5  1.0 38132 21384
?       S    Nov20 
53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle<br>postgres
32030  5.1  1.1 39816 24740 ?      
S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1(xxx)
idle<br>postgres   877 14.6  1.1 39812 24676
?       S    Nov20 481:37  \_
postgres: user1 my_db 127.0.0.1(xxx) idle<br>postgres  3008  4.9 
1.1 39404 24460 ?       S    Nov20
158:17  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<br>postgres  3009  1.7  1.1 39012 23804
?       S    Nov20 
56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<br>postgres 17186  1.2  1.1 38804 23204
?       S    Nov20 
31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle<br>postgres
19966  2.7  1.1 38596 23056 ?      
S    Nov21  25:08  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<br>postgres 30306 19.4  1.1 39448 23908
?       S    07:43 
86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle<br>postgres  2830
15.4  1.0 38992 22068 ?      
S    10:45  40:40  \_ postgres: user1 my_db
127.0.0.1(xxx) idle<br><br>Anyone have any clue why these processes are eating
up so much CPU time?<br><br>Thanks,<br>Aaron<br><br>--
<br> ============================================================ ======<br>  
Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   http://www.aranya.com<br>   <a href="http://codeelixir.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://codeelixir.com</a><br> ============================================================ ======

</span></div><span class="q"><p> ************************************************************ **********<br>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.</p>
<p>Thank you.</p>
<p><font size="1">                                                                                                                         
FADLD
Tag</font><br> ************************************************************ **********</p>
</span></div>

</blockquote></div><br><br clear="all"><br>-- <br> ============================================================ ======<br>   Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   <a href="http://www.aranya.com">http://www.aranya.com
</a><br>   http://codeelixir.com<br> ============================================================ ======

------=_Part_64103_18347219.1164231976572--
Aaron Bono [ Mi, 22 November 2006 22:46 ] [ ID #1544261 ]

Re: Monitoring PostgreSQL Process

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).

Sincerely,

--
Husam


________________________________

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


On 11/22/06, Tomeh, Husam <htomeh [at] 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 [at] gmail.com [mailto:aaron.bono [at] gmail.com] On
Behalf Of Aaron Bono
Sent: Wednesday, November 22, 2006 1:14 PM
To: Tomeh, Husam
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] Monitoring PostgreSQL Process
=09
=09
=09
On 11/22/06, Tomeh, Husam <htomeh [at] firstam.com> wrote:
=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-log ging.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:
=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
=09
-------+----------------------+---------+----------+-------- --+---------
---------------------+-------------------------------+------ ------------
-------------+-------------+-------------
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)
=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 <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?
=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
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
--
=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
=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
************************************************************ **********




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

------_=_NextPart_001_01C70E80.9FC1D88E
Content-Type: text/html;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D865115121-22112006><FONT face=3D"Trebuchet MS" =
size=3D2>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).</FONT></SPAN></DIV>
<DIV><SPAN class=3D865115121-22112006><FONT face=3D"Trebuchet MS"
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D865115121-22112006><FONT face=3D"Trebuchet MS"
size=3D2>Sincerely,</FONT></SPAN></DIV>
<DIV><FONT face=3D"Trebuchet MS" size=3D2></FONT> </DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3D"Trebuchet MS" =
size=3D2>--</FONT></DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial size=3D2><FONT =
face=3D"Trebuchet MS"> 
Husam</FONT> </FONT></DIV>
<DIV> </DIV><BR>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr align=3Dleft>
<HR tabIndex=3D-1>
<FONT face=3DTahoma size=3D2><B>From:</B> aaron.bono [at] gmail.com
[mailto:aaron.bono [at] gmail.com] <B>On Behalf Of </B>Aaron =
Bono<BR><B>Sent:</B>
Wednesday, November 22, 2006 1:46 PM<BR><B>To:</B> Tomeh, =
Husam<BR><B>Cc:</B>
pgsql-admin [at] postgresql.org<BR><B>Subject:</B> Re: [ADMIN] Monitoring =
PostgreSQL
Process<BR></FONT><BR></DIV>
<DIV></DIV>On 11/22/06, <B class=3Dgmail_sendername>Tomeh, Husam</B> =
<<A
href=3D"mailto:htomeh [at] firstam.com">htomeh [at] firstam.com</A>> wrote:
<DIV><SPAN class=3Dgmail_quote></SPAN>
<BLOCKQUOTE class=3Dgmail_quote
style=3D"PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: =
rgb(204,204,204) 1px solid">
<DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2>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.
</FONT></SPAN></DIV></DIV></BLOCKQUOTE>
<DIV><BR>Right, as I mentioned, I had stats_command_string turned off =
but turned
it on.  What I expected to find is that the <SPAN class=3De
id=3Dq_10f1191549e0624e_1><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
</SPAN><SPAN class=3De id=3Dq_10f1191549e0624e_1><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?<BR><BR>In fact 30961 and 2830 are still not =
showing
anything new and 30960 and 30306 show Idle most of the time. <BR><BR>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.<BR><BR>Thanks,<BR>Aaron<BR></SPAN></DIV><BR>
<BLOCKQUOTE class=3Dgmail_quote
style=3D"PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: =
rgb(204,204,204) 1px solid">
<DIV>
<DIV lang=3Den-us dir=3Dltr align=3Dleft>
<HR>
<FONT face=3DTahoma size=3D2><B>From:</B> <A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"mailto:aaron.bono [at] gmail.com" =
target=3D_blank>aaron.bono [at] gmail.com</A>
[mailto:<A onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"mailto:aaron.bono [at] gmail.com" =
target=3D_blank>aaron.bono [at] gmail.com</A>]
<B>On Behalf Of </B>Aaron Bono<BR><B>Sent:</B> Wednesday, November 22, =
2006
1:14 PM<BR><B>To:</B> Tomeh, Husam<BR><B>Cc:</B> <A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"mailto:pgsql-admin [at] postgresql.org"
target=3D_blank>pgsql-admin [at] postgresql.org</A><BR><B>Subject:</B> Re: =
[ADMIN]
Monitoring PostgreSQL Process<BR></FONT><BR></DIV>
<DIV><SPAN class=3De id=3Dq_10f1191549e0624e_1>
<DIV></DIV>On 11/22/06, <B class=3Dgmail_sendername>Tomeh, Husam</B> =
<<A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"mailto:htomeh [at] firstam.com" =
target=3D_blank>htomeh [at] firstam.com</A>>
wrote:
<DIV><SPAN class=3Dgmail_quote></SPAN>
<BLOCKQUOTE class=3Dgmail_quote
style=3D"PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: =
rgb(204,204,204) 1px solid">
<DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2>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: <A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
=
href=3D"http://www.postgresql.org/docs/8.1/static/runtime-co nfig-logging.=
html"
=
target=3D_blank>http://www.postgresql.org/docs/8.1/static/ru ntime-config-=
logging.html
</A></FONT></SPAN></DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2>Once you capture =
your SQL
statement, you can run "explain" on it to check out its execution
plan.</FONT></SPAN></DIV></DIV></BLOCKQUOTE></DIV><BR>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: =

<BR><BR>my_db=3D# select * from pg_stat_activity where procpid in =
(30960, 30961,
877, 30306, 2830);<BR> datid =
|      
datname        | procpid | usesysid =
|
usename  |       
current_query        
|         
query_start         
|        
backend_start         | =
client_addr |
client_port
=
<BR> -------+----------------------+---------+----------+-------- --+------=
------------------------+-------------------------------+--- -------------=
---------------+-------------+-------------<BR> 29246
| my_db |   30960 |    24114 | usr1 | =
<command
string not enabled>
=
|            =
            &=
nbsp;     
| 2006-11-20 07:20: 24.068052-06 | <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A>   =
|      
xxxxx<BR> 29246 | my_db |   30961 |    =
24114 |
usr1 | <command string not enabled>
=
|            =
            &=
nbsp;     
| 2006-11-20 07:20:24.072152-06 | <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A>   =
|      
xxxxx<BR> 29246 | my_db |     877 =
|   
24114 | usr1 |
=
<IDLE>          &=
nbsp;           
| 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A>   =
|      
xxxxx<BR> 29246 | my_db |    2830 =
|   
24114 | usr1 | <command string not enabled>
=
|            =
            &=
nbsp;     
| 2006-11-22 10:45:09.528452-06 | <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A>   =
|      
xxxxx <BR> 29246 | my_db |   30306 |    =
24113 |
usr2  |
=
<BIND>          &=
nbsp;           
| 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A>   =
|      
xxxxx<BR>(5 rows)<BR><BR clear=3Dall>I cleared out the client =
ports.<BR><BR>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? =
<BR><BR>Here is
what I get from "ps axuf":<BR><BR>postgres 25635  0.0  0.2 =
34864
5592 ?        S   
Oct01   0:02 /usr/bin/postmaster -p 5432 -D
/var/lib/pgsql/data<BR>postgres 25637  0.0  0.0 11220  =
768
?        S   
Oct01   1:07  \_ postgres: logger process <BR>postgres
25639  0.0  0.8 35100 17624 =
?      
S    Oct01   5:03  \_ postgres: writer
process<BR>postgres 25640  0.0  0.0 12220 1568
?        S   
Oct01   0:50  \_ postgres: stats buffer =
process<BR>postgres
25641  0.0  0.0 11908 1224
?        S   
Oct01   1:03  |   \_ postgres: stats =
collector
process<BR>postgres 30950  2.2  1.1 38728 23000
?       S    Nov20 
74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle<BR>postgres
30951  2.1  1.1 38752 23328 =
?      
S    Nov20  70:22  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<BR>postgres 30960 21.3  1.1 39840 24740
?       S    Nov20 =
715:38 
\_ postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres 30961 =
17.2  1.1
39496 24356 ?       S    =
Nov20
578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres
30962  9.5  1.1 39864 24588 =
?      
R    Nov20 320:22  \_ postgres: user1 my_db =
127.0.0.1(xxx)
PARSE<BR>postgres 31068  0.0   0.2 36584 4940
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db =
127.0.0.1(xxx)
idle<BR>postgres 31069  0.6  0.9 36724 20268
?       S    Nov20 
22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) =
idle<BR>postgres
31070  0.0  0.2 35792 4548
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db =
127.0.0.1(xxx)
idle<BR>postgres 31199  1.8  1.1 39320 23708
?       S    Nov20 
62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle<BR>postgres
31359  0.6  1.0 37092 21308 =
?      
S    Nov20  21:11  \_ postgres: user2 =
my_test_db
127.0.0.1(xxx) idle<BR>postgres 31361  0.0  0.2 36720 5000
?        S   
Nov20   0:00  \_ postgres: user2 my_test_db =
127.0.0.1(xxx)
idle<BR>postgres 31362  1.5  1.0 38132 21384
?       S    Nov20 
53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) =
idle<BR>postgres
32030  5.1  1.1 39816 24740 =
?      
S    Nov20 171:42  \_ postgres: user2 my_db =
127.0.0.1(xxx)
idle<BR>postgres   877 14.6  1.1 39812 24676
?       S    Nov20 =
481:37 
\_ postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres  =
3008 
4.9  1.1 39404 24460 ?      
S    Nov20 158:17  \_ postgres: user2 my_db =
127.0.0.1(xxx)
idle in transaction<BR>postgres  3009  1.7  1.1 39012 =
23804
?       S    Nov20 
56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<BR>postgres 17186  1.2  1.1 38804 23204
?       S    Nov20 
31:19  \_ postgres: user3 my_db <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A> (xxx) idle<BR>postgres 19966  =
2.7  1.1
38596 23056 ?       S   
Nov21  25:08  \_ postgres: user3 my_db 127.0.0.1(xxx)
idle<BR>postgres 30306 19.4  1.1 39448 23908
?       S    07:43 
86:45  \_ postgres: user2 my_db <A
onclick=3D"return top.js.OpenExtLink(window,event,this)" =
href=3D"http://127.0.0.1"
target=3D_blank>127.0.0.1</A> (xxx) idle<BR>postgres  2830 =
15.4  1.0
38992 22068 ?       S   
10:45  40:40  \_ postgres: user1 my_db 127.0.0.1(xxx)
idle<BR><BR>Anyone have any clue why these processes are eating up so =
much CPU
time?<BR><BR>Thanks,<BR>Aaron<BR><BR>--
=
<BR> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<BR>  
Aaron Bono<BR>   Aranya Software Technologies, =
Inc.<BR>  
<A onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"http://www.aranya.com"
target=3D_blank>http://www.aranya.com</A><BR>   <A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"http://codeelixir.com"
=
target=3D_blank>http://codeelixir.com</A><BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
</SPAN></DIV><SPAN class=3Dq>
=
<P> ************************************************************ **********=
<BR>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.</P>
<P>Thank you.</P>
<P><FONT
=
size=3D1>          &nbs=
p;            =
;            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;            =
;            =
             =

FADLD
=
Tag</FONT><BR> ***********************************************************=
***********</P></SPAN></DIV></BLOCKQUOTE></DIV><BR><BR
clear=3Dall><BR>--
<BR> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<BR>  
Aaron Bono<BR>   Aranya Software Technologies, =
Inc.<BR>   <A
href=3D"http://www.aranya.com">http://www.aranya.com =
</A><BR>   <A
href=3D"http://codeelixir.com">http://codeelixir.com</A><BR>=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
</BODY></HTML>

------_=_NextPart_001_01C70E80.9FC1D88E--
htomeh [ Mi, 22 November 2006 22:53 ] [ ID #1544262 ]

Re: Monitoring PostgreSQL Process

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.

Sincerely,

--
Husam


________________________________

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


On 11/22/06, Tomeh, Husam <htomeh [at] firstam.com> wrote:

=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-log ging.html
=09
=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:

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

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

************************************************************ **********
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
FADLD Tag
************************************************************ **********

------_=_NextPart_001_01C70E7D.0DE556B5
Content-Type: text/html;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"=
>
<META content=3D"MSHTML 6.00.5730.11" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D130012321-22112006><FONT face=3D"Trebuchet MS" size=3D=
2>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 stateme=
nt.
</FONT></SPAN></DIV>
<DIV><SPAN class=3D130012321-22112006><FONT face=3D"Trebuchet MS"
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D130012321-22112006><FONT face=3D"Trebuchet MS"
size=3D2>Sincerely,</FONT></SPAN></DIV>
<DIV><SPAN class=3D130012321-22112006><FONT face=3D"Trebuchet MS"
size=3D2></FONT></SPAN> </DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3D"Trebuchet MS" size=3D2>--</FONT=
></DIV>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial size=3D2><FONT face=3D"Tre=
buchet MS"> 
Husam</FONT> </FONT></DIV>
<DIV> </DIV><BR>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr align=3Dleft>
<HR tabIndex=3D-1>
<FONT face=3DTahoma size=3D2><B>From:</B> aaron.bono [at] gmail.com
[mailto:aaron.bono [at] gmail.com] <B>On Behalf Of </B>Aaron Bono<BR><B>Sent:<=
/B>
Wednesday, November 22, 2006 1:14 PM<BR><B>To:</B> Tomeh, Husam<BR><B>Cc:=
</B>
pgsql-admin [at] postgresql.org<BR><B>Subject:</B> Re: [ADMIN] Monitoring Post=
greSQL
Process<BR></FONT><BR></DIV>
<DIV></DIV>On 11/22/06, <B class=3Dgmail_sendername>Tomeh, Husam</B> <=
<A
href=3D"mailto:htomeh [at] firstam.com">htomeh [at] firstam.com</A>> wrote:
<DIV><SPAN class=3Dgmail_quote></SPAN>
<BLOCKQUOTE class=3Dgmail_quote
style=3D"PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(2=
04,204,204) 1px solid">
<DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2>You may query the sys=
tem 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 t=
o enable
statement logging. For more details on that, check out: <A
onclick=3D"return top.js.OpenExtLink(window,event,this)"
href=3D"http://www.postgresql.org/docs/8.1/static/runtime-co nfig-logg=
ing.html"
target=3D_blank>http://www.postgresql.org/docs/8.1/static/ru ntime-con=
fig-logging.html</A></FONT></SPAN></DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2></FONT></SPAN> <=
/DIV>
<DIV><SPAN><FONT face=3D"Trebuchet MS" size=3D2>Once you capture your=
SQL
statement, you can run "explain" on it to check out its execution
plan.</FONT></SPAN></DIV></DIV></BLOCKQUOTE></DIV><BR>So I went out t=
o the
server and selected on pg_stat_activity and found that stats_command_stri=
ng 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: <BR><BR>my_db=3D#=
select *
from pg_stat_activity where procpid in (30960, 30961, 877, 30306,
2830);<BR> datid |      
datname        | procpid | usesysid | =

usename  |       
current_query        
|         
query_start         
|        
backend_start         | client_ad=
dr |
client_port
<BR> -------+----------------------+---------+----------+-------- --+------=
------------------------+-------------------------------+--- -------------=
---------------+-------------+-------------<BR> 29246
| my_db |   30960 |    24114 | usr1 | <comman=
d
string not enabled>
|            =
            &=
nbsp;     
| 2006-11-20 07:20: 24.068052-06 | <A
href=3D"http://127.0.0.1">127.0.0.1</A>  
|       xxxxx<BR> 29246 | my_db | =
; 
30961 |    24114 | usr1 | <command string not enabled&g=
t;
|            =
            &=
nbsp;     
| 2006-11-20 07:20:24.072152-06 | <A
href=3D"http://127.0.0.1">127.0.0.1</A>  
|       xxxxx<BR> 29246 | my_db
|     877 |    24114 | usr1 |
<IDLE>          &=
nbsp;           
| 2006-11-22 15:04:42.230017-06 | 2006-11-20 08:20:35.765786-06 | <A
href=3D"http://127.0.0.1">127.0.0.1</A>  
|       xxxxx<BR> 29246 | my_db
|    2830 |    24114 | usr1 | <command s=
tring
not enabled>
|            =
            &=
nbsp;     
| 2006-11-22 10:45:09.528452-06 | <A
href=3D"http://127.0.0.1">127.0.0.1</A>  
|       xxxxx <BR> 29246 | my_db
|   30306 |    24113 | usr2  |
<BIND>          &=
nbsp;           
| 2006-11-22 15:04:42.408911-06 | 2006-11-22 07:43:08.825445-06 | <A
href=3D"http://127.0.0.1">127.0.0.1</A>  
|       xxxxx<BR>(5 rows)<BR><BR clear=3Dal=
l>I
cleared out the client ports.<BR><BR>The funny thing is that process ID's=
30960,
30961 and 2830 are using a lot of the CPU but according to pg_stat_activi=
ty the
<command string not enabled> has not been updated.  Doesn't th=
is
suggest that those processes are NOT running anything on the database, or=
at
least anything new? <BR><BR>Here is what I get from "ps axuf":<BR><BR>pos=
tgres
25635  0.0  0.2 34864 5592 ?      =
; 
S    Oct01   0:02 /usr/bin/postmaster -p 5432 -D=

/var/lib/pgsql/data<BR>postgres 25637  0.0  0.0 11220  768=

?        S   
Oct01   1:07  \_ postgres: logger process <BR>postgres
25639  0.0  0.8 35100 17624 ?     &nbs=
p;
S    Oct01   5:03  \_ postgres: writer
process<BR>postgres 25640  0.0  0.0 12220 1568
?        S   
Oct01   0:50  \_ postgres: stats buffer process<BR>postgre=
s
25641  0.0  0.0 11908 1224 ?      =
; 
S    Oct01   1:03  |   \_ postgre=
s:
stats collector process<BR>postgres 30950  2.2  1.1 38728 23000=

?       S    Nov20 
74:23  \_ postgres: user3 my_db 127.0.0.1(xxx) idle<BR>postgres 3095=
1 
2.1  1.1 38752 23328 ?      
S    Nov20  70:22  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<BR>postgres 30960 21.3  1.1 39840 24740
?       S    Nov20 715:38&nb=
sp; \_
postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres 30961 17.2  1.=
1 39496
24356 ?       S    Nov20
578:08  \_ postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres
30962  9.5  1.1 39864 24588 ?     &nbs=
p;
R    Nov20 320:22  \_ postgres: user1 my_db 127.0.0.1=
(xxx)
PARSE<BR>postgres 31068  0.0   0.2 36584 4940
?        S   
Nov20   0:00  \_ postgres: user1 my_test_db 127.0.0.1(xxx)=

idle<BR>postgres 31069  0.6  0.9 36724 20268
?       S    Nov20 
22:09  \_ postgres: user1 my_test_db 127.0.0.1(xxx) idle<BR>postgres=

31070  0.0  0.2 35792 4548 ?      =
; 
S    Nov20   0:00  \_ postgres: user1 my_te=
st_db
127.0.0.1(xxx) idle<BR>postgres 31199  1.8  1.1 39320 23708
?       S    Nov20 
62:14  \_ postgres: user2 my_db 127.0.0.1(xxx) idle<BR>postgres 3135=
9 
0.6  1.0 37092 21308 ?      
S    Nov20  21:11  \_ postgres: user2 my_test_db=

127.0.0.1(xxx) idle<BR>postgres 31361  0.0  0.2 36720 5000
?        S   
Nov20   0:00  \_ postgres: user2 my_test_db 127.0.0.1(xxx)=

idle<BR>postgres 31362  1.5  1.0 38132 21384
?       S    Nov20 
53:26  \_ postgres: user2 my_test_db 127.0.0.1(xxx) idle<BR>postgres=

32030  5.1  1.1 39816 24740 ?     &nbs=
p;
S    Nov20 171:42  \_ postgres: user2 my_db 127.0.0.1=
(xxx)
idle<BR>postgres   877 14.6  1.1 39812 24676
?       S    Nov20 481:37&nb=
sp; \_
postgres: user1 my_db 127.0.0.1(xxx) idle<BR>postgres  3008  4.=
9 
1.1 39404 24460 ?       S   =
Nov20
158:17  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<BR>postgres  3009  1.7  1.1 39012 23804
?       S    Nov20 
56:44  \_ postgres: user2 my_db 127.0.0.1(xxx) idle in
transaction<BR>postgres 17186  1.2  1.1 38804 23204
?       S    Nov20 
31:19  \_ postgres: user3 my_db 127.0.0.1 (xxx) idle<BR>postgres
19966  2.7  1.1 38596 23056 ?     &nbs=
p;
S    Nov21  25:08  \_ postgres: user3 my_db
127.0.0.1(xxx) idle<BR>postgres 30306 19.4  1.1 39448 23908
?       S    07:43 
86:45  \_ postgres: user2 my_db 127.0.0.1 (xxx) idle<BR>postgres&nbs=
p; 2830
15.4  1.0 38992 22068 ?      
S    10:45  40:40  \_ postgres: user1 my_db
127.0.0.1(xxx) idle<BR><BR>Anyone have any clue why these processes are e=
ating
up so much CPU time?<BR><BR>Thanks,<BR>Aaron<BR><BR>--
<BR> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<BR>  
Aaron Bono<BR>   Aranya Software Technologies, Inc.<BR> &n=
bsp; <A
href=3D"http://www.aranya.com">http://www.aranya.com</A><BR>   =
<A
href=3D"http://codeelixir.com">http://codeelixir.com</A><BR>=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

<P> ************************************************************ **********=
<BR>This
message contains confidential information intended only for the use of th=
e
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.</P>
<P>Thank you.</P>
<P><FONT
size=3D1>          &nbs=
p;            =
;            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;            =
;            =
             =

FADLD
Tag</FONT><BR> ***********************************************************=
***********</P>
</BODY></HTML>

------_=_NextPart_001_01C70E7D.0DE556B5--
htomeh [ Mi, 22 November 2006 22:27 ] [ ID #1544263 ]

Re: Monitoring PostgreSQL Process

------=_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 <postgresql [at] 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
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>The following query with the stats_command_string parameter turned on will give you some of the SQL per transaction:-</div>
<div> </div>
<div><br>SELECT<br> datid,<br> datname as "DB Name",<br> substr(procpid,1,6) as "Procpid",<br> substr(usesysid,1,5) as "UseSysid",<br> usename,<br> current_query as "SQL",<br> query_start
<br>FROM<br> pg_stat_activity<br>order by<br> procpid;<br><br> </div>
<div><span class="gmail_quote">On 11/22/06, <b class="gmail_sendername">Aaron Bono</b> <postgresql [at] aranya.com> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">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.
<br><br>Is there a way to monitor the SQL being run for a specific connection/process? <br><br>We are using PostgreSQL 8.1.5 on CentOS Linux.<br><br>Thanks,<br>Aaron<br clear="all"><span class="sg"><br>-- <br> ============================================================ ======
<br>   Aaron Bono<br>   Aranya Software Technologies, Inc. <br>   <a onclick="return top.js.OpenExtLink(window,event,this)" href="http://www.aranya.com/" target="_blank">http://www.aranya.com</a><br>   <a onclick="return top.js.OpenExtLink(window,event,this)" href="http://codeelixir.com/" target="_blank">
http://codeelixir.com</a><br> ============================================================ ====== </span></blockquote></div><br>

------=_Part_38511_30598640.1164240133871--
adey [ Do, 23 November 2006 01:02 ] [ ID #1545469 ]

Re: Monitoring PostgreSQL Process

------=_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 <adey11 [at] 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 [at] 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
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.<br>
<br>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.
<br><br>Thanks all.<br><br>On 11/22/06, <b class="gmail_sendername">adey</b> <adey11 [at] gmail.com> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>The following query with the stats_command_string parameter turned on will give you some of the SQL per transaction:-</div>
<div> </div>
<div><br>SELECT<br> datid,<br> datname as "DB Name",<br> substr(procpid,1,6) as "Procpid",<br> substr(usesysid,1,5) as "UseSysid",<br> usename,<br> current_query as "SQL",<br> query_start
<br>FROM<br> pg_stat_activity<br>order by<br> procpid;<br><br> </div><div><span class="e" id="q_10f121eb73e411cf_1">
<div><span class="gmail_quote">On 11/22/06, <b class="gmail_sendername">Aaron Bono</b> <<a href="mailto:postgresql [at] aranya.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">postgresql [at] aranya.com
</a>> wrote:</span>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left: 1ex;">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.
<br><br>Is there a way to monitor the SQL being run for a specific connection/process? <br><br>We are using PostgreSQL 8.1.5 on CentOS Linux.<br><br></blockquote></div></span></div></blockquote></div><br clear="all"><br>
-- <br> ============================================================ ======<br>   Aaron Bono<br>   Aranya Software Technologies, Inc.<br>   http://www.aranya.com<br>   <a href="http://codeelixir.com">
http://codeelixir.com</a><br> ============================================================ ======

------=_Part_66256_19368765.1164249208732--
Aaron Bono [ Do, 23 November 2006 03:33 ] [ ID #1545473 ]
Datenbanken » gmane.comp.db.postgresql.admin » Monitoring PostgreSQL Process

Vorheriges Thema: PGSQL manager password lost.
Nächstes Thema: Fw: postgres database crash

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum