Thread: too many clients
What does this mean and how do I diagnose it? java.sql.SQLException: FATAL: Sorry, too many clients already Not all the Postgres databases on this machine are experiencing this. One of them is using pooling with Tomcat and the JNDI interface. -- ____________________________________________________________ Glenn Holmer gholmer@weycogroup.com Programmer/Analyst phone: 414.908.1809 Weyco Group, Inc. fax: 414.908.1601
On 04/11/2003 13:34 Glenn Holmer wrote: > What does this mean and how do I diagnose it? > > java.sql.SQLException: FATAL: Sorry, too many clients already > > Not all the Postgres databases on this machine are experiencing this. > One of them is using pooling with Tomcat and the JNDI interface. > You've probably reached max_connections. Try upping the value in your postgresql.conf. You'll need to restart the back-end for it to take effect. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
You have exceeded your connection limit to the database. I believe it defaults to 64 in most distributions. This is a general administration issue- not specific to JDBC. All database clients, such as psql, pg_dump, etc... draw from the same limited resource. Your pooling applications are probably OK because they initially create a set of connections that are never released- simply pooled & re-used. You may be able to bring temporary relief by increasing your connection limit in postgresql.conf, but the long term fix involves managing the connections via pooling or other resource management schemes. -Nick > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Glenn Holmer > Sent: Tuesday, November 04, 2003 8:35 AM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] too many clients > > > What does this mean and how do I diagnose it? > > java.sql.SQLException: FATAL: Sorry, too many clients already > > Not all the Postgres databases on this machine are experiencing this. > One of them is using pooling with Tomcat and the JNDI interface. > > -- > ____________________________________________________________ > Glenn Holmer gholmer@weycogroup.com > Programmer/Analyst phone: 414.908.1809 > Weyco Group, Inc. fax: 414.908.1601 > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > >
Paul Thomas wrote: > > On 04/11/2003 13:34 Glenn Holmer wrote: > >> What does this mean and how do I diagnose it? >> >> java.sql.SQLException: FATAL: Sorry, too many clients already >> >> Not all the Postgres databases on this machine are experiencing this. >> One of them is using pooling with Tomcat and the JNDI interface. >> > > You've probably reached max_connections. Try upping the value in your > postgresql.conf. You'll need to restart the back-end for it to take effect. This is on SuSE 8.2, which provides a file /etc/sysconfig/postgresql; this is where we put "-i" for TCP/IP. Since that's a postmaster flag, I'm probably OK with putting the max_connections here as well? Looks like it would be, e.g. "-i -N 64 -B 128"? (space after -N and -B?) There are a lot more Postgres users on this machine lately (some new apps). A long time ago, we used to get "hash table out of memory. Use -B parameter to increase buffers"; that param was never added when we upgraded the machine last summer. -- ____________________________________________________________ Glenn Holmer gholmer@weycogroup.com Programmer/Analyst phone: 414.908.1809 Weyco Group, Inc. fax: 414.908.1601
On 04/11/2003 15:03 Glenn Holmer wrote: > Paul Thomas wrote: >> >> On 04/11/2003 13:34 Glenn Holmer wrote: >> >>> What does this mean and how do I diagnose it? >>> >>> java.sql.SQLException: FATAL: Sorry, too many clients already >>> >>> Not all the Postgres databases on this machine are experiencing this. >>> One of them is using pooling with Tomcat and the JNDI interface. >>> >> >> You've probably reached max_connections. Try upping the value in your >> postgresql.conf. You'll need to restart the back-end for it to take >> effect. > > This is on SuSE 8.2, which provides a file /etc/sysconfig/postgresql; > this is where we put "-i" for TCP/IP. Since that's a postmaster flag, > I'm probably OK with putting the max_connections here as well? Looks > like it would be, e.g. "-i -N 64 -B 128"? (space after -N and -B?) I don't use Suse but I'd guess that the file contains command-line parameters to the postmaster so you should be OK. Unless your machine has only got about 16-MB RAM, I'd increase buffers (-B) to at least 1000 for performance reasons and max_connections (-N) to 100. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+