Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0873CB5DBF0 for ; Wed, 24 Aug 2011 08:09:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 23594-09 for ; Wed, 24 Aug 2011 11:09:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp200.dfw.emailsrvr.com (smtp200.dfw.emailsrvr.com [67.192.241.200]) by mail.postgresql.org (Postfix) with ESMTP id 9DBCBB5DBDC for ; Wed, 24 Aug 2011 08:09:07 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp10.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id DD0E41B830F; Wed, 24 Aug 2011 07:09:06 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp10.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 560971B82F0; Wed, 24 Aug 2011 07:09:04 -0400 (EDT) Message-ID: <4E54DBDE.9030000@orkash.com> Date: Wed, 24 Aug 2011 16:39:18 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Venkat Balaji CC: Guillaume Lelarge , pgsql-performance@postgresql.org Subject: Re: How to track number of connections and hosts to Postgres cluster References: <1314172144.2193.9.camel@localhost.localdomain> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000402070102010009040400" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/263 X-Sequence-Number: 44710 This is a multi-part message in MIME format. --------------000402070102010009040400 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > 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 > > --------------000402070102010009040400 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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@lelarge.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



--------------000402070102010009040400--