Skip site navigation (1) Skip section navigation (2)

Re: total number of concurrent connections

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: total number of concurrent connections
Date: 2009-07-27 23:18:48
Message-ID: 7c1574a90907271618y6359641fmad4fde425c26d74b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-07-27 23:31:46
Subject: Re: total number of concurrent connections
Previous:From: Bob McConnellDate: 2009-07-27 20:52:04
Subject: Re: Location of databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group