Re: total number of concurrent connections

Lonni J Friedman <netllama@gmail.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.

> 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.

            regards, tom lane