commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pramodh Peddi" <ppe...@IMPRIVATA.com>
Subject RE: [dbcp] Performance decrease woth common-dbcp
Date Wed, 21 Dec 2005 15:30:40 GMT
Hi,

I am back after doing some investigation on the performance of our
product w/ commons-dbcp-1.2 and also commons-dbcp-1.2.1. Bad news is
both 1.2 and 1.2.1 gave me the same bad performance compared to our
home-brewed connection pooling. We expected that commons-dbcp will give
better performance with PreparedStatement pooling turned on. But,
results are unfortunately otherwise.

 

These are my notes, which might be helpful to others who are using dbcp:

 

1. We used commons-dbcp1.2 in our product and used PreparedStatement
caching/pooling, Abandoned features. Using commons-dbcp degraded the
performance by around 25%.

This is the configuration set:

factory = org.apache.commons.dbcp.BasicDataSourceFactory

username = myuser

password = mypass

driverClassName = com.mysql.jdbc.Driver

url =
jdbc:mysql://localhost:3306/MYDATABASE?autoReconnect=true&useServerPrepS
tmts=false&jdbcCompliantTruncation=false

maxActive = 170

maxWait = 360000

poolPreparedStatements = true

 

2. Not using the three *Abandoned* settings in the dbcp configuration
gave me a 20% performance benefit, which still is not good enough to
give me back the performance I used to have before using dbcp.

 

3. org.apache.commons.dbpc.PoolingConnection$PStmtKey.hashCode was
taking significant amount of time. This is the source of the code:

We (My colleague and I) analyzed/understood why it is taking so long. It
is calculating the hashcode of (catalog + sqlquery) every time this
method is called to pool a prepared statement. Calculation of HashCode
is very expensive. Hashcode of a String is calculated once and "cached",
so is available for free the next time in JVM's lifetime. The problem is
String(catalog + sqlquery) is a new String and thus the hashcode is
calculated for EVERY prepared stmt creation. We actually changed the
source of org.apache.commons.dbpc.PoolingConnection$PStmtKey.hashCode to
not concatenate catalog. This method eventually went off the radar in
the profiler we used, but the performance was surprisingly still the
same - not better not worse.

I understand catalog is used to support multiple databases, but
cost/performance of using pooled prepared statements turned out to be
MORE expensive than not using pooled prepared statements - according to
the profiler report.

 

Any suggestions on how to improve performance? I will appreciate it! If
I do not find a way to improve the performance of dbcp, unfortunately we
will have to NOT use commons-dbcp - either not use any pooling tool or
use some other better performing pooling tool.

 

Thanks,

 

-Pramodh.

 

-----Original Message-----
From: Edgar Poce [mailto:edgarpoce@gmail.com] 
Sent: Wednesday, August 17, 2005 5:01 PM
To: Jakarta Commons Users List
Subject: Re: [dbcp] Performance decrease woth common-dbcp

 

Hi Pramodh

 

Pramodh Peddi wrote:

> Thanks for the positive response Edgar. 

> 

> When you said similar problems, did you have performance problems and

> also abandon feature problems? 

yes

 

>If so, I understand after moving to 1.2.1

> both problems were solved, along with any other problems you had.

yes

 

> Sorry for asking more questions. Did u use programmatic technique to

> create BasicDataSource (not JNDI way)?

yes, I used it programatically.

 

br,

edgar

 

> I feel better now, and I am going to move to 1.2.1 hoping to see my

> problems solved.

> 

>  

> 

> Thanks,

> 

>  

> 

> Pramodh.  

> 

>  

> 

> -----Original Message-----

> From: Edgar Poce [mailto:edgarpoce@gmail.com] 

> Sent: Tuesday, August 16, 2005 5:17 PM

> To: Jakarta Commons Users List

> Subject: Re: [dbcp] Performance decrease woth common-dbcp

> 

>  

> 

> Hi Pramodh

> 

>  

> 

>   I had similar problems with commons-dbcp 1.2. I didn't investigate 

> 

> further because the problems were solved when I moved to commons-dbcp 

> 

> 1.2.1. I debugged my app to see whether the prepared statements were 

> 

> reused and AFAIR it worked fine with this version. I don't remember if
I

> 

> 

> had problems with a newer driver, but I decided to use my 

> 

> mysql-connector-java-3.0.10-stable-bin.jar.

> 

>  

> 

> br,

> 

> edgar

> 

>  

> 

> Pramodh Peddi wrote:

> 

> 

>>Hi DBCP experts,

> 

> 

> 

>> 

> 

> 

> 

>>(Sorry for the relatively big email. This email is critical for me

> 

> (and

> 

> 

>>my company:-)). I wanted to make sure I explain everything clearly to

> 

> 

>>make it easy for people in responding)

> 

> 

> 

>> 

> 

> 

> 

>>I recently introduced commons-dbcp into our product. While basic

> 

> 

>>functionality is working fine, apparently there are few things deeply

> 

> 

>>hurting the product. And I am hoping to fix them to get the product to

> 

> 

>>the normal state. I have a situation where I have to decide whether we

> 

> 

>>should stick to the commons-dbcp (provided I fix what we lost when we

> 

> 

>>shifted to commons-dbcp) or go back to our manual connection pooling

> 

> 

>>(creating bunch of connection objects and keeping them in memory).

> 

> 

>>Following are the software versions I am using:

> 

> 

> 

>> 

> 

> 

> 

>>MySQL-4.1.4-gamma

> 

> 

> 

>>(driver) mysql-connector-java-3.1.6-bin.jar

> 

> 

> 

>>commons-dbcp-1.2.jar

> 

> 

> 

>>commons-pool-1.1.jar

> 

> 

> 

>>commons-collections-3.1.jar

> 

> 

> 

>> 

> 

> 

> 

>>The problems I am facing, for which I am hoping to find solutions are:

> 

> 

> 

>>1. (MAJOR) The performance of the product was decreased drastically

> 

> when

> 

> 

>>using commons-dbcp compared to our old manual pooling. Decreased by

> 

> 40%.

> 

> 

>>I can see that though I am taking the connections from the pool, about

> 

> 

>>50% of times it takes FEW SECONDS (1, 2, 3.....upto 12 seconds

> 

> 

>>sometimes) to get the connection from the pool. Not sure if I am doing

> 

> 

>>anything wrong or missing anything. I assume/understand that once pool

> 

> 

>>is initialized, it creates connections in the pool and getting

> 

> 

>>connections from the pool should not consume much time.

> 

> 

> 

>>2. (semi-major) "Abandon* feature does not work. The tool does not

> 

> close

> 

> 

>>the active connection when it satisfies the condition ((getNumIdle() <

> 

> 

>>2) and (getNumActive() > getMaxActive() - 3).   

> 

> 

> 

>> 

> 

> 

> 

>>We introduced commons-dbcp into our product to achieve two things:

> 

> 

> 

>>1. Have an automatic pool of connections (which we almost achieved)

> 

> 

> 

>>2. Improve the performance of the product by having

> 

> 

>>poolPreparedStatements = true in the configuration.

> 

> 

> 

>>I am also wondering if dbcp is really poling the prepared statements

> 

> 

>>when it working with the MySQL server and driver versions we are

> 

> using.

> 

> 

>>Is there any one out there using this configuration and also uses

> 

> 

>>commons-dbcp? Can you confirm if poolPreparedStatements = true is

> 

> giving

> 

> 

>>any performance gain.

> 

> 

> 

>> 

> 

> 

> 

>>Performance is mission critical for our product. Can I have

> 

> suggestions

> 

> 

>>from any one on how to handle the commons-dbcp tool better to improve

> 

> 

>>the performance? Following is the configuration set I am using:

> 

> 

> 

>>********

> 

> 

> 

>>factory = org.apache.commons.dbcp.BasicDataSourceFactory

> 

> 

> 

>>username = myuser

> 

> 

> 

>>password = mypwd

> 

> 

> 

>>driverClassName = com.mysql.jdbc.Driver

> 

> 

> 

>>url =

> 

> 

>
jdbc:mysql://localhost:3306/MYDATABASE?autoReconnect=true&useServerPrepS

> 

> 

>>tmts=false&jdbcCompliantTruncation=false

> 

> 

> 

>>maxActive = 170

> 

> 

> 

>>maxWait = 360000

> 

> 

> 

>>removeAbandonedTimeout = 1800000

> 

> 

> 

>>removeAbandoned = true

> 

> 

> 

>>logAbandoned = true

> 

> 

> 

>>poolPreparedStatements = true

> 

> 

> 

>>***************

> 

> 

> 

>> 

> 

> 

> 

>>I am programmatically creating the BasicDataSource and the pool. I

> 

> mean

> 

> 

>>I am NOT using JNDI to create the pool. Extract of the code:

> 

> 

> 

>>Properties props = getPropertiesFileForDBCpConfig();

> 

> 

> 

>>BasicDataSource basicDataSource = (BasicDataSource)

> 

> 

>>BasicDataSourceFactory.createDataSource(props);

> 

> 

> 

>> 

> 

> 

> 

>>Hoping to get a response from you experts,

> 

> 

> 

>> 

> 

> 

> 

>>Thanks in advance,

> 

> 

> 

>> 

> 

> 

> 

>>Pramodh.

> 

> 

> 

>> 

> 

> 

> 

>> 

> 

> 

> 

>> 

> 

> 

> 

>> 

> 

> 

> 

>> 

> 

> 

> 

> 

>  

> 

> ---------------------------------------------------------------------

> 

> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org

> 

> For additional commands, e-mail: commons-user-help@jakarta.apache.org

> 

>  

> 

> 

 

---------------------------------------------------------------------

To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org

For additional commands, e-mail: commons-user-help@jakarta.apache.org

 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message