pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aaron Bono <postgresql@aranya.com>
To: Tomeh, Husam <htomeh@firstam.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Monitoring PostgreSQL Process
Date: Wed, 22 Nov 2006 16:12:08 -0600
Message-ID: <bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com> (raw)
In-Reply-To: <F1B0F9305B343E43A1C3EECE48B853D5093093@CITGSNA01SXCH02.ana.firstamdata.com>
References: <bf05e51c0611221346t3970fcfahe81941ec974916ed@mail.gmail.com>
	<F1B0F9305B343E43A1C3EECE48B853D5093093@CITGSNA01SXCH02.ana.firstamdata.com>

On 11/22/06, Tomeh, Husam <htomeh@firstam.com> wrote:
>
>  If you're not logged in as a superuser, you won't be able to see other
> user's queries. Try to login as a superuser (ei, postgres or whatever you
> superuser account is).
>

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

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



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

view thread (9+ messages)  latest in thread

Message-ID: <bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com>
Permalink:  ../bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com/
Also on:    postgresql.org/message-id/bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: postgresql@aranya.com, htomeh@firstam.com
  Subject: Re: Monitoring PostgreSQL Process
  In-Reply-To: <bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com>
  References: <bf05e51c0611221346t3970fcfahe81941ec974916ed@mail.gmail.com> <F1B0F9305B343E43A1C3EECE48B853D5093093@CITGSNA01SXCH02.ana.firstamdata.com> <bf05e51c0611221412p2cf866c3p51873115aed477e9@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox