On Mon, 2005-10-10 at 10:59, Venki@xxxxxxxxxx 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... ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
