Thread: Exception in DriverManager.getConnection creates multiple connections in SYN_SEND state
Exception in DriverManager.getConnection creates multiple connections in SYN_SEND state
Re: Exception in DriverManager.getConnection creates multiple connections in SYN_SEND state
Dear Emanuel,
You should try with JDBC Connection Pooling. There are great tutorials are available. Using Connection Pool generally improves performance by 3x.
From: Emanuel Freitas [mailto:ejsfreitas@gmail.com]
Sent: Friday, October 29, 2010 7:52 PM
To: pgsql-jdbc@postgresql.org
Subject: Exception in DriverManager.getConnection creates multiple connections in SYN_SEND state
Hello,
I'm running a server that receives approximately 300 requests per second. For each request I need to do some operations in a database. So, when I receive a request i check if there is any active connection to the database and if not i create one. The code for create the connection is something like this:
private Connection initConn() {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException cnfe) {
System.err.println("Error on Driver");
return null;
}
try {
Connection db = DriverManager.getConnection("jdbc:postgresql://192.168.1.2:5432/test?loginTimeout=3", "test", "1234");
return db;
} catch (SQLException sqle) {
System.err.println("Error on Connection");
return null;
}
}
If the database host is accessible the getConnection method works and I return the connection, on the other hand, if for some reason the host is not accessible I get a SQLException after 3 seconds (loginTimeout). That's exactly what i want to do.
The problem is, for each time it executes the "getConnection" method one connection to the database is created and if the host is not accessible the connection stays in SYN_SENT state for alot of time...
As I receive alot of requests per second these connections are created until it reaches the max connections allowed by the SO (linux).
Can you tell me what I'm doing wrong or what can I do to prevent this?
Thank You!
Re: Exception in DriverManager.getConnection creates multiple connections in SYN_SEND state
Emanuel Freitas wrote:
> I'm running a server that receives approximately 300 requests per
> second. For each request I need to do some operations in a database. So,
> when I receive a request i [sic] check if there is any active connection to
> the database and if not i [sic] create one. The code for create the connection
> is something like this:
>
> private Connection initConn() {
>
> try {
> Class.forName("org.postgresql.Driver");
You only need to load the driver class once. Load it in a static initializer
block.
> } catch (ClassNotFoundException cnfe) {
> System.err.println("Error on Driver");
Consider using a logging framework like log4j or java.util.logging.
> return null;
Consider stopping the application for this particular exception.
> }
>
> try {
>
> Connection db =
> DriverManager.getConnection("jdbc:postgresql://192.168.1.2:5432/test?loginTimeout=3
> http://192.168.1.2:5432/test?loginTimeout=3", "test", "1234");
Consider externalizing the connection details. Try persistence.xml,
context.xml or an application server's setup script/screen to create a data
source.
> return db;
>
> } catch (SQLException sqle) {
> System.err.println("Error on Connection");
> return null;
You might want to log more details (again, using a logger) to troubleshoot
this scenario. One candidate detail is the SQLState.
> }
> }
--
Lew