Re: total number of concurrent connections

First, thanks for replying! Comments/questions below.

On Sun, Jul 26, 2009 at 1:23 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
>> I've got a postgresql-8.1.10 instance running on a Linux system.  Over
>> the weekend it suddenly started rejecting client connection attempts
>> with a "too many connected" error. ...
>> The real problem that I'm having is that I can't seem to find any
>> obvious way to determine what the current number of connections are to
>> the server.  Googling a bit suggests that running "select count(*)
>> from pg_stat_activity;" would provide the answer, however I'm very
>> suspicious of the accuracy of the results.
>
> pg_stat_activity should be reasonably trustworthy, modulo the fact that
> the display might be a fraction of a second out-of-date.

Hrmm, that's not what I'm seeing. I'm finding that connections
continue to appear in the table long after I've terminated a remote
pqsl connection. I'm talking minutes or even hours. In one case, I
rebooted a system that was supposedly connected, and it continued to
appear in the table (or rather the IP associated with the system).

>
>> especially when I run 'netstat -a | grep -c postgr' and I get 183 back
>> (granted many are in a TIME_WAIT state, but shouldn't that still count
>> as a valid connection to the database?).
>
> No, it doesn't.  That's a recently-dead connection.  It may still be of
> interest to the TCP stack, but Postgres has forgotten about it.
>
>> Another thing that makes no sense is why the client_addr is seemingly
>> null
>
> Expected for a Unix-socket connection.
>
> I think you've got a lot of extremely transient connections and you just
> happened to have a burst of them at the same instant.  Aside from the
> question of not being sure where to set max_connections, this is a
> pretty bad idea from a performance standpoint.  PG backends aren't all
> that lightweight, so it's not good to fire one up for just a single
> query, which is what it sounds like your app is doing.  Consider using a
> connection pooler.

Thanks, I'll definitely look into that as time allows.