total number of concurrent connections

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: total number of concurrent connections
Date: 2009-07-26 20:11:19
Message-ID: 7c1574a90907261311p71fdd0bas1bc0d2f2f2a35566@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Greetings,
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. At the time, we were using the
default max_connections of 100. I've doubled this for the time being
until we can determine what/why its misbehaving.

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. At this very minute, it
claims that there are just 3 connections, however I'm fairly confident
that there must be a lot more, as we've go about 200 client systems
running an in hour app that are continuously hitting the database
(every 30-60 seconds), plus a few web apps that are seeing some
activity (even over the weekend). So just 3 seems wrong to me,
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?). For example, here's the
current output:

select * from pg_stat_activity ;
datid | datname | procpid | usesysid | usename |
current_query | query_start | backend_start |
client_addr | client_port
-------+---------+---------+----------+-----------+------------------------------+-------------+-------------------------------+-------------+-------------
16636 | nightly | 29579 | 16386 | lfriedman | <command string
not enabled> | | 2009-07-26 13:07:38.139417-07 |
| -1
16636 | nightly | 22822 | 10 | postgres | <command string
not enabled> | | 2009-07-26 13:00:02.723319-07 |
| -1
65770 | minidvs | 22662 | 16386 | lfriedman | <command string
not enabled> | | 2009-07-26 09:48:22.958256-07 |
| -1
(3 rows)

Another thing that makes no sense is why the client_addr is seemingly
null (is there some postgresql.conf option that I should be setting or
changing?).

I'll be very happy to hear that I'm misunderstanding something.

Thanks in advance for guidance.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand https://netllama.linux-sxs.org

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-07-26 20:23:26 Re: total number of concurrent connections
Previous Message Jaime Casanova 2009-07-24 21:13:22 Re: Understanding the output of EXPLAIN ANALYSE