-
1. Re: Getting DB connections
hey,
the 'recommended' way is to get the connection from the pool when you need it and put it back as soon as you don't need it anymore.
The number of concurrent connections is best kept to a minimum to be able to serve the largest number of concurrent users with the smallest RAM footprint.
I know this is not important for MySQL but for commercial databases like Oracle and DB2 the license price is determined by the number of concurrent connections.
Werner -
2. Re: Getting DB connections
Yeah getting NEW connections is expensive, that is the whole point of pooling them.
Presumably, you are not giving back the connections to the pool, so everytime you get a connection from the pool, it actually has to open a new one to the db.
The recommended way is:
just before you need a connection (like, the line before creating the statement), get the connection from the pool. As soon as you're done processing the answer from the db, release the connection to the pool, by calling close()
I just checked our application (using oracle connections), getting a connection the first time costs 0.54 s, but getting a connection from the pool which has already been set up, costs only 0.04s - and the includes all the debug logging which probably accounts for most of that time ;) -
3. Re: Getting DB connections
Oh yeah I almost forgot this important issue too:
be sure to write a finally {} clause around all your sql stuff. If an exception occurs, release all resources, including connection, if they're set !!
Otherwise for any old sql error, you will have stuff lying around until some system cleanup, and you don't want to rely on that if there is heavy usage. -
4. Re: Getting DB connections
Thanks for the response chaps.
I'm a bit worried here guys because you are concurring with my view of how to get and release connections, but I *think* my code reflects recommendations. However when we set connection pool size to min 500 max 1000 and then repeatedly get/put from the pool operation times drop from the order of 17secs to 2secs under load!
I've put the 'get' code below and also the jboss.jcml settings. Is there something obvious that I haven't seen?
Many many thanks
Ian
======================================================
// Get connection & use it for a query...
public Records executeQuery(Command comm)
throws Exception {
Records result = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Exception exception = null; // Reference to exception.
try {
conn = _connections.getConnection(); // Uses JNDI to a local EJB that does a connection retrieval from pool
if (conn == null) throw new SQLException("Unable to get a connection");
stmt = conn.createStatement();
///////////////////////////////////
// Execute command here....
rs = stmt.executeQuery(comm.getSql());
result = new Records(rs); // Process results
rs.close();
///////////////////////////////////
// The finally clause closes the connection
// statement
} catch(Exception exc) {
exception = exc;
println("Problem with database access: " + exc.getMessage());
} finally {
// This follows the pattern as
//STRONGLY recommended by the JBoss team...
try {
if (rs != null) rs.close();
} catch(SQLException sqle) {
exception = sqle;
println("Cannot close the results set");
} catch(NullPointerException npex) { }
try {
if (stmt != null) stmt.close();
} catch(SQLException sqle) {
exception = sqle;
println("Cannot close the statement");
} catch(NullPointerException npex) { }
try {
if (conn != null) _connections.releaseConnection(conn); // Simply does conn.close();
} catch(SQLException sqle) {
exception = sqle;
println("Cannot close the connection");
} catch(NullPointerException npex) { }
}
if (exception != null) throw exception;
return(result);
}
=======================================================
org.gjt.mm.mysql.Driver
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
DefaultDS
jdbc:mysql://@DB_SERVER@/teamwarrior
[User]
[Pword]
true
1000
500
true
200000
true
1800000
true
================================================ -
5. Re: Getting DB connections
> conn = _connections.getConnection(); // Uses
> Uses JNDI to a local EJB that does a connection
> retrieval from pool
This one looks suspicious to me. Are you actually creating an ejb everytime you want to get a connection from the pool ? That would be overhead. Also, it's not so clear how the resource retrieved would then be freed.
Maybe the comment is misleading; in any case for what it's worth here's how I get connections:
in a common class, accessible by all beans (a ServiceLocator class, with a static instance of itself) I have a getConnection() method with:
DataSource ds = (DataSource)getResource("java:/" + poolName, DataSource.class);
Connection connection = ds.getConnection();
Now from a bean which needs a connection, a call to ServiceLocator.getConnection() provides a connection within 0.04s (assuming it is already an open connection, meaning one which has already been used).
I'm running JBoss 2.4.4 on a not-so-mighty machine, so you should not have worse lookup times. -
6. Re: Getting DB connections
Mmmm. I briefly questioned this myself.
We set the standard stateless bean config to a minimum way above our requirements. So I *believe* that getting a Stateless Bean should be quick and not impact the performance as much as we see - wrong?
When I have time I'll try the static object approach (are there threading issues with this?)
Many Thanks
Ian -
7. Re: Getting DB connections
Hi
Never, ever try to make you own DBConnection pooling. Whenever you need a new connection create one from the DataSource and close it after you are done.
Andy -
8. Re: Getting DB connections
Hi Andy,
Re-inventing connection pools is not on the agenda here.
Cheers
Ian