From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: Venkat Balaji <venkat.balaji@verse.in>
Cc: Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to track number of connections and hosts to Postgres cluster
Date: Wed, 24 Aug 2011 16:39:18 +0530
Message-ID: <4E54DBDE.9030000@orkash.com> (raw)
In-Reply-To: <CAFrxt0iiQO8bBryOXRiCcW-U6NrdJepLZ_ZRXV4d+RhZziWbcA@mail.gmail.com>
References: <CAFrxt0gk66pDOuEjB9oQJ=b1VYAeiiNDgEaFz99RhVxrecWsMg@mail.gmail.com>
<1314172144.2193.9.camel@localhost.localdomain>
<CAFrxt0iiQO8bBryOXRiCcW-U6NrdJepLZ_ZRXV4d+RhZziWbcA@mail.gmail.com>
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 <mailto: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
>
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adarsh.sharma@orkash.com, venkat.balaji@verse.in, guillaume@lelarge.info
Subject: Re: How to track number of connections and hosts to Postgres cluster
In-Reply-To: <4E54DBDE.9030000@orkash.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