pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Monitoring PostgreSQL Process
9+ messages / 3 participants
[nested] [flat]

* Monitoring PostgreSQL Process
@ 2006-11-22 05:49 Aaron Bono <postgresql@aranya.com>
  2006-11-22 18:02 ` Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
  2006-11-23 00:02 ` Re: Monitoring PostgreSQL Process adey <adey11@gmail.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Aaron Bono @ 2006-11-22 05:49 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 05:49 Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
@ 2006-11-22 18:02 ` Tomeh, Husam <htomeh@firstam.com>
  2006-11-22 18:46   ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Tomeh, Husam @ 2006-11-22 18:02 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; pgsql-admin

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.
 
--
  Husam
  http://firstdba.googlepages.com
 
________________________________

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


I have a couple processes/connections to one of our databases that
appears to be eating up most of the CPU and we are trying to determine
what these processes are doing that is taking so much CPU time.

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

We are using PostgreSQL 8.1.5 on CentOS Linux.

Thanks,
Aaron

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

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

Thank you.

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 05:49 Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  2006-11-22 18:02 ` Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
@ 2006-11-22 18:46   ` Aaron Bono <postgresql@aranya.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Aaron Bono @ 2006-11-22 18:46 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

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

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

--
>   Husam
>   http://firstdba.googlepages.com
>
>  ------------------------------
> *From:* pgsql-admin-owner@postgresql.org [mailto:
> pgsql-admin-owner@postgresql.org] *On Behalf Of *Aaron Bono
> *Sent:* Tuesday, November 21, 2006 9:50 PM
> *To:* pgsql-admin@postgresql.org
> *Subject:* [ADMIN] Monitoring PostgreSQL Process
>
> I have a couple processes/connections to one of our databases that appears
> to be eating up most of the CPU and we are trying to determine what these
> processes are doing that is taking so much CPU time.
>
> Is there a way to monitor the SQL being run for a specific
> connection/process?
>
> We are using PostgreSQL 8.1.5 on CentOS Linux.
>
> Thanks,
> Aaron
>
> --
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================
>
> **********************************************************************
> This message contains confidential information intended only for the use
> of the addressee(s) named above and may contain information that is legally
> privileged.  If you are not the addressee, or the person responsible for
> delivering it to the addressee, you are hereby notified that reading,
> disseminating, distributing or copying this message is strictly prohibited.
> If you have received this message by mistake, please immediately notify us
> by replying to the message and delete the original message immediately
> thereafter.
>
> Thank you.
>
>
> FADLD Tag
> **********************************************************************
>



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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 05:49 Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
@ 2006-11-23 00:02 ` adey <adey11@gmail.com>
  2006-11-23 02:33   ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  1 sibling, 1 reply; 9+ messages in thread

From: adey @ 2006-11-23 00:02 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-admin

The following query with the stats_command_string parameter turned on will
give you some of the SQL per transaction:-


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


On 11/22/06, Aaron Bono <postgresql@aranya.com> wrote:
>
> I have a couple processes/connections to one of our databases that appears
> to be eating up most of the CPU and we are trying to determine what these
> processes are doing that is taking so much CPU time.
>
> Is there a way to monitor the SQL being run for a specific
> connection/process?
>
> We are using PostgreSQL 8.1.5 on CentOS Linux.
>
> Thanks,
> Aaron
>
> --
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 05:49 Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  2006-11-23 00:02 ` Re: Monitoring PostgreSQL Process adey <adey11@gmail.com>
@ 2006-11-23 02:33   ` Aaron Bono <postgresql@aranya.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Aaron Bono @ 2006-11-23 02:33 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: pgsql-admin

I queried pg_stat_activity a bunch of times and was able to tell what tables
were being queried.  I finally honed in on the section of code causing the
problem and fix it and all is good on the server now - over 90% idle.

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

Thanks all.

On 11/22/06, adey <adey11@gmail.com> wrote:
>
> The following query with the stats_command_string parameter turned on will
> give you some of the SQL per transaction:-
>
>
> SELECT
>  datid,
>  datname as "DB Name",
>  substr(procpid,1,6) as "Procpid",
>  substr(usesysid,1,5) as "UseSysid",
>  usename,
>  current_query as "SQL",
>  query_start
> FROM
>  pg_stat_activity
> order by
>  procpid;
>
>
> On 11/22/06, Aaron Bono <postgresql@aranya.com> wrote:
> >
> > I have a couple processes/connections to one of our databases that
> > appears to be eating up most of the CPU and we are trying to determine what
> > these processes are doing that is taking so much CPU time.
> >
> > Is there a way to monitor the SQL being run for a specific
> > connection/process?
> >
> > We are using PostgreSQL 8.1.5 on CentOS Linux.
> >
> >

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
@ 2006-11-22 21:27 Tomeh, Husam <htomeh@firstam.com>
  2006-11-22 21:46 ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tomeh, Husam @ 2006-11-22 21:27 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-admin

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@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
**********************************************************************

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 21:27 Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
@ 2006-11-22 21:46 ` Aaron Bono <postgresql@aranya.com>
  2006-11-22 21:53   ` Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Aaron Bono @ 2006-11-22 21:46 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 21:27 Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
  2006-11-22 21:46 ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
@ 2006-11-22 21:53   ` Tomeh, Husam <htomeh@firstam.com>
  2006-11-22 22:12     ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tomeh, Husam @ 2006-11-22 21:53 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Monitoring PostgreSQL Process
  2006-11-22 21:27 Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
  2006-11-22 21:46 ` Re: Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
  2006-11-22 21:53   ` Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
@ 2006-11-22 22:12     ` Aaron Bono <postgresql@aranya.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Aaron Bono @ 2006-11-22 22:12 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2006-11-23 02:33 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-22 05:49 Monitoring PostgreSQL Process Aaron Bono <postgresql@aranya.com>
2006-11-22 18:02 ` Tomeh, Husam <htomeh@firstam.com>
2006-11-22 18:46   ` Aaron Bono <postgresql@aranya.com>
2006-11-23 00:02 ` adey <adey11@gmail.com>
2006-11-23 02:33   ` Aaron Bono <postgresql@aranya.com>
2006-11-22 21:27 Re: Monitoring PostgreSQL Process Tomeh, Husam <htomeh@firstam.com>
2006-11-22 21:46 ` Aaron Bono <postgresql@aranya.com>
2006-11-22 21:53   ` Tomeh, Husam <htomeh@firstam.com>
2006-11-22 22:12     ` Aaron Bono <postgresql@aranya.com>

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