Обсуждение: Too many clients connected
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. 1. How does postgresql handles database connections? 2. Under what situations does this error "Too many clients connected" is thrown? 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. 4. is it advisable to upgrade postgresql to 8.0?? will it solve this error once and for all. Thanks in advance regards venki
first of all, your clients are closing connections?? yo should use a pool connection. 2005/10/10, Venki@insoft.com <Venki@insoft.com>: > > > > > 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. > 1. How does postgresql handles database connections? > 2. Under what situations does this error "Too many clients connected" is > thrown? > 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. > 4. is it advisable to upgrade postgresql to 8.0?? will it solve this error > once and for all. > > Thanks in advance > > regards > venki > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Atte Moises Alberto Lindo Gutarra Consultor y Desarrollador Java / Open Source TUMI Solutions SAC Tel: +51.13481104 Cel: +51.197366260 MSN : mlindo@tumisolutions.com
Venki, Postgres is using a thread per _realized_ connection, and reserves some memory per _configured_ connection. So setting the configured connection count too high is not free even if normally you won't have that many connections actually open. And of course at peak load the number of connections will go high, causing higher resource usage, which might be worse than handling the same load with a fixed number of connection managed by a connection pool. So the short answer to your question is: you should look into using a connection pooling solution for accessing the DB. I guess you're using .net, which I have absolutely no idea about, but I guess there must be some connection pooling project, general or postgres specific - search for it :-) HTH, Csaba. On Mon, 2005-10-10 at 17: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. > 1. How does postgresql handles database connections? > 2. Under what situations does this error "Too many clients connected" is > thrown? > 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. > 4. is it advisable to upgrade postgresql to 8.0?? will it solve this error > once and for all. > > Thanks in advance > > regards > venki > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
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...