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

* Monitoring Connections
@ 2006-09-20 17:48 Aaron Bono <postgresql@aranya.com>
  2006-09-20 18:21 ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Aaron Bono @ 2006-09-20 17:48 UTC (permalink / raw)
  To: pgsql-admin

Is there a way to get diagnostics on current connections to the database?
Specifically I am looking for:

1. How many current connections are there?
2. Who is connected (user name, database and from what IP address)?
3. When was the last time the connection was used?
4. When was the connection established?

I can get most of this information from the ps command but am having problem
with getting the information on #3.

My problem is that I have a number of web sites running on the server all
using different databases and/or user log ins and also using connection
pooling.  Whenever I redeploy an application, the "old" connections do not
appear to disconnect but new connections are established.  I fear that,
since the application server is not actually restarting but just reloading
the application, it is keeping old connections open when they really should
be closed.  This is something I will need to fix in the application but I
need to do some digging before I know this is indeed the problem.

I am using PostgreSQL 8.1.3 on CentOS.

Thanks!

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

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

* Re: Monitoring Connections
  2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
@ 2006-09-20 18:21 ` Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:25   ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 6+ messages in thread

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

Query the pg_stat_activity. 
For more detailed stats activities, you may query the pg_stat_* and
pg_statio_*  system views.
 
http://www.postgresql.org/docs/8.0/static/monitoring.html
 
--
  Husam 
  http://firstdba.googlepages.com
 


________________________________

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Aaron Bono
Sent: Wednesday, September 20, 2006 10:48 AM
To: pgsql admin
Subject: [ADMIN] Monitoring Connections


Is there a way to get diagnostics on current connections to the
database?  Specifically I am looking for:

1. How many current connections are there?
2. Who is connected (user name, database and from what IP address)? 
3. When was the last time the connection was used?
4. When was the connection established?

I can get most of this information from the ps command but am having
problem with getting the information on #3.

My problem is that I have a number of web sites running on the server
all using different databases and/or user log ins and also using
connection pooling.  Whenever I redeploy an application, the "old"
connections do not appear to disconnect but new connections are
established.  I fear that, since the application server is not actually
restarting but just reloading the application, it is keeping old
connections open when they really should be closed.  This is something I
will need to fix in the application but I need to do some digging before
I know this is indeed the problem. 

I am using PostgreSQL 8.1.3 on CentOS.

Thanks!

-- 
==================================================================
   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] 6+ messages in thread

* Re: Monitoring Connections
  2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
  2006-09-20 18:21 ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
@ 2006-09-20 18:25   ` Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:46     ` Re: Monitoring Connections Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tomeh, Husam @ 2006-09-20 18:25 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; Aaron Bono <postgresql@aranya.com>; pgsql-admin

Make that:  http://www.postgresql.org/docs/8.1/static/monitoring.html
8.1 docs rather..   :)
 
--
  Husam 
  http://firstdba.googlepages.com <http://firstdba.googlepages.com/; 

________________________________

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tomeh, Husam
Sent: Wednesday, September 20, 2006 11:22 AM
To: Aaron Bono; pgsql admin
Subject: Re: [ADMIN] Monitoring Connections


Query the pg_stat_activity. 
For more detailed stats activities, you may query the pg_stat_* and
pg_statio_*  system views.
 
http://www.postgresql.org/docs/8.0/static/monitoring.html
 
--
  Husam 
  http://firstdba.googlepages.com
 


________________________________

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Aaron Bono
Sent: Wednesday, September 20, 2006 10:48 AM
To: pgsql admin
Subject: [ADMIN] Monitoring Connections


Is there a way to get diagnostics on current connections to the
database?  Specifically I am looking for:

1. How many current connections are there?
2. Who is connected (user name, database and from what IP address)? 
3. When was the last time the connection was used?
4. When was the connection established?

I can get most of this information from the ps command but am having
problem with getting the information on #3.

My problem is that I have a number of web sites running on the server
all using different databases and/or user log ins and also using
connection pooling.  Whenever I redeploy an application, the "old"
connections do not appear to disconnect but new connections are
established.  I fear that, since the application server is not actually
restarting but just reloading the application, it is keeping old
connections open when they really should be closed.  This is something I
will need to fix in the application but I need to do some digging before
I know this is indeed the problem. 

I am using PostgreSQL 8.1.3 on CentOS.

Thanks!

-- 
==================================================================
   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] 6+ messages in thread

* Re: Monitoring Connections
  2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
  2006-09-20 18:21 ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:25   ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
@ 2006-09-20 18:46     ` Aaron Bono <postgresql@aranya.com>
  2006-09-20 19:15       ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 1 reply; 6+ messages in thread

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

On 9/20/06, Tomeh, Husam <htomeh@firstam.com> wrote:
>
>  Make that:  http://www.postgresql.org/docs/8.1/static/monitoring.html
> 8.1 docs rather..   :)
>
> --
>   Husam
>   http://firstdba.googlepages.com
>
>  ------------------------------
> *From:* pgsql-admin-owner@postgresql.org [mailto:
> pgsql-admin-owner@postgresql.org] *On Behalf Of *Tomeh, Husam
> *Sent:* Wednesday, September 20, 2006 11:22 AM
> *To:* Aaron Bono; pgsql admin
> *Subject:* Re: [ADMIN] Monitoring Connections
>
>  Query the pg_stat_activity.
> For more detailed stats activities, you may query the pg_stat_* and
> pg_statio_*  system views.
>
> http://www.postgresql.org/docs/8.0/static/monitoring.html
>
> --
>   Husam
>   http://firstdba.googlepages.com
>
>
>  ------------------------------
> *From:* pgsql-admin-owner@postgresql.org [mailto:
> pgsql-admin-owner@postgresql.org] *On Behalf Of *Aaron Bono
> *Sent:* Wednesday, September 20, 2006 10:48 AM
> *To:* pgsql admin
> *Subject:* [ADMIN] Monitoring Connections
>
> Is there a way to get diagnostics on current connections to the database?
> Specifically I am looking for:
>
> 1. How many current connections are there?
> 2. Who is connected (user name, database and from what IP address)?
> 3. When was the last time the connection was used?
> 4. When was the connection established?
>
> I can get most of this information from the ps command but am having
> problem with getting the information on #3.
>
> My problem is that I have a number of web sites running on the server all
> using different databases and/or user log ins and also using connection
> pooling.  Whenever I redeploy an application, the "old" connections do not
> appear to disconnect but new connections are established.  I fear that,
> since the application server is not actually restarting but just reloading
> the application, it is keeping old connections open when they really should
> be closed.  This is something I will need to fix in the application but I
> need to do some digging before I know this is indeed the problem.
>
> I am using PostgreSQL 8.1.3 on CentOS.
>
> Thanks!
>


I checked the pg_stat_activity table and it doesn't give any more
information than I got from the ps command.

Is there any way to find out the last time the connection was used?

Thanks,
Aaron

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

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

* Re: Monitoring Connections
  2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
  2006-09-20 18:21 ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:25   ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:46     ` Re: Monitoring Connections Aaron Bono <postgresql@aranya.com>
@ 2006-09-20 19:15       ` Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 19:20         ` Re: Monitoring Connections Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tomeh, Husam @ 2006-09-20 19:15 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-admin

The backend_start column in pg_stat_activity system view gives you the
start time of the backend connection which's what you see in the ps
command; however, the query_start column should give you the start time
of last query within the connection.  The values of these columns may
not be necessarily the same if you're using connection pooling. If you
don't see the data for these columns, make sure to enable the stats
collection on your pg postgresql.conf file (stats_*  parameters).
 
Regards, 

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

From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of
Aaron Bono
Sent: Wednesday, September 20, 2006 11:46 AM
To: Tomeh, Husam
Cc: pgsql admin
Subject: Re: [ADMIN] Monitoring Connections 
 
I checked the pg_stat_activity table and it doesn't give any more
information than I got from the ps command. 

Is there any way to find out the last time the connection was used?

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] 6+ messages in thread

* Re: Monitoring Connections
  2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
  2006-09-20 18:21 ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:25   ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
  2006-09-20 18:46     ` Re: Monitoring Connections Aaron Bono <postgresql@aranya.com>
  2006-09-20 19:15       ` Re: Monitoring Connections Tomeh, Husam <htomeh@firstam.com>
@ 2006-09-20 19:20         ` Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jeff Frost @ 2006-09-20 19:20 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-admin

I wrote some slony task based documentation for Pervasive a few months 
back. Now that they've decided to opt out of the postgres community I 
understand they will be turning over the documentation to the community. 
Hopefully we'll be able to make some use of this stuff.  Unfortunately, I'm 
not sure who we could contact to get hold of this stuff.

On Wed, 20 Sep 2006, Tomeh, Husam wrote:

> The backend_start column in pg_stat_activity system view gives you the
> start time of the backend connection which's what you see in the ps
> command; however, the query_start column should give you the start time
> of last query within the connection.  The values of these columns may
> not be necessarily the same if you're using connection pooling. If you
> don't see the data for these columns, make sure to enable the stats
> collection on your pg postgresql.conf file (stats_*  parameters).
>
> Regards,
>
> --
>  Husam
>  http://firstdba.googlepages.com
>
>
> ________________________________
>
> From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of
> Aaron Bono
> Sent: Wednesday, September 20, 2006 11:46 AM
> To: Tomeh, Husam
> Cc: pgsql admin
> Subject: Re: [ADMIN] Monitoring Connections
>
> I checked the pg_stat_activity table and it doesn't give any more
> information than I got from the ps command.
>
> Is there any way to find out the last time the connection was used?
>
> 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
> **********************************************************************
>

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



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


end of thread, other threads:[~2006-09-20 19:20 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-20 17:48 Monitoring Connections Aaron Bono <postgresql@aranya.com>
2006-09-20 18:21 ` Tomeh, Husam <htomeh@firstam.com>
2006-09-20 18:25   ` Tomeh, Husam <htomeh@firstam.com>
2006-09-20 18:46     ` Aaron Bono <postgresql@aranya.com>
2006-09-20 19:15       ` Tomeh, Husam <htomeh@firstam.com>
2006-09-20 19:20         ` Jeff Frost <jeff@frostconsultingllc.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