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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
