Skip site navigation (1) Skip section navigation (2)

Re: How to track number of connections and hosts to Postgres cluster

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>
Cc: Guillaume Lelarge <guillaume(at)lelarge(dot)info>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How to track number of connections and hosts to Postgres cluster
Date: 2011-08-24 11:09:18
Message-ID: 4E54DBDE.9030000@orkash.com (view raw or flat)
Thread:
Lists: pgsql-performance
pg_stat_activity keeps track of all this information.

select * from pg_stat_activity where datname='databasename';



Venkat Balaji wrote:
> Thanks Guillaume !!
>
> But, if put log_connections to on and log_disconnections to on 
> wouldn't the Postgres be logging in lot of data ?
>
> Will this not be IO intensive ? I understand that this is the best 
> way, but, would want to know if there is an other way to reduce IO ( 
> may be through queries to catalog tables ).
>
> Thanks
> Venkat
>
> On Wed, Aug 24, 2011 at 1:19 PM, Guillaume Lelarge 
> <guillaume(at)lelarge(dot)info <mailto:guillaume(at)lelarge(dot)info>> wrote:
>
>     On Wed, 2011-08-24 at 13:05 +0530, Venkat Balaji wrote:
>     > Hello Everyone,
>     >
>     > I am working on an alert script to track the number of
>     connections with the
>     > host IPs to the Postgres cluster.
>     >
>     > 1. I need all the host IPs making a connection to Postgres
>     Cluster (even for
>     > a fraction of second).
>
>     You should set log_connections to on.
>
>     > 2. I would also want to track number of IDLE connections, IDLE IN
>     > TRANSACTION connections and length of the connections as well.
>     >
>
>     IDLE and IDLE in transactions are the kind of informations you get in
>     pg_stat_activity.
>
>     Length of connections, you can get it with log_disconnections.
>
>     > I would be making use of pg_stat_activity and also thought of
>     enabling
>     > logging the host ips in the db server log files which seems to
>     be expensive
>     > for me (in terms of IO and logfile size).
>     >
>
>     Using pg_stat_activity won't get you really small connections. You
>     need
>     log_connections for that, and log_disconnections for the duration of
>     connections. So you'll have to work on a tool that could get some
>     informations with queries on pg_stat_activity, and that could read
>     PostgreSQL log files.
>
>
>     --
>     Guillaume
>      http://blog.guillaume.lelarge.info
>      http://www.dalibo.com
>
>

In response to

Responses

pgsql-performance by date

Next:From: Venkat BalajiDate: 2011-08-24 11:21:14
Subject: Re: How to track number of connections and hosts to Postgres cluster
Previous:From: Venkat BalajiDate: 2011-08-24 11:07:02
Subject: Re: How to track number of connections and hosts to Postgres cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group