You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Melih Utkan UNSAL <me...@yahoo.com> on 2009/02/22 12:55:08 UTC
[DBCP] getting connection from the pool takes long time
Hi everybody,
i have a problem with dbcp connection pooling.
configuration is like that;
JAVA : 1.4.2
library : commons-dbcp-1.2.2.jar ; commons-pool-1.3.jar
DB : Oracle 9i
datasource is constructed like that;
GenericObjectPool.Config config = new GenericObjectPool.Config();
config.maxActive = -1;
config.minIdle = 50;
config.maxIdle = 10;
config.maxWait = -1;
config.testOnBorrow = true;
config.testOnReturn = true;
config.testWhileIdle = true;
config.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
config.timeBetweenEvictionRunsMillis = -1;
config.minEvictableIdleTimeMillis = -1;
config.softMinEvictableIdleTimeMillis=-1;
config.numTestsPerEvictionRun = -1;
GenericObjectPool connectionPool = new GenericObjectPool(null,config);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(systemConnString, systemUser, systemPassword);
PoolableConnectionFactory
poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory, connectionPool, null,
PooledConnection.VALIDATEQUERY, false, true);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool("example", connectionPool);
when i need a connection, i get it like that;
Connection con = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
when i do not need any more, i just close the connection like that;
con. close
My applicaiton works fine but sometimes, it takes very long time (50000,60000,70000,80000, 90000 MILISECONDS) getting a connection from the pool.
(It happens sometimes not always) And because of that ,performance is not acceptable.
What do you think the problem is?
Thanks in advance
RE: [DBCP] performance and scalability enhancements
Posted by Todd Carmichael <to...@concur.com>.
I will look at writing up a patch for the single evictor thread.
The other reply indicating that a team wrote an addon for marking
connections dead indicates a need for a higher throughput mechanism for
removing bad connections from the pool. Ideally since the DBCP library
has a connection wrapper class, code could be added to all methods in
the wrapper class to mark a connection as dead and not put it back into
the pool.
-----Original Message-----
From: Mark Thomas [mailto:markt@apache.org]
Sent: Tuesday, February 24, 2009 1:43 AM
To: Commons Users List
Subject: Re: [DBCP] performance and scalability enhancements
Todd Carmichael wrote:
> Our site is using DBCP in several different applications. Our site
has
> high scalability requirements and some areas of DBCP and Pooling have
> needed tuning in order to scale. I am soliciting feedback on these
> changes. If the projects DBCP/POOL would/could make use of these
> changes and to present a problem that we still having.
>
> 1) A single eviction thread for cleanup of stale connections. Our
site
> can have hundreds of database connection pools. An evictor thread per
> connection pool puts a strain on the OS.
> 2) testOnBorrow causes far too much overhead on our site and limits
> performance. We have implemented a testOnBorrow after a X
milliseconds.
> Meaning, it will only test the connection if the connection has NOT
been
> tested for X milliseconds.
Both enhancements sound useful. Can you create a Jira entry and attach a
patch?
Cheers,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] performance and scalability enhancements
Posted by Mark Thomas <ma...@apache.org>.
Todd Carmichael wrote:
> Our site is using DBCP in several different applications. Our site has
> high scalability requirements and some areas of DBCP and Pooling have
> needed tuning in order to scale. I am soliciting feedback on these
> changes. If the projects DBCP/POOL would/could make use of these
> changes and to present a problem that we still having.
>
> 1) A single eviction thread for cleanup of stale connections. Our site
> can have hundreds of database connection pools. An evictor thread per
> connection pool puts a strain on the OS.
> 2) testOnBorrow causes far too much overhead on our site and limits
> performance. We have implemented a testOnBorrow after a X milliseconds.
> Meaning, it will only test the connection if the connection has NOT been
> tested for X milliseconds.
Both enhancements sound useful. Can you create a Jira entry and attach a patch?
Cheers,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
RE: [DBCP] performance and scalability enhancements
Posted by Wes Clark <wc...@guidewire.com>.
We've overridden certain parts of DBCP.
We don't run testOnBorrow at all for performance reasons because we
return the connection to the pool if all we're doing is a SELECT, and
only keep the connection if we are planning on doing updates.
If during a rollback, if we catch an exception, we
"markConnectionForDeath". We then override
PoolableConnectionFactory,passivateObject(), removing all the clean-up
that DBCP does every time we return a connection to the pool, and check
to see if the connection has been marked for death. If so, we though an
exception at that point to discard the connection.
We an evictor thread as well, and that should find and evict any bad
connections.
Does this help at all?
-----Original Message-----
From: Todd Carmichael [mailto:toddc@concur.com]
Sent: Monday, February 23, 2009 4:28 PM
To: Commons Users List
Subject: [DBCP] performance and scalability enhancements
Our site is using DBCP in several different applications. Our site has
high scalability requirements and some areas of DBCP and Pooling have
needed tuning in order to scale. I am soliciting feedback on these
changes. If the projects DBCP/POOL would/could make use of these
changes and to present a problem that we still having.
1) A single eviction thread for cleanup of stale connections. Our site
can have hundreds of database connection pools. An evictor thread per
connection pool puts a strain on the OS.
2) testOnBorrow causes far too much overhead on our site and limits
performance. We have implemented a testOnBorrow after a X milliseconds.
Meaning, it will only test the connection if the connection has NOT been
tested for X milliseconds.
The problem we are having is due in part to not using the out of the box
testOnBorrow functionality but can happen other times as well. Our JDBC
driver is throwing a SocketException when network connectivity to the
database server is lost (using MSSQL, issue a kill spid or the Activity
Monitor tool to kill a connection). Look at the following stack.
Feb 10, 2009 5:52:40 PM
com.concur.cup.webservices.xmlhttp.util.ErrorHandler logException
SEVERE:
com.inet.tds.be: java.net.SocketException: Connection reset by peer:
socket write error
at com.inet.tds.al.a(Unknown Source)
at com.inet.tds.n.a(Unknown Source)
at com.inet.tds.n.a(Unknown Source)
at com.inet.tds.n.setAutoCommit(Unknown Source)
at
org.apache.commons.dbcp.cpdsadapter.ConnectionImpl.setAutoCommit(Connect
ionImpl.java:325)
at
com.concur.cup.services.QueueProcessor.ProcessStepPoller.getProcessStepI
nstToProcess(ProcessStepPoller.java:344)
at
com.concur.cup.services.QueueProcessor.ProcessStepPoller.run(ProcessStep
Poller.java:290)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.net.SocketException: Connection reset by peer: socket
write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at
java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at com.inet.tds.g.a(Unknown Source)
... 6 more
The method signature for setAutoCommit does not state that it will throw
a SocketException. And, if I return this connection to the pool (in a
finally block), another bit of code will get a connection in a bad state
that cannot be used.
Any suggestions? Should Every single call to JDBC be wrapped with
something like:
catch(Exception e) { if (e instanceof SocketException) { don't return
connection to pool }}
Has anyone run into something like this?
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
[DBCP] performance and scalability enhancements
Posted by Todd Carmichael <to...@concur.com>.
Our site is using DBCP in several different applications. Our site has
high scalability requirements and some areas of DBCP and Pooling have
needed tuning in order to scale. I am soliciting feedback on these
changes. If the projects DBCP/POOL would/could make use of these
changes and to present a problem that we still having.
1) A single eviction thread for cleanup of stale connections. Our site
can have hundreds of database connection pools. An evictor thread per
connection pool puts a strain on the OS.
2) testOnBorrow causes far too much overhead on our site and limits
performance. We have implemented a testOnBorrow after a X milliseconds.
Meaning, it will only test the connection if the connection has NOT been
tested for X milliseconds.
The problem we are having is due in part to not using the out of the box
testOnBorrow functionality but can happen other times as well. Our JDBC
driver is throwing a SocketException when network connectivity to the
database server is lost (using MSSQL, issue a kill spid or the Activity
Monitor tool to kill a connection). Look at the following stack.
Feb 10, 2009 5:52:40 PM
com.concur.cup.webservices.xmlhttp.util.ErrorHandler logException
SEVERE:
com.inet.tds.be: java.net.SocketException: Connection reset by peer:
socket write error
at com.inet.tds.al.a(Unknown Source)
at com.inet.tds.n.a(Unknown Source)
at com.inet.tds.n.a(Unknown Source)
at com.inet.tds.n.setAutoCommit(Unknown Source)
at
org.apache.commons.dbcp.cpdsadapter.ConnectionImpl.setAutoCommit(Connect
ionImpl.java:325)
at
com.concur.cup.services.QueueProcessor.ProcessStepPoller.getProcessStepI
nstToProcess(ProcessStepPoller.java:344)
at
com.concur.cup.services.QueueProcessor.ProcessStepPoller.run(ProcessStep
Poller.java:290)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.net.SocketException: Connection reset by peer: socket
write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at
java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at com.inet.tds.g.a(Unknown Source)
... 6 more
The method signature for setAutoCommit does not state that it will throw
a SocketException. And, if I return this connection to the pool (in a
finally block), another bit of code will get a connection in a bad state
that cannot be used.
Any suggestions? Should Every single call to JDBC be wrapped with
something like:
catch(Exception e) { if (e instanceof SocketException) { don't return
connection to pool }}
Has anyone run into something like this?
Todd
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
RE: [DBCP] re-establish severed connections
Posted by Wes Clark <wc...@guidewire.com>.
testWhileIdle=true,
timeBetweenEvictionRunsMillis=5000,
numTestsPerEvictionRun=5,
...
This will cause the idle object evictor thread to wake up every five
second, and test 5 threads with the validation query. We use this in our
application because we were having dead threads being given to the
application, as we don't test on borrow. You may need to set the
minEvictableIdleTimeMillis to something other than the default, which is
30 minutes.
-----Original Message-----
From: John Cartwright [mailto:John.C.Cartwright@noaa.gov]
Sent: Monday, February 23, 2009 8:38 AM
To: Commons Users List
Subject: [DBCP] re-establish severed connections
Hello All,
We've recently installed a firewall that terminates connections that
have not had any traffic for 30 minutes. This has caused problems with
our connection-pooled webapps in tomcat, since the connections in the
pool get severed. I added a validation query, but it seems like the
first request after a period of inactivity hangs and subsequent requests
work normally.
I don't see any kind of "keepalive" setting for the pool - can someone
suggest a better strategy or settings to get around this problem? I'm
using Oracle 10.2 and tomcat 6.0.18. Here's my current configuration
w/in the context.xml.
Thanks for any suggestions!
--john
<Resource name="jdbc/CHEETAH"
type="javax.sql.DataSource"
auth="Container"
username="dbuser"
password="dbpasswd"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@myhost:1521:CHEETAH"
maxActive="8"
maxIdle="4"
validationQuery="select 1 from dual" />
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] re-establish severed connections
Posted by Mark Thomas <ma...@apache.org>.
John Cartwright wrote:
>
>
> Mark Thomas wrote:
>> The validation query on its own does nothing. Have you tried setting
>> testOnBorrow="true" ? That may be sufficient if you can live with a
>> slight delay
>> if a broken connection needs to be refreshed.
>>
>> Mark
>>
> Thanks Mark, I have the "testWhileIdle=true" which I understood would
> run the validationQuery when the evictor thread fires up - is that
> correct?
Correct.
> In limited testing, DBCP in this configuration seems to even
> re-establish connections after a database restart. Any further advice
> that you can offer in dealing w/ that situation?
I'd use both. The evictor thread might not catch all the broken connections in
the pool before you try and borrow one. testOnBorrow will ensure you don't give
you app a broken connection while testOnIdle will ensure that you rarely get a
delay on borrow due to having to fix a broken connection.
Mark
>
> Listed below is my current configuration which seems to be working.
> Many thanks for your help!
>
> --john
>
> <Resource name="jdbc/CHEETAH"
> type="javax.sql.DataSource"
> auth="Container"
> username="user"
> password="passwd"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@hostname:1521:CHEETAH"
> maxActive="8"
> maxIdle="4"
> validationQuery="select 1 from dual"
> testWhileIdle="true"
> timeBetweenEvictionRunsMillis="300000"
> minEvictableIdleTimeMillis="600000"/>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] re-establish severed connections
Posted by John Cartwright <Jo...@noaa.gov>.
Mark Thomas wrote:
> The validation query on its own does nothing. Have you tried setting
> testOnBorrow="true" ? That may be sufficient if you can live with a slight delay
> if a broken connection needs to be refreshed.
>
> Mark
>
Thanks Mark, I have the "testWhileIdle=true" which I understood would
run the validationQuery when the evictor thread fires up - is that
correct? In limited testing, DBCP in this configuration seems to even
re-establish connections after a database restart. Any further advice
that you can offer in dealing w/ that situation?
Listed below is my current configuration which seems to be working.
Many thanks for your help!
--john
<Resource name="jdbc/CHEETAH"
type="javax.sql.DataSource"
auth="Container"
username="user"
password="passwd"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@hostname:1521:CHEETAH"
maxActive="8"
maxIdle="4"
validationQuery="select 1 from dual"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="300000"
minEvictableIdleTimeMillis="600000"/>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] re-establish severed connections
Posted by Mark Thomas <ma...@apache.org>.
John Cartwright wrote:
> I've added the following to the configuration:
>
> testWhileIdle="true"
> timeBetweenEvictionRunsMillis="300000"
> minEvictableIdleTimeMillis="600000"
>
> with the intention that any idle connections would be evicted and closed
> normally before the firewall terminated them. Does specifying these
> enable the evictor thread? Does this seem like a reasonable approach?
The validation query on its own does nothing. Have you tried setting
testOnBorrow="true" ? That may be sufficient if you can live with a slight delay
if a broken connection needs to be refreshed.
Mark
>
> Thanks!
>
> --john
>
> John Cartwright wrote:
>> Hello All,
>>
>> We've recently installed a firewall that terminates connections that
>> have not had any traffic for 30 minutes. This has caused problems
>> with our connection-pooled webapps in tomcat, since the connections in
>> the pool get severed. I added a validation query, but it seems like
>> the first request after a period of inactivity hangs and subsequent
>> requests work normally.
>>
>> I don't see any kind of "keepalive" setting for the pool - can someone
>> suggest a better strategy or settings to get around this problem? I'm
>> using Oracle 10.2 and tomcat 6.0.18. Here's my current configuration
>> w/in the context.xml.
>>
>> Thanks for any suggestions!
>>
>> --john
>>
>> <Resource name="jdbc/CHEETAH"
>> type="javax.sql.DataSource"
>> auth="Container"
>> username="dbuser"
>> password="dbpasswd"
>> driverClassName="oracle.jdbc.OracleDriver"
>> url="jdbc:oracle:thin:@myhost:1521:CHEETAH"
>> maxActive="8"
>> maxIdle="4"
>> validationQuery="select 1 from dual" />
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] re-establish severed connections
Posted by John Cartwright <Jo...@noaa.gov>.
I've added the following to the configuration:
testWhileIdle="true"
timeBetweenEvictionRunsMillis="300000"
minEvictableIdleTimeMillis="600000"
with the intention that any idle connections would be evicted and closed
normally before the firewall terminated them. Does specifying these
enable the evictor thread? Does this seem like a reasonable approach?
Thanks!
--john
John Cartwright wrote:
> Hello All,
>
> We've recently installed a firewall that terminates connections that
> have not had any traffic for 30 minutes. This has caused problems
> with our connection-pooled webapps in tomcat, since the connections in
> the pool get severed. I added a validation query, but it seems like
> the first request after a period of inactivity hangs and subsequent
> requests work normally.
>
> I don't see any kind of "keepalive" setting for the pool - can someone
> suggest a better strategy or settings to get around this problem? I'm
> using Oracle 10.2 and tomcat 6.0.18. Here's my current configuration
> w/in the context.xml.
>
> Thanks for any suggestions!
>
> --john
>
> <Resource name="jdbc/CHEETAH"
> type="javax.sql.DataSource"
> auth="Container"
> username="dbuser"
> password="dbpasswd"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@myhost:1521:CHEETAH"
> maxActive="8"
> maxIdle="4"
> validationQuery="select 1 from dual" />
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
[DBCP] re-establish severed connections
Posted by John Cartwright <Jo...@noaa.gov>.
Hello All,
We've recently installed a firewall that terminates connections that
have not had any traffic for 30 minutes. This has caused problems with
our connection-pooled webapps in tomcat, since the connections in the
pool get severed. I added a validation query, but it seems like the
first request after a period of inactivity hangs and subsequent requests
work normally.
I don't see any kind of "keepalive" setting for the pool - can someone
suggest a better strategy or settings to get around this problem? I'm
using Oracle 10.2 and tomcat 6.0.18. Here's my current configuration
w/in the context.xml.
Thanks for any suggestions!
--john
<Resource name="jdbc/CHEETAH"
type="javax.sql.DataSource"
auth="Container"
username="dbuser"
password="dbpasswd"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@myhost:1521:CHEETAH"
maxActive="8"
maxIdle="4"
validationQuery="select 1 from dual" />
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] getting connection from the pool takes long time
Posted by Mark Thomas <ma...@apache.org>.
Melih Utkan UNSAL wrote:
> Thanks Mark
>
> By the way, i made a mistake when i wrote the mail.
>
> my configuration is like that;
>
> config.minIdle = 10;
> config.maxIdle = 50;
OK - that makes sense.
> and PooledConnection.VALIDATEQUERY = "select col1 from table1"
"SELECT 1 FROM DUAL" might be a little quicker.
> However, i am anxious about enabling the evictor because it may cause performance problem. Am i right?
It will depend on your settings. I would expect the following to be safe
for most systems:
timeBetweenEvictionRunsMillis=60000 (1 mins)
numTestsPerEvictionRun=3
minEvictableIdleTimeMillis=600000 (10 mins)
The only way you'll know for sure is to try it.
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] getting connection from the pool takes long time
Posted by Melih Utkan UNSAL <me...@yahoo.com>.
Thanks Mark
By the way, i made a mistake when i wrote the mail.
my configuration is like that;
config.minIdle = 10;
config.maxIdle = 50;
and PooledConnection.VALIDATEQUERY = "select col1 from table1"
However, i am anxious about enabling the evictor because it may cause performance problem. Am i right?
________________________________
From: Mark Thomas <ma...@apache.org>
To: Commons Users List <us...@commons.apache.org>
Sent: Sunday, February 22, 2009 3:52:44 PM
Subject: Re: [DBCP] getting connection from the pool takes long time
Melih Utkan UNSAL wrote:
> GenericObjectPool.Config config = new GenericObjectPool.Config();
> config.maxActive = -1;
> config.minIdle = 50;
> config.maxIdle = 10;
minIdle > maxIdle doesn't make sense
With no evictor, these have no effect
> config.maxWait = -1;
> config.testOnBorrow = true;
> config.testOnReturn = true;
> config.testWhileIdle = true;
With no evictor, this has no effect
> config.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
No need for this - pool is infinite
> config.timeBetweenEvictionRunsMillis = -1;
> config.minEvictableIdleTimeMillis = -1;
> config.softMinEvictableIdleTimeMillis=-1;
> config.numTestsPerEvictionRun = -1;
No need for these, there is no evictor
> GenericObjectPool connectionPool = new GenericObjectPool(null,config);
> ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(systemConnString, systemUser, systemPassword);
> PoolableConnectionFactory
> poolableConnectionFactory = new
> PoolableConnectionFactory(connectionFactory, connectionPool, null,
> PooledConnection.VALIDATEQUERY, false, true);
What is PooledConnection.VALIDATEQUERY
> Class.forName("org.apache.commons.dbcp.PoolingDriver");
> PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
> driver.registerPool("example", connectionPool);
>
> when i need a connection, i get it like that;
>
> Connection con = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
>
> when i do not need any more, i just close the connection like that;
>
> con. close
>
>
> My applicaiton works fine but sometimes, it takes very long time (50000,60000,70000,80000, 90000 MILISECONDS) getting a connection from the pool.
> (It happens sometimes not always) And because of that ,performance is not acceptable.
>
> What do you think the problem is?
Could be many things. My guess would be either -
- a connection leak is the root cause and the symptom you are seeing is
as a result of hitting some connection / cursor limit in Oracle.
- you are receiving connections that have been idle for so long, Oracle
has closed then and the delay you are seeing is the timeout on the
validation query
I would suggest the following:
- clean up your config
- enable the evictor thread
- monitor current connections in Oracle
- consider limiting the size of your connection pool
- test with the abandoned object pool to see if you have any connection
leaks
- Add some logging to your app to record size of the pool / connections
in use and check it agrees with Oracle
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] getting connection from the pool takes long time
Posted by Mark Thomas <ma...@apache.org>.
Melih Utkan UNSAL wrote:
> GenericObjectPool.Config config = new GenericObjectPool.Config();
> config.maxActive = -1;
> config.minIdle = 50;
> config.maxIdle = 10;
minIdle > maxIdle doesn't make sense
With no evictor, these have no effect
> config.maxWait = -1;
> config.testOnBorrow = true;
> config.testOnReturn = true;
> config.testWhileIdle = true;
With no evictor, this has no effect
> config.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
No need for this - pool is infinite
> config.timeBetweenEvictionRunsMillis = -1;
> config.minEvictableIdleTimeMillis = -1;
> config.softMinEvictableIdleTimeMillis=-1;
> config.numTestsPerEvictionRun = -1;
No need for these, there is no evictor
> GenericObjectPool connectionPool = new GenericObjectPool(null,config);
> ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(systemConnString, systemUser, systemPassword);
> PoolableConnectionFactory
> poolableConnectionFactory = new
> PoolableConnectionFactory(connectionFactory, connectionPool, null,
> PooledConnection.VALIDATEQUERY, false, true);
What is PooledConnection.VALIDATEQUERY
> Class.forName("org.apache.commons.dbcp.PoolingDriver");
> PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
> driver.registerPool("example", connectionPool);
>
> when i need a connection, i get it like that;
>
> Connection con = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
>
> when i do not need any more, i just close the connection like that;
>
> con. close
>
>
> My applicaiton works fine but sometimes, it takes very long time (50000,60000,70000,80000, 90000 MILISECONDS) getting a connection from the pool.
> (It happens sometimes not always) And because of that ,performance is not acceptable.
>
> What do you think the problem is?
Could be many things. My guess would be either -
- a connection leak is the root cause and the symptom you are seeing is
as a result of hitting some connection / cursor limit in Oracle.
- you are receiving connections that have been idle for so long, Oracle
has closed then and the delay you are seeing is the timeout on the
validation query
I would suggest the following:
- clean up your config
- enable the evictor thread
- monitor current connections in Oracle
- consider limiting the size of your connection pool
- test with the abandoned object pool to see if you have any connection
leaks
- Add some logging to your app to record size of the pool / connections
in use and check it agrees with Oracle
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org