You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Pramodh Peddi <pp...@IMPRIVATA.com> on 2005/12/21 16:30:40 UTC
RE: [dbcp] Performance decrease woth common-dbcp
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