Re: Too many clients connected

On Mon, 2005-10-10 at 10:59, Venki@insoft.com wrote:
>
>
> Hi All,
> I am sorry if this was already discussed. I am new to the world of
> postgressql and was experincing a problem for which I need help of an
> expert in this lists.
> We have devloped a site(asp.net 1.1) for which we have used postgresql 7.4
> as the backend database. When the number of connections increases we are
> getting an error "Too many clients are connected" we then adjusted the
> number of connections from the initial value of 30+ to 200 it get solved
> initially but we are experincing the same problem again. Before adjust the
> connection settings I would like some one with answers to the following
> questions.

It sounds to me like your application is leaking connections.  This
usually happens when your application doesn't close connections on
exit.  This is bad behaviour, and the best fix is to stop this from
happening.

> 1. How does postgresql handles database connections?

One at a time?  Not sure what you're asking for here.  Or do you want to
know about time outs?  Generally, PostgreSQL lets the TCP/IP stack
timeout connections.  for this reason, you may find it useful to set
tcp_keepalive to something small, like 300, and the retries to something
small as well.

On a linux box (that has sysctl installed), you can see these settings
like so:

 sysctl -a|grep tcp_k
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 600

The keepalive time is normally set to two hours (7200).  What happens is
that at the end of the keepalive_time, the kernel will send a tcp
keepalive packet to the other end.  It will wait 75 seconds, and repeat
this for 8 more times (9 total) and THEN will time out the connection
and let PostgreSQL know that the connection is dead.

If you set your keepalive time to 600 (10 minutes) and reduce the probes
and intvl so they multiply out to about 5 minutes, then "lost"
connections should be harvested by postgresql fast enough to keep up
with the loss.  Note this isn't a "fix" it's a workaround.

> 2. Under what situations does this error "Too many clients connected" is
> thrown?

When too many clients are connected?  Sorry.  I think my answer to 1
covered this point as well.

> 3. Should we increase the max connection settings to say 1000 if yes what
> about the PC configuration that is needed for it. We are currently running
> the postgresql in a linux box with 1 GB ram and 2GHZ xeon processor.

No, you should set it high enough that by reducing tcp keepalive you
don't ever run out.  Monitor the number of backends with something like:

watch "ps ax|grep post|wc -l"

(or use the backend to check it with some kind of select from pg_...)

and see how high your number gets.  As long as it doesn't spike really
fast, the tcp keepalive will keep you afloat until you can fix your .net
app or connector.

Also, look at connection pooling, as mentioned elsewhere.  If you don't
want to use application level pooling, look at pg_pool.

> 4. is it advisable to upgrade postgresql to 8.0?? will it solve this error
> once and for all.

No, this error is NOT in postgresql's domain, it is in your
application's domain.  However, 8.0 is quite a step up from 7.4...