Re: Monitoring number of backends

On 10/23/2013 11:07 AM, David Kerr wrote:
> On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote:
> - Hi all.
> -
> - My website is about to get a little more popular.  I'm trying to add in
> - some measurements to determine an upper limit of how many concurrent
> - database connections I'm currently using.
> -
> - I've started running this:
> -
> - SELECT sum(numbackends) AS count, sum(xact_commit) as ttlcommit FROM
> - pg_stat_database;
> -
> - Every 10 seconds or so.  I don't think its what I want though.  It seems
> - way too small.  I'm guessing that its not a measure of the previous 10
> - seconds.  Its a count of how many backends are in use at the exact
> - moment I run the sql.
> -
> - Is there a cumulative count someplace?
> -
> - Thanks for your time,
> -
> - -Andy
>
> You've gotten good info from the other guys on how to scale your're DB
> but to answer you're original question. I usually use this query:
>
> select count(*) from pg_stat_activity where state <> 'idle';
>
> That gives you the # of "active" connections to your database and is
> something you want to try to get good metrics on.
>
> Idle connections have some overhead but if Active > # of CPUs your performance
> starts to degrade. Now, really that's pretty normal but, ideally, you need to
> know what the ratio of Active Connections to # CPUs still gives you acceptable
> performance. And that's really based on your app and hardware.
>
>

How often do you run that?  Once a second?  And graph it?  I was doing
it every 10 seconds, but it doesn't give me a good view of the system.

-Andy