Thread: Too many clients----A big problem for my team
Hi Experts,
I work with postgresql 8.0 version. I have a team of 14 developers. When all the team connects the DB i see this error very very frequently which is a severe problem for me and team all the day. The error is:
Please report this exception: java.sql.SQLException: FATAL: sorry, too many clients already
when i do trial and error changes i see this error differently but the essense of the error remains same.
I have max_connections=200 and i have 5 users for my DB. I dont understand what to do in order to resolve this. Please suggest me something by which i can overcome this problem. I referred few threads but none of them are of my case.
Waiting for your valuable suggestion..........
Thanks and Regards
Ram
Well, first of all, max_connections can only be set at server start. Are you restarting postgres when you do "trial and error changes"? Secondly, 5 users can still manage to make multiple connections. You can use ps to see how many connections are active at any given time. http://www.postgresql.org/docs/8.0/static/monitoring.html#MONITORING-PS -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 7, 2005, at 7:08 AM, ganapatiram wrote: > Hi Experts, > > I work with postgresql 8.0 version. I have a team of 14 > developers. When all the team connects the DB i see this error very > very frequently which is a severe problem for me and team all the day. > The error is: > > > Please report this exception: java.sql.SQLException: FATAL: sorry, > too many clients already > > > > when i do trial and error changes i see this error differently but the > essense of the error remains same. > > > > I have max_connections=200 and i have 5 users for my DB. I > dont understand what to do in order to resolve this. Please suggest me > something by which i can overcome this problem. I referred few threads > but none of them are of my case. > > > > > > Waiting for your valuable suggestion.......... > > > > > > > > > > Thanks and Regards > > Ram
> I work with postgresql 8.0 version. I have a team of 14 > developers. When all the team connects the DB i see this error very > very frequently which is a severe problem for me and team all the > day. The error is: > > Please report this exception: java.sql.SQLException: FATAL: sorry, > too many clients already ... > I have max_connections=200 and i have 5 users for my DB. I > dont understand what to do in order to resolve this... What is your _server_ telling you? Check the logs. Check ps for instances of postgres. Check netstat for connections to PG and track back to the source point of each of the connections. Keep a connection open to your server so you can run "select * from pg_stat_activity;" to see what queries are running. You will probably find: 1) Your app is lying to you or is connecting to a different server than you assumed if you have multiple servers. 2) You don't really have 200 connections (did you restart after changing your config?) 3) Your app is making more connections than you think. (Doing any pooling??) 4) Your apps aren't closing connections that you thought were being closed. Cheers, Steve
I had the same problem a few weeks ago. Was happening that our developers using Tomcat, were using connection pools, and stoping and restarting their aplications which causes to leave these connection opened and opening some new. Look in the servlet engine (or what they use) for the number of connections they made (in Tomcat it's a parameter), say them not to leave the connections opened, and not to stop and restart the java application when they make changes, they must restart the "engine" (it closes all the opened connections). It worked for me ... ganapatiram wrote: > Hi Experts, > > I work with postgresql 8.0 version. I have a team of 14 > developers. When all the team connects the DB i see this error very very > frequently which is a severe problem for me and team all the day. The > error is: > > > *Please report this exception: java.sql.SQLException: FATAL: sorry, too > many clients already* > > ** > > *when i do trial and error changes i see this error differently but the > essense of the error remains same. * > > ** > > * *I have max_connections=200 and i have 5 users for my DB. I > dont understand what to do in order to resolve this. Please suggest me > something by which i can overcome this problem. I referred few threads > but none of them are of my case. > > > > > > Waiting for your valuable suggestion.......... > > > > > > > > > > Thanks and Regards > > Ram > -- ******************************************************** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ********************************************************
Hi everydoby!
I have a table with more than 26000 rows and I need to use update a
column of this tables on all lines according with a column. So, I need to
use update 26000 times correct? I tried to use a this command:
"UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where
VARIABLES is the list separeted by commas. But the list has about 26000
entries, so I got a message of too long parameters. Is there a way to use
UPDATE pushing values from a file? Is there a way to run update more faster?
It is taking about 10 seconds for each UPDATE... Does anyone knows another
form to do this task?
Thanks
----- Original Message -----
From: "Daniel Rubio" <drubior@tinet.org>
To: <pgsql-admin@postgresql.org>
Sent: Wednesday, March 09, 2005 8:33 AM
Subject: Re: [ADMIN] Too many clients----A big problem for my team
> I had the same problem a few weeks ago.
>
> Was happening that our developers using Tomcat, were using connection
> pools, and stoping and restarting their aplications which causes to
> leave these connection opened and opening some new.
>
> Look in the servlet engine (or what they use) for the number of
> connections they made (in Tomcat it's a parameter), say them not to
> leave the connections opened, and not to stop and restart the java
> application when they make changes, they must restart the "engine" (it
> closes all the opened connections).
>
> It worked for me ...
>
> ganapatiram wrote:
>
> > Hi Experts,
> >
> > I work with postgresql 8.0 version. I have a team of 14
> > developers. When all the team connects the DB i see this error very very
> > frequently which is a severe problem for me and team all the day. The
> > error is:
> >
> >
> > *Please report this exception: java.sql.SQLException: FATAL: sorry, too
> > many clients already*
> >
> > **
> >
> > *when i do trial and error changes i see this error differently but the
> > essense of the error remains same. *
> >
> > **
> >
> > * *I have max_connections=200 and i have 5 users for my DB. I
> > dont understand what to do in order to resolve this. Please suggest me
> > something by which i can overcome this problem. I referred few threads
> > but none of them are of my case.
> >
> >
> >
> >
> >
> > Waiting for your valuable suggestion..........
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Thanks and Regards
> >
> > Ram
> >
>
>
> --
> ********************************************************
> Daniel Rubio Rodríguez
> OASI (Organisme Autònom Per la Societat de la Informació)
> c/ Assalt, 12
> 43003 - Tarragona
> Tef.: 977.244.007 - Fax: 977.224.517
> e-mail: drubio a oasi.org
> ********************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, Mar 09, 2005 at 11:44:33 -0300, Ricardo Valença de Assis <valenca@campusvirtual.br> wrote: > Hi everydoby! > > I have a table with more than 26000 rows and I need to use update a > column of this tables on all lines according with a column. So, I need to > use update 26000 times correct? I tried to use a this command: > "UPDATE database SET column1=0 WHERE column2 in (VARIABLES)", where > VARIABLES is the list separeted by commas. But the list has about 26000 > entries, so I got a message of too long parameters. Is there a way to use > UPDATE pushing values from a file? Is there a way to run update more faster? > It is taking about 10 seconds for each UPDATE... Does anyone knows another > form to do this task? If you really want to do this for all rows in the table just do: UPDATE tablename SET column1=0; > > ----- Original Message ----- > From: "Daniel Rubio" <drubior@tinet.org> > To: <pgsql-admin@postgresql.org> > Sent: Wednesday, March 09, 2005 8:33 AM > Subject: Re: [ADMIN] Too many clients----A big problem for my team Why did you include this message that had nothing to do with your question?
<div style="background-color:"><p>If it is necessary for you to comapre each row with a list of 26 possible values (VARIABLES)in the IN clause and not do a replacement of all values in column1 with "0" as suggested previously, you mightwant to place the 26000 variables in a temporary table that is either sorted physically in the required search order,or indexed, then place a select on the IN clause from that table.<p>I'm not surprised it's taking a long time to runthe update. Firstly, it's an update, and secondly, for each of the 26000 rows in the table, it is comparing with 26000variables, so it is doing 26000 x 26000 "selects".<br /><br /><br /><br /><br /><div><p>Adrian<p>ICQ 120480893<p><a href="https://www.paypal.com/refer/pal=N6T2FQ7WRPHH4">https://www.paypal.com/refer/pal=N6T2FQ7WRPHH4</a></div>From: BrunoWolff III <bruno@wolff.to> To: Ricardo Valen�a de Assis <valenca@campusvirtual.br> CC: pgsql-admin@postgresql.orgSubject: Re: [ADMIN] Slow Update Date: Wed, 9 Mar 2005 09:19:18 -0600 On Wed, Mar 09, 2005 at 11:44:33-0300, Ricardo Valen�a de Assis <valenca@campusvirtual.br> wrote: > Hi everydoby! > > I have a tablewith more than 26000 rows and I need to use update a > column of this tables on all lines according with a column.So, I need to > use update 26000 times correct? I tried to use a this command: > "UPDATE database SET column1=0WHERE column2 in (VARIABLES)", where > VARIABLES is the list separeted by commas. But the list has about 26000> entries, so I got a message of too long parameters. Is there a way to use > UPDATE pushing values from a file?Is there a way to run update more faster? > It is taking about 10 seconds for each UPDATE... Does anyone knows another> form to do this task? If you really want to do this for all rows in the table just do: UPDATE tablename SET column1=0;> > ----- Original Message ----- > From: "Daniel Rubio" <drubior@tinet.org> > To: <pgsql-admin@postgresql.org>> Sent: Wednesday, March 09, 2005 8:33 AM > Subject: Re: [ADMIN] Too many clients----Abig problem for my team Why did you include this message that had nothing to do with your question? ---------------------------(endof broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org</div><br clear="all" /><hr />Looking for love? Check out <a href="http://g.msn.com/8HMBENNZ/2749??PS=47575"target="_top">XtraMSN Personals</a>
On Friday 18 March 2005 5:31 am, ganapatiram wrote:
> Hi Mr. Steeve,
>
> I really thank for your detailed reply. I worked on the
> four points what you mentioned. I am sure it worked. But when i
> went for testing i saw a different error this time. It says:
>
> org.jboss.util.NestedSQLException: No ManagedConnections available
> within configured blocking timeout ( 1000 [ms] ); - nested
> throwable: (javax.resource.ResourceException: No ManagedConnections
> available within configured blocking timeout ( 1000 [ms] ))
>
> Here i tried by changing the " blocking-timeout-millis" to
> 30000. Even that did not work i am getting the same error. Please
> let me know your suggestion in this regard.
>
> Once again thanks for your reply.
You need to diagnose what is happening and reply to the list with your
findings. In particular, when you get this message you need to check
your PostgreSQL server and see how many connections it is handling.
Naturally you should first make sure the problem app/client can
connect once. If not, there may be a config or coding problem. Make
sure that you can connect from that machine using psql. Note and
report any error messages.
Your postgresql log file may yield clues as well.
When you are having the problem see if you can connect with psql from
the problem machine and from other known good machines. If you can,
the problem is probably somewhere in the code or configuration of
your application or Jboss so you will have to work internally or ask
on the Jboss groups.
If you can't connect, check the number of connections on the PG server
("ps -ef | grep postgres:" works on my server and also shows the IP
of each connecting client). If you are choked with lots of idle
connections you may have to track them down. You can use netstat on
the server to find the client-side port number for each of the
connections and on the clients you can use lsof, fuser or similar to
locate the specific process associated with each connection. That
still leaves you with determining why the connections are there which
will again probably be a question for Jboss or your developers.
Cheers,
Steve