[dbcp] Performance decrease woth common-dbcp

[Pramodh Peddi <pp...@IMPRIVATA.com>]

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.

 

 

 

 

 

Re: [dbcp] Performance decrease woth common-dbcp

[Edgar Poce <ed...@gmail.com>]

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

Re: [dbcp] Performance decrease woth common-dbcp

[Brian Cook <bc...@printtime.com>]

Ahhhh I think you maybe making the same mistake I did when I first 
started using commons.dbcp.  I switched to it and not only did not see 
an improvement in connection speed the connections got slower.  It 
turned out had implemented commons.dbcp in the worst possible way for my 
needs.

The most efficient way to use commons.dbcp is using the standard 
DataSource object with JNDI.  When you use the BasicDataSource object 
you creating an entirely new pool every time you construct a new 
instates of BasicDataSource class.

Not only does it take longer to get connections this way but it uses a 
lot of resources managing all of those different pools.  I saw a 40% - 
60% increase in performance when I switched to using DataSource with 
JNDI.  And netstat went from reporting 60 - 120 ports in use to 6.  It 
never even gets close to its new cap of 12.

Does this sound like it may be the situation?




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.
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
> 


-- 
Brian Cook
Digital Services Analyst
Print Time Inc.
bcook@printtime.com
913.345.8900