You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Purnima Venkatram <pu...@gmail.com> on 2008/03/03 14:36:07 UTC
maxActive/maxWait
Hi all.
I am using commons dbcp through Tomcat.
Configuration:
1) Spring 2.5.1
2) Tomcat 5.5.25
3) mysql 5.0.21.
Have put the commons-dbcp-1.2.2.jar / commonds-pool-1.4.jar /
commonds-logging-1.0.4.jar into tomcat's common/lib.
The dataSource is accessed through a JNDI lookup. I want to check connection
pooling.
For the same I tried the following for the dataSource definition.
In context.xml I have:
<Resource
name="jdbc/XYZ"
auth="Container" type="javax.sql.DataSource"
maxActive="1"
maxWait="0"
maxIdle="0"
validationQuery="SELECT 1"
username="test"
password="test123"
driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
logAbandoned="true"
url="jdbc:mysql://127.0.0.1:3306/abc"/>
Setting maxActive to 1 means that only 1 connection will be allocated at the
same time. Which means that If i establish two concurrent requests to the
application, then the second one should fail. I don't see this happen. I
initially thought that I had very few rows in the db and hence the mysql
operation was finishing quickly due to which the second request was also
getting processed. But putting a loop and executing the mysql statement also
results in both the requests getting served.
I tried this with Jmeter too. I tried a Forever loop with Jmeter. And during
that time maxActive=1 and then from the browser again tried a request to the
jsp and this time I got a 'JDBC exception : Communications link failure' -
Address already in use. If i stop Jmeter tests, an redo the browser test, it
only succeeds after a while. ie the first few runs still fail with the same
exceptions and then it seems like the connections are released and then this
call succeeds. But in this time interval , in mysql 'show processlist' I
dont see the connections.
I have tried this for a lot of time and any suggestions/comments would be
appreciated.
I have checked the source of BasicDataSource and saw that it uses the
ObjectPool and PoolableConnectionFactory. Hence am assuming there isnt
anything else that I need to do to get pooling to work.
I also tried deleting the naming-dbcp jar from tomcat's common lib since
that seemed to have the same classes as commons-dbcp.
Is anything else required ?
In spring this is accessed using getBean and then the corresponding dao
method is called.
Also, as soon as it finishes the operation, the connection is removed from
the 'show processlist' of mysql. Does that mean , Tomcat drops and creates
connections for every call ? Isn't that against connection pooling ? Most of
the time I see the status as 'null' / 'sleep' / 'query' with validatation
query and very less time for the actual select query.
Best Regards,
Purnima Venkatram.
Re: maxActive/maxWait
Posted by Purnima Venkatram <pu...@gmail.com>.
Thanks a lot Phil ! It finally worked with all parameter permutations :).
I tested with maxActive=50 and maxIdle=-1.
With maxWait=1 , for 51 simultaneous requests, it failed for one request.
For subsequent runs, it succeeds.
The same with maxWait=1000, works for 51 connections and goes till the
Tomcat limit of 250 connections. Checked this by increasing Tomcat's limit
of threads.
Also, tomcat 5.5.25 bundles all the necessary functionality in
naming-dbcp.jar (commons-collections,commons-pool,commons-dbcp). Hence,
nothing more is required as a jar. Only in the datasource for factory="
org.apache.commons.dbcp.BasicDataSourceFactory" we need to give
org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory".
I finally set the paramters to maxActive=70 , maxIdle=-1 , maxWait=1000 ,
initialSize=10.
Thanks a lot again for all the help !
Regards,
Purnima.
On Wed, Mar 5, 2008 at 10:48 AM, Phil Steitz <ph...@gmail.com> wrote:
> On Tue, Mar 4, 2008 at 6:09 AM, Purnima Venkatram
> <pu...@gmail.com> wrote:
> > Hi Phil.
> >
> > There still seems to be some problem with maxActive and maxWait.
> >
> > I changed the approach that I was using and used the context lookup
> > mechanism
> >
> > Snippet
> > ==========================================
> >
> > ctx = new InitialContext();
> > if(ctx == null )
> > throw new Exception("Boom - No Context");
> >
> > System.out.println("Context : "+ctx);
> >
> > ds =
> > (DataSource)ctx.lookup(
> > "java:/comp/env/jdbc/XYZ");
> >
> > if (ds != null) {
> > conn = ds.getConnection();
> >
> >
> > if(conn != null) {
> > name = "Got Connection "+conn.toString();
> > Statement stmt = conn.createStatement();
> > ResultSet rst =
> > stmt.executeQuery(
> > "select xyz from abc where xyz=1");
> > if(rst.next()) {
> > id = rst.getLong(1);
> > }
> > conn.close();
> > }
> > }
> >
> > =================================================
> >
> > Get and print this from a jsp.
> >
> > Tested with Jmeter:
> >
> > maxActive=100 maxWait=0 maxIdle=0
>
> Sorry I did not notice this before, but maxIdle = 0 means no object
> can sit idle in the pool, so connections are going to be closed when
> they are returned to the pool. Either set this to a negative value
> for no limit, leave it at the default (8), or set it to some
> reasonable positive number.
>
> IIRC, "show status" from MySql will display the total number of
> threads and also how many connections have been opened.
>
> Phil
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
Re: maxActive/maxWait
Posted by Phil Steitz <ph...@gmail.com>.
On Tue, Mar 4, 2008 at 6:09 AM, Purnima Venkatram
<pu...@gmail.com> wrote:
> Hi Phil.
>
> There still seems to be some problem with maxActive and maxWait.
>
> I changed the approach that I was using and used the context lookup
> mechanism
>
> Snippet
> ==========================================
>
> ctx = new InitialContext();
> if(ctx == null )
> throw new Exception("Boom - No Context");
>
> System.out.println("Context : "+ctx);
>
> ds =
> (DataSource)ctx.lookup(
> "java:/comp/env/jdbc/XYZ");
>
> if (ds != null) {
> conn = ds.getConnection();
>
>
> if(conn != null) {
> name = "Got Connection "+conn.toString();
> Statement stmt = conn.createStatement();
> ResultSet rst =
> stmt.executeQuery(
> "select xyz from abc where xyz=1");
> if(rst.next()) {
> id = rst.getLong(1);
> }
> conn.close();
> }
> }
>
> =================================================
>
> Get and print this from a jsp.
>
> Tested with Jmeter:
>
> maxActive=100 maxWait=0 maxIdle=0
Sorry I did not notice this before, but maxIdle = 0 means no object
can sit idle in the pool, so connections are going to be closed when
they are returned to the pool. Either set this to a negative value
for no limit, leave it at the default (8), or set it to some
reasonable positive number.
IIRC, "show status" from MySql will display the total number of
threads and also how many connections have been opened.
Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: maxActive/maxWait
Posted by Purnima Venkatram <pu...@gmail.com>.
Hi Phil.
There still seems to be some problem with maxActive and maxWait.
I changed the approach that I was using and used the context lookup
mechanism
Snippet
==========================================
ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
System.out.println("Context : "+ctx);
ds =
(DataSource)ctx.lookup(
"java:/comp/env/jdbc/XYZ");
if (ds != null) {
conn = ds.getConnection();
if(conn != null) {
name = "Got Connection "+conn.toString();
Statement stmt = conn.createStatement();
ResultSet rst =
stmt.executeQuery(
"select xyz from abc where xyz=1");
if(rst.next()) {
id = rst.getLong(1);
}
conn.close();
}
}
=================================================
Get and print this from a jsp.
Tested with Jmeter:
maxActive=100 maxWait=0 maxIdle=0
It processes 250 simulataneous requests and then fails for 251st concurrent
request
maxActive=100 maxWait=1 maxIdle=0
Same output
maxActive=10 maxWait=1 maxIdle=0
Same output.
I use these through the same resource defnitions in context.xml
<Resource
> name="jdbc/XYZ"
> auth="Container" type="javax.sql.DataSource"
> maxActive="100"
> maxWait="1"
> maxIdle="0"
> validationQuery="SELECT 1"
> username="test"
> password="test123"
> driverClassName="com.mysql.jdbc.Driver"
> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> logAbandoned="true"
> url="jdbc:mysql://127.0.0.1:3306/abc"/>
>
How can maxActive=10 and maxActive=100 not have a difference.
The expectation is that if I give maxWait=1 and then give maxActive=10 my
11th conneciton should fail. And if my mysql operation were to complete
within the 1 ms for the maxWait then maybe fail around for 50th request or
so.
How can it always fail for the 251st request. Seems like its only using
mysql's 100 connections and some other timeout options.
Can you help me with this ? Do i need to do anything else to get this
working ? Basically since I can't see the mysql processlist connections
Jmeter concurrent requests are the way that I'm testing if the connection
pooling works.
The BasicDataSourceFactory is the only requirement right for connection
pooling and the jars I mentioned ?
commons-dbcp/commons-collections/commons-pool ?
Thanks and Best Regards,
Purnima.
On Mon, Mar 3, 2008 at 9:56 PM, Purnima Venkatram <
purnima.venkatram@gmail.com> wrote:
> Thanks Phil for the prompt reply.
> I'll try this again with maxWait = 1 and check the results and get back.
>
> Regards,
> Purnima
>
>
>
>
> On Mon, Mar 3, 2008 at 7:53 PM, Phil Steitz <ph...@gmail.com> wrote:
>
> > On Mon, Mar 3, 2008 at 6:36 AM, Purnima Venkatram
> > <pu...@gmail.com> wrote:
> > > Hi all.
> > >
> > > I am using commons dbcp through Tomcat.
> > >
> > > Configuration:
> > >
> > > 1) Spring 2.5.1
> > > 2) Tomcat 5.5.25
> > > 3) mysql 5.0.21.
> > >
> > > Have put the commons-dbcp-1.2.2.jar / commonds-pool-1.4.jar /
> > > commonds-logging-1.0.4.jar into tomcat's common/lib.
> > >
> > > The dataSource is accessed through a JNDI lookup. I want to check
> > connection
> > > pooling.
> > >
> > > For the same I tried the following for the dataSource definition.
> > >
> > > In context.xml I have:
> > >
> > > <Resource
> > > name="jdbc/XYZ"
> > > auth="Container" type="javax.sql.DataSource"
> > > maxActive="1"
> > > maxWait="0"
> > > maxIdle="0"
> > > validationQuery="SELECT 1"
> > > username="test"
> > > password="test123"
> > > driverClassName="com.mysql.jdbc.Driver"
> > > factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> > > logAbandoned="true"
> > > url="jdbc:mysql://127.0.0.1:3306/abc"/>
> > >
> > > Setting maxActive to 1 means that only 1 connection will be allocated
> > at the
> > > same time. Which means that If i establish two concurrent requests to
> > the
> > > application, then the second one should fail.
> >
> > Not necessarily, depending on configuration, the second request may
> > wait for a connection to be returned to the pool. Unfortunately, 0 is
> > interpreted similarly to the default, -1, as a configuration parameter
> > for maxWait, so maxWait = 0 means wait indefinitely. (This behavior
> > was introduced in early versions of commons pool and has not been
> > changed to preserve backward compatability). There is no way to get
> > BasicDataSource to fail *immediately* when the pool is exhausted. To
> > get that behavior, you would need to set up the pool yourself and
> > create a PoolingDataSource from it, setting the whenExhaustedAction to
> > WHEN_EXHAUSTED_FAIL. Setting maxWait to a very small positive number,
> > e.g. 1, with BasicDataSource should effectively accomplish the same
> > thing, though.
> >
> > I don't see this happen. I
> > > initially thought that I had very few rows in the db and hence the
> > mysql
> > > operation was finishing quickly due to which the second request was
> > also
> > > getting processed. But putting a loop and executing the mysql
> > statement also
> > > results in both the requests getting served.
> > >
> > > I tried this with Jmeter too. I tried a Forever loop with Jmeter. And
> > during
> > > that time maxActive=1 and then from the browser again tried a request
> > to the
> > > jsp and this time I got a 'JDBC exception : Communications link
> > failure' -
> > > Address already in use. If i stop Jmeter tests, an redo the browser
> > test, it
> > > only succeeds after a while. ie the first few runs still fail with
> > the same
> > > exceptions and then it seems like the connections are released and
> > then this
> > > call succeeds. But in this time interval , in mysql 'show
> > processlist' I
> > > dont see the connections.
> > >
> > > I have tried this for a lot of time and any suggestions/comments
> > would be
> > > appreciated.
> > >
> > > I have checked the source of BasicDataSource and saw that it uses the
> > > ObjectPool and PoolableConnectionFactory. Hence am assuming there
> > isnt
> > > anything else that I need to do to get pooling to work.
> > > I also tried deleting the naming-dbcp jar from tomcat's common lib
> > since
> > > that seemed to have the same classes as commons-dbcp.
> > >
> > > Is anything else required ?
> > >
> > > In spring this is accessed using getBean and then the corresponding
> > dao
> > > method is called.
> > >
> > > Also, as soon as it finishes the operation, the connection is removed
> > from
> > > the 'show processlist' of mysql. Does that mean , Tomcat drops and
> > creates
> > > connections for every call ? Isn't that against connection pooling ?
> > Most of
> > > the time I see the status as 'null' / 'sleep' / 'query' with
> > validatation
> > > query and very less time for the actual select query.
> > >
> > I am not a MySql expert, but IIRC, show processlist reports on
> > *running* threads, so it does not show idle connections. The one
> > active connection will be idle between requests or when the engine has
> > finished responding.
> >
> > Phil
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> > For additional commands, e-mail: user-help@commons.apache.org
> >
> >
>
Re: maxActive/maxWait
Posted by Purnima Venkatram <pu...@gmail.com>.
Thanks Phil for the prompt reply.
I'll try this again with maxWait = 1 and check the results and get back.
Regards,
Purnima
On Mon, Mar 3, 2008 at 7:53 PM, Phil Steitz <ph...@gmail.com> wrote:
> On Mon, Mar 3, 2008 at 6:36 AM, Purnima Venkatram
> <pu...@gmail.com> wrote:
> > Hi all.
> >
> > I am using commons dbcp through Tomcat.
> >
> > Configuration:
> >
> > 1) Spring 2.5.1
> > 2) Tomcat 5.5.25
> > 3) mysql 5.0.21.
> >
> > Have put the commons-dbcp-1.2.2.jar / commonds-pool-1.4.jar /
> > commonds-logging-1.0.4.jar into tomcat's common/lib.
> >
> > The dataSource is accessed through a JNDI lookup. I want to check
> connection
> > pooling.
> >
> > For the same I tried the following for the dataSource definition.
> >
> > In context.xml I have:
> >
> > <Resource
> > name="jdbc/XYZ"
> > auth="Container" type="javax.sql.DataSource"
> > maxActive="1"
> > maxWait="0"
> > maxIdle="0"
> > validationQuery="SELECT 1"
> > username="test"
> > password="test123"
> > driverClassName="com.mysql.jdbc.Driver"
> > factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> > logAbandoned="true"
> > url="jdbc:mysql://127.0.0.1:3306/abc"/>
> >
> > Setting maxActive to 1 means that only 1 connection will be allocated
> at the
> > same time. Which means that If i establish two concurrent requests to
> the
> > application, then the second one should fail.
>
> Not necessarily, depending on configuration, the second request may
> wait for a connection to be returned to the pool. Unfortunately, 0 is
> interpreted similarly to the default, -1, as a configuration parameter
> for maxWait, so maxWait = 0 means wait indefinitely. (This behavior
> was introduced in early versions of commons pool and has not been
> changed to preserve backward compatability). There is no way to get
> BasicDataSource to fail *immediately* when the pool is exhausted. To
> get that behavior, you would need to set up the pool yourself and
> create a PoolingDataSource from it, setting the whenExhaustedAction to
> WHEN_EXHAUSTED_FAIL. Setting maxWait to a very small positive number,
> e.g. 1, with BasicDataSource should effectively accomplish the same
> thing, though.
>
> I don't see this happen. I
> > initially thought that I had very few rows in the db and hence the
> mysql
> > operation was finishing quickly due to which the second request was
> also
> > getting processed. But putting a loop and executing the mysql statement
> also
> > results in both the requests getting served.
> >
> > I tried this with Jmeter too. I tried a Forever loop with Jmeter. And
> during
> > that time maxActive=1 and then from the browser again tried a request
> to the
> > jsp and this time I got a 'JDBC exception : Communications link
> failure' -
> > Address already in use. If i stop Jmeter tests, an redo the browser
> test, it
> > only succeeds after a while. ie the first few runs still fail with the
> same
> > exceptions and then it seems like the connections are released and then
> this
> > call succeeds. But in this time interval , in mysql 'show processlist'
> I
> > dont see the connections.
> >
> > I have tried this for a lot of time and any suggestions/comments would
> be
> > appreciated.
> >
> > I have checked the source of BasicDataSource and saw that it uses the
> > ObjectPool and PoolableConnectionFactory. Hence am assuming there isnt
> > anything else that I need to do to get pooling to work.
> > I also tried deleting the naming-dbcp jar from tomcat's common lib
> since
> > that seemed to have the same classes as commons-dbcp.
> >
> > Is anything else required ?
> >
> > In spring this is accessed using getBean and then the corresponding dao
> > method is called.
> >
> > Also, as soon as it finishes the operation, the connection is removed
> from
> > the 'show processlist' of mysql. Does that mean , Tomcat drops and
> creates
> > connections for every call ? Isn't that against connection pooling ?
> Most of
> > the time I see the status as 'null' / 'sleep' / 'query' with
> validatation
> > query and very less time for the actual select query.
> >
> I am not a MySql expert, but IIRC, show processlist reports on
> *running* threads, so it does not show idle connections. The one
> active connection will be idle between requests or when the engine has
> finished responding.
>
> Phil
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
Re: maxActive/maxWait
Posted by Phil Steitz <ph...@gmail.com>.
On Mon, Mar 3, 2008 at 6:36 AM, Purnima Venkatram
<pu...@gmail.com> wrote:
> Hi all.
>
> I am using commons dbcp through Tomcat.
>
> Configuration:
>
> 1) Spring 2.5.1
> 2) Tomcat 5.5.25
> 3) mysql 5.0.21.
>
> Have put the commons-dbcp-1.2.2.jar / commonds-pool-1.4.jar /
> commonds-logging-1.0.4.jar into tomcat's common/lib.
>
> The dataSource is accessed through a JNDI lookup. I want to check connection
> pooling.
>
> For the same I tried the following for the dataSource definition.
>
> In context.xml I have:
>
> <Resource
> name="jdbc/XYZ"
> auth="Container" type="javax.sql.DataSource"
> maxActive="1"
> maxWait="0"
> maxIdle="0"
> validationQuery="SELECT 1"
> username="test"
> password="test123"
> driverClassName="com.mysql.jdbc.Driver"
> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> logAbandoned="true"
> url="jdbc:mysql://127.0.0.1:3306/abc"/>
>
> Setting maxActive to 1 means that only 1 connection will be allocated at the
> same time. Which means that If i establish two concurrent requests to the
> application, then the second one should fail.
Not necessarily, depending on configuration, the second request may
wait for a connection to be returned to the pool. Unfortunately, 0 is
interpreted similarly to the default, -1, as a configuration parameter
for maxWait, so maxWait = 0 means wait indefinitely. (This behavior
was introduced in early versions of commons pool and has not been
changed to preserve backward compatability). There is no way to get
BasicDataSource to fail *immediately* when the pool is exhausted. To
get that behavior, you would need to set up the pool yourself and
create a PoolingDataSource from it, setting the whenExhaustedAction to
WHEN_EXHAUSTED_FAIL. Setting maxWait to a very small positive number,
e.g. 1, with BasicDataSource should effectively accomplish the same
thing, though.
I don't see this happen. I
> initially thought that I had very few rows in the db and hence the mysql
> operation was finishing quickly due to which the second request was also
> getting processed. But putting a loop and executing the mysql statement also
> results in both the requests getting served.
>
> I tried this with Jmeter too. I tried a Forever loop with Jmeter. And during
> that time maxActive=1 and then from the browser again tried a request to the
> jsp and this time I got a 'JDBC exception : Communications link failure' -
> Address already in use. If i stop Jmeter tests, an redo the browser test, it
> only succeeds after a while. ie the first few runs still fail with the same
> exceptions and then it seems like the connections are released and then this
> call succeeds. But in this time interval , in mysql 'show processlist' I
> dont see the connections.
>
> I have tried this for a lot of time and any suggestions/comments would be
> appreciated.
>
> I have checked the source of BasicDataSource and saw that it uses the
> ObjectPool and PoolableConnectionFactory. Hence am assuming there isnt
> anything else that I need to do to get pooling to work.
> I also tried deleting the naming-dbcp jar from tomcat's common lib since
> that seemed to have the same classes as commons-dbcp.
>
> Is anything else required ?
>
> In spring this is accessed using getBean and then the corresponding dao
> method is called.
>
> Also, as soon as it finishes the operation, the connection is removed from
> the 'show processlist' of mysql. Does that mean , Tomcat drops and creates
> connections for every call ? Isn't that against connection pooling ? Most of
> the time I see the status as 'null' / 'sleep' / 'query' with validatation
> query and very less time for the actual select query.
>
I am not a MySql expert, but IIRC, show processlist reports on
*running* threads, so it does not show idle connections. The one
active connection will be idle between requests or when the engine has
finished responding.
Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org