From ganapatiram@symphonydata.com Mon Mar 7 13:12:44 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 317DF563FB for ; Mon, 7 Mar 2005 13:12:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51751-01 for ; Mon, 7 Mar 2005 13:12:35 +0000 (GMT) Received: from rs7k.tempdomainname.com (rs7k.tempdomainname.com [198.173.255.221]) by svr1.postgresql.org (Postfix) with ESMTP id DC6A3564A5 for ; Mon, 7 Mar 2005 13:12:20 +0000 (GMT) Received: from rram ([220.227.243.250]) (authenticated bits=0) by rs7k.tempdomainname.com (8.13.1/8.12.6) with ESMTP id j27DCLDY044574 for ; Mon, 7 Mar 2005 13:12:23 GMT (envelope-from ganapatiram@symphonydata.com) Message-ID: <003101c52316$bd8e4f00$8c0aa8c0@rram> Reply-To: "ganapatiram" From: "ganapatiram" To: Subject: Too many clients----A big problem for my team Date: Mon, 7 Mar 2005 18:38:15 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C52344.D2BB0F70" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.014 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/108 X-Sequence-Number: 16606 This is a multi-part message in MIME format. ------=_NextPart_000_002E_01C52344.D2BB0F70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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.=20 I have max_connections=3D200 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.=20 Waiting for your valuable suggestion.......... Thanks and Regards Ram ------=_NextPart_000_002E_01C52344.D2BB0F70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Experts,
 
           I = work with=20 postgresql 8.0 version. I have a team of 14 developers. When all the = team=20 connects the DB i see this error very very frequently which is a severe = problem=20 for me and team all the day. The error is:
 

Please report this exception: java.sql.SQLException: = FATAL:  sorry, too many clients=20 already

 

when i=20 do trial and error changes i see this error differently but the essense = of the=20 error remains same.

 

   =20     I have max_connections=3D200 and i have 5 = users for my=20 DB. I dont understand what to do in order to resolve this. Please = suggest me=20 something by which i can overcome this problem. I referred few threads = but none=20 of them are of my case.

 

 

Waiting=20 for your valuable suggestion..........

 

 

 

 

Thanks=20 and Regards

Ram

------=_NextPart_000_002E_01C52344.D2BB0F70-- From tfo@sitening.com Tue Mar 8 05:41:44 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9D4885653D for ; Tue, 8 Mar 2005 05:41:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50430-01 for ; Tue, 8 Mar 2005 05:41:34 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 4F3E8563EF for ; Tue, 8 Mar 2005 05:41:33 +0000 (GMT) Received: (qmail 22734 invoked from network); 8 Mar 2005 05:41:32 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 8 Mar 2005 05:41:32 -0000 In-Reply-To: <003101c52316$bd8e4f00$8c0aa8c0@rram> References: <003101c52316$bd8e4f00$8c0aa8c0@rram> Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-Id: <93dd2f9da9397187a135f96b5f607c75@sitening.com> Content-Transfer-Encoding: quoted-printable Cc: From: Thomas F.O'Connell Subject: Re: Too many clients----A big problem for my team Date: Mon, 7 Mar 2005 23:41:31 -0600 To: "ganapatiram" X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/111 X-Sequence-Number: 16609 Well, first of all, max_connections can only be set at server start.=20 Are you restarting postgres when you do "trial and error changes"? Secondly, 5 users can still manage to make multiple connections. You=20 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, > =A0 > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 I work with postgresql 8.0 version. I = have a team of 14=20 > developers. When all the team connects the DB i see this error very=20 > very frequently which is a severe problem for me and team all the day.=20= > The error is: > =A0 > > Please report this exception: java.sql.SQLException: FATAL:=A0 sorry,=20= > too many clients already > > =A0 > > when i do trial and error changes i see this error differently but the=20= > essense of the error remains same. > > =A0 > > =A0=A0=A0 =A0=A0=A0 I have max_connections=3D200 and i have 5 users = for my DB. I=20 > dont understand what to do in order to resolve this. Please suggest me=20= > something by which i can overcome this problem. I referred few threads=20= > but none of them are of my case. > > =A0 > > =A0 > > Waiting for your valuable suggestion.......... > > =A0 > > =A0 > > =A0 > > =A0 > > Thanks and Regards > > Ram From scrawford@pinpointresearch.com Tue Mar 8 16:49:38 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5A38852A8E for ; Tue, 8 Mar 2005 16:49:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27686-07 for ; Tue, 8 Mar 2005 16:49:21 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 247CC52A55 for ; Tue, 8 Mar 2005 16:49:19 +0000 (GMT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id E9D261038E; Tue, 8 Mar 2005 08:49:18 -0800 (PST) From: Steve Crawford Organization: Pinpoint Research To: "ganapatiram" , Subject: Re: Too many clients----A big problem for my team Date: Tue, 8 Mar 2005 08:49:18 -0800 User-Agent: KMail/1.5.1 References: <003101c52316$bd8e4f00$8c0aa8c0@rram> In-Reply-To: <003101c52316$bd8e4f00$8c0aa8c0@rram> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503080849.18471.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.115 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/115 X-Sequence-Number: 16613 > 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 From drubior@tinet.org Wed Mar 9 11:36:05 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 93CA15643D for ; Wed, 9 Mar 2005 11:36:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79797-07 for ; Wed, 9 Mar 2005 11:36:04 +0000 (GMT) Received: from tinet.org (mail.tinet.org [195.77.216.130]) by svr1.postgresql.org (Postfix) with ESMTP id C5ADA563F6 for ; Wed, 9 Mar 2005 11:36:02 +0000 (GMT) Received: (qmail 4123 invoked from network); 9 Mar 2005 11:36:03 -0000 Received: from serena.tinet.org (HELO tinet.org) ([194.224.210.4]) (envelope-sender ) by mail.tinet.org (SMTP OASI) with SMTP for ; 9 Mar 2005 11:36:03 -0000 Message-ID: <422EDF21.4080903@tinet.org> Date: Wed, 09 Mar 2005 12:33:53 +0100 From: Daniel Rubio User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Too many clients----A big problem for my team References: <003101c52316$bd8e4f00$8c0aa8c0@rram> In-Reply-To: <003101c52316$bd8e4f00$8c0aa8c0@rram> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/123 X-Sequence-Number: 16621 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 ******************************************************** From scrawford@pinpointresearch.com Fri Mar 18 16:24:14 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B734254EAA for ; Fri, 18 Mar 2005 16:24:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77975-10 for ; Fri, 18 Mar 2005 16:24:11 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 949E553C91 for ; Fri, 18 Mar 2005 16:24:10 +0000 (GMT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 0214A10390; Fri, 18 Mar 2005 08:24:09 -0800 (PST) From: Steve Crawford Organization: Pinpoint Research To: pgsql-admin@postgresql.org Subject: Re: Too many clients----A big problem for my team Date: Fri, 18 Mar 2005 08:24:09 -0800 User-Agent: KMail/1.5.1 References: <003101c52316$bd8e4f00$8c0aa8c0@rram> <200503080849.18471.scrawford@pinpointresearch.com> <001a01c52bbe$dac9dc50$8c0aa8c0@rram> In-Reply-To: <001a01c52bbe$dac9dc50$8c0aa8c0@rram> Cc: "ganapatiram" MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503180824.09613.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.092 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/288 X-Sequence-Number: 16786 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