You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by noon <ra...@gmail.com> on 2007/09/01 21:42:19 UTC
DBCP maxActive
We have a small performance issue in our project and I'm trying to locate the
problem.
The application is a Spring MVC application and the database has about 90
000 rows in the database. We're using DBCP as JDBC pooling tool. Now we have
realized that we're having some performance issues. The application has
about 100 - 150 users.
I just realized that we're using it with it's default values so the
maxActive setting is only 8. Do you think this might cause some performance
issues? If I increase the maxActive up to 40 is it enough? What is the
downside if I set it as 100 or -1 to indicate unlimited connections?
Another question regarding the DBCP.
Our Spring application is multilayered application with web, manager and dao
layers. When exactly does the DBCP give a JDBC connection to the
application? Just when the application needs a connection in the dao (I'm
using Hibernate as ORM + Spring's HibernateDaoSupport jdbc template class)?
or somewhere before? If I make let say 10 queries into the database in the
web or manager layer (each in differend daos), does my application request
for each dao access a new JDBC connection from the pool?
Sorry for newbie questions, but DBCP's internal life is a bit "gray area" at
the moment.
--
View this message in context: http://www.nabble.com/DBCP-maxActive-tf4365566.html#a12443352
Sent from the Commons - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP maxActive
Posted by Phil Steitz <ph...@gmail.com>.
On 9/2/07, noon <ra...@gmail.com> wrote:
>
> Hi Phil,
>
> Thanks for you quick reply.
>
> I kind of knew this, but just wanted that some Pro or Guru would proof that
> :)
>
> I don't know yet what causes the performance issues in my application. I
> just started to investigate all the issues that might cause this and the
> size of the DB pool is one of them. The symtoms of the issues our customer
> is having, sounds like the problem was with the database... when user makes
> a request to DB, it takes some time to get the reply... just like the
> connection pool would have reached the "exhausted" state or something else.
>
> Do you know is there any good technique to log the state of the connection
> pool? how many active connections there are and how many of them are in use
> or free etc. I checked the source code and I didn't find any logging rows of
> anykind from the source (commons pool and commons dbcp projects).
>
Neither DBCP nor pool have logging capabilities themselves. DBCP's
BasicDataSource exposes getNumActive and getNumIdle methods, however,
that can be used programatically to check the number of active/idle
connections at a given time. Be careful introducing lots of
activations of these methods, however, since (as of version 1.2) they
are synchronized.
You might do best monitoring the database itself or looking at its
logs to see how many connections are being opened / closed and if you
can get this information, query response times. If many / most
queries are responding very slowly, then you are going to have
problems under load no matter how you configure the pool. The pool
really just takes away the overhead of opening / closing connections.
In any case, you should focus on finding the long-running queries and
improving their performance assuming you have that problem.
Phil
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP maxActive
Posted by noon <ra...@gmail.com>.
Hi Phil,
Thanks for you quick reply.
I kind of knew this, but just wanted that some Pro or Guru would proof that
:)
I don't know yet what causes the performance issues in my application. I
just started to investigate all the issues that might cause this and the
size of the DB pool is one of them. The symtoms of the issues our customer
is having, sounds like the problem was with the database... when user makes
a request to DB, it takes some time to get the reply... just like the
connection pool would have reached the "exhausted" state or something else.
Do you know is there any good technique to log the state of the connection
pool? how many active connections there are and how many of them are in use
or free etc. I checked the source code and I didn't find any logging rows of
anykind from the source (commons pool and commons dbcp projects).
Phil Steitz wrote:
>
> On 9/1/07, noon <ra...@gmail.com> wrote:
>>
>> We have a small performance issue in our project and I'm trying to locate
>> the
>> problem.
>> The application is a Spring MVC application and the database has about 90
>> 000 rows in the database. We're using DBCP as JDBC pooling tool. Now we
>> have
>> realized that we're having some performance issues. The application has
>> about 100 - 150 users.
>>
>> I just realized that we're using it with it's default values so the
>> maxActive setting is only 8. Do you think this might cause some
>> performance
>> issues? If I increase the maxActive up to 40 is it enough? What is the
>> downside if I set it as 100 or -1 to indicate unlimited connections?
>>
> If the load is heavy enough, 8 active database connections could be
> insufficient and increasing the number of connections might improve
> performance. This may not, however, be the source of the bottleneck.
> If the database queries are running too slowly, for example, you will
> still have performance problems. Increasing maxActive increases the
> maximum number of physical database connections that can be in use at
> a given time. Making that number larger uses more resources on the
> database engine and memory on the client side (where the pool lives).
>
>> Another question regarding the DBCP.
>> Our Spring application is multilayered application with web, manager and
>> dao
>> layers. When exactly does the DBCP give a JDBC connection to the
>> application? Just when the application needs a connection in the dao (I'm
>> using Hibernate as ORM + Spring's HibernateDaoSupport jdbc template
>> class)?
>> or somewhere before? If I make let say 10 queries into the database in
>> the
>> web or manager layer (each in differend daos), does my application
>> request
>> for each dao access a new JDBC connection from the pool?
>>
> I am not a Hibernate-Spring expert, but what DBCP does is maintain a
> *pool* of physical connections that are borrowed by clients when they
> make getConnection requests of DBCP datasources. Connections are
> returned to the pool when clients close them. DBCP interaction is
> handled for you by Hibernate, so in your case, it is Hibernate that
> gets and closes DBCP connections. I don't know the internals of
> Hibernate, so I can't answer the question definitively of when the
> getConnection happens for a Hibernate session. Your different queries
> may be attached to different Hibernate sessions, so they may compete
> for connections from the pool. You should verify that your code,
> Spring and Hibernate are working correctly together to close all of
> the DBCP connections that are opened.
>
> Phil
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/DBCP-maxActive-tf4365566.html#a12447184
Sent from the Commons - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP maxActive
Posted by Phil Steitz <ph...@gmail.com>.
On 9/1/07, noon <ra...@gmail.com> wrote:
>
> We have a small performance issue in our project and I'm trying to locate the
> problem.
> The application is a Spring MVC application and the database has about 90
> 000 rows in the database. We're using DBCP as JDBC pooling tool. Now we have
> realized that we're having some performance issues. The application has
> about 100 - 150 users.
>
> I just realized that we're using it with it's default values so the
> maxActive setting is only 8. Do you think this might cause some performance
> issues? If I increase the maxActive up to 40 is it enough? What is the
> downside if I set it as 100 or -1 to indicate unlimited connections?
>
If the load is heavy enough, 8 active database connections could be
insufficient and increasing the number of connections might improve
performance. This may not, however, be the source of the bottleneck.
If the database queries are running too slowly, for example, you will
still have performance problems. Increasing maxActive increases the
maximum number of physical database connections that can be in use at
a given time. Making that number larger uses more resources on the
database engine and memory on the client side (where the pool lives).
> Another question regarding the DBCP.
> Our Spring application is multilayered application with web, manager and dao
> layers. When exactly does the DBCP give a JDBC connection to the
> application? Just when the application needs a connection in the dao (I'm
> using Hibernate as ORM + Spring's HibernateDaoSupport jdbc template class)?
> or somewhere before? If I make let say 10 queries into the database in the
> web or manager layer (each in differend daos), does my application request
> for each dao access a new JDBC connection from the pool?
>
I am not a Hibernate-Spring expert, but what DBCP does is maintain a
*pool* of physical connections that are borrowed by clients when they
make getConnection requests of DBCP datasources. Connections are
returned to the pool when clients close them. DBCP interaction is
handled for you by Hibernate, so in your case, it is Hibernate that
gets and closes DBCP connections. I don't know the internals of
Hibernate, so I can't answer the question definitively of when the
getConnection happens for a Hibernate session. Your different queries
may be attached to different Hibernate sessions, so they may compete
for connections from the pool. You should verify that your code,
Spring and Hibernate are working correctly together to close all of
the DBCP connections that are opened.
Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org