You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by John Jason Brzozowski <jj...@gmail.com> on 2008/08/05 15:04:56 UTC
Apache DBCP Question
Hello I am using the following code for database connection pooling using
Apache DBCP.
Here is the routine I use to setup the pool so:
public static void setupDriver(String connectURI) throws Exception {
logger.trace("Creating connection pool using URI, " + connectURI);
Config gkopc = new Config();
gkopc.maxActive = 10;
gkopc.maxIdle = 10;
gkopc.maxWait = 2000;
gkopc.whenExhaustedAction = 2;
gkopc.testOnBorrow = false;
gkopc.testOnReturn = false;
gkopc.timeBetweenEvictionRunsMillis = 10000;
gkopc.numTestsPerEvictionRun = 5;
gkopc.minEvictableIdleTimeMillis = 5000;
gkopc.testWhileIdle = true;
gkopc.maxTotal = 10;
logger.trace("Generic Keyed Object Pool Config = " +
gkopc.toString());
connectionPool = new GenericObjectPool(null);
DriverManagerConnectionFactory connFactory = new
DriverManagerConnectionFactory(mDbUrl, mDbUser, mDbPass);
KeyedObjectPoolFactory kopf = new
GenericKeyedObjectPoolFactory(null, gkopc);
logger.trace("Keyed Object Pool Factory = " + kopf.toString());
PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(connFactory,connectionPool,kopf,"select count(*)
from dual",false,true);
logger.trace("Poolable Connection Factory = " +
poolableConnectionFactory.toString());
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver)
DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool("foo",connectionPool);
logger.trace("Driver = " + driver.toString());
logger.trace("Initial connection pool idle connections, " +
connectionPool.getNumIdle() + ", active connections, " +
connectionPool.getNumActive());
}
Here is how I call it:
try {
logger.info("Setting up driver for, " + completeMDbUrl);
setupDriver(completeMDbUrl);
} catch (Exception e) {
logger.fatal(e);
e.printStackTrace();
}
Here is some output from the routing itself:
TRACE [main] (?:?) - Initial connection pool idle connections, 0, active
connections, 0
Zero connections...however, once I issue the following as I would with
typical JDBC:
try {
logger.info("Loading MySQL driver");
Class.forName("com.mysql.jdbc.Driver");
logger.debug("Connecting to " + mDbUrl + " as " + mDbUser +
"/(Password not displayed)");
mconn =
DriverManager.getConnection("jdbc:apache:commons:dbcp:foo");
if(!mconn.isClosed()) {
logger.trace("Connected to MySQL database");
}
...
}
}
} catch (Exception e) {
logger.fatal(e);
e.printStackTrace();
}
When I print the number of connections active and idle I get the following:
TRACE [main] (?:?) - Connection pool idle connections, 0, active
connections, 1
The number of active connections never increases past one. I thought the
following would cause 10 connections to be created:
gkopc.maxActive = 10;
Any comments on what I missing would be greatly appreciated. To provide
additional context I wish to use connection pooling when making large
quantities of database updates. Most examples I see are for SELECTs not
INSERTs.
Thanks,
John
--
===================================
John Jason Brzozowski
===================================
Re: Apache DBCP Question
Posted by John Jason Brzozowski <jj...@gmail.com>.
Thanks for replying Simon. I also tried something else that appears to have
provided some benefit. I proceeded to create a connection from the pool for
specific uses, one for query type A, one for query type B, and one for
inserts. Three connections were created and things seem to be behaving,
however, not ideal.
Originally, I intended to leverage the pools to increase the performance of
updates. So in the case you describe below I can create a pool of "n"
connections. Assuming a threaded application each thread (assuming these
are for updates in this case) uses a separate connection I need to make sure
the number of threads performing inserts/updates does not exceed the number
of available connections. If this happens then a connection needs to be
freed up to allow subsequent threads or updates to be processed.
Thanks again,
John
On Wed, Aug 6, 2008 at 5:13 AM, simon.kitching@chello.at <
simon.kitching@chello.at> wrote:
> John Jason Brzozowski schrieb:
>
>> try {
>>
>> logger.info("Loading MySQL driver");
>> Class.forName("com.mysql.jdbc.Driver");
>> logger.debug("Connecting to " + mDbUrl + " as " + mDbUser +
>> "/(Password not displayed)");
>> mconn =
>> DriverManager.getConnection("jdbc:apache:commons:dbcp:foo");
>> if(!mconn.isClosed()) {
>> logger.trace("Connected to MySQL database");
>> }
>> ...
>> }
>> }
>> } catch (Exception e) {
>> logger.fatal(e);
>> e.printStackTrace();
>> }
>>
>> When I print the number of connections active and idle I get the
>> following:
>>
>> TRACE [main] (?:?) - Connection pool idle connections, 0, active
>> connections, 1
>>
>> The number of active connections never increases past one. I thought the
>> following would cause 10 connections to be created:
>>
>> gkopc.maxActive = 10;
>>
>> Any comments on what I missing would be greatly appreciated. To provide
>> additional context I wish to use connection pooling when making large
>> quantities of database updates. Most examples I see are for SELECTs not
>> INSERTs.
>>
>>
> It's been a long time since I've used DBCP. But as no-one has answered,
> I'll offer a suggestion.
>
> That maxActive looks like a *maximum* setting.
>
> Try this:
> Connection[] conns = new Connection[20];
> for(int i=0; i<8; ++i) {
> conns[i] = DriverManager.getConnection(....);
> }
> then dump the output.
>
> I would expect that then you will see 8 active connections reported.
>
> And if you increate the loop to > 10, then it will probably hang, as the
> attempt to fetch the 11th connection will block until someone returns one of
> the existing ones. Which would happen in a threaded app, but won't happen
> here in a single-threaded test.
>
> Regards, Simon
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
--
===================================
John Jason Brzozowski
===================================
Re: Apache DBCP Question
Posted by "simon.kitching@chello.at" <si...@chello.at>.
John Jason Brzozowski schrieb:
> try {
>
> logger.info("Loading MySQL driver");
> Class.forName("com.mysql.jdbc.Driver");
> logger.debug("Connecting to " + mDbUrl + " as " + mDbUser +
> "/(Password not displayed)");
> mconn =
> DriverManager.getConnection("jdbc:apache:commons:dbcp:foo");
> if(!mconn.isClosed()) {
> logger.trace("Connected to MySQL database");
> }
> ...
> }
> }
> } catch (Exception e) {
> logger.fatal(e);
> e.printStackTrace();
> }
>
> When I print the number of connections active and idle I get the following:
>
> TRACE [main] (?:?) - Connection pool idle connections, 0, active
> connections, 1
>
> The number of active connections never increases past one. I thought the
> following would cause 10 connections to be created:
>
> gkopc.maxActive = 10;
>
> Any comments on what I missing would be greatly appreciated. To provide
> additional context I wish to use connection pooling when making large
> quantities of database updates. Most examples I see are for SELECTs not
> INSERTs.
>
It's been a long time since I've used DBCP. But as no-one has answered,
I'll offer a suggestion.
That maxActive looks like a *maximum* setting.
Try this:
Connection[] conns = new Connection[20];
for(int i=0; i<8; ++i) {
conns[i] = DriverManager.getConnection(....);
}
then dump the output.
I would expect that then you will see 8 active connections reported.
And if you increate the loop to > 10, then it will probably hang, as the
attempt to fetch the 11th connection will block until someone returns
one of the existing ones. Which would happen in a threaded app, but
won't happen here in a single-threaded test.
Regards, Simon
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org