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.
