[jira] Created: (DBCP-206) DBCP high performance contention point

["Phil Steitz (JIRA)" <ji...@apache.org>]

DBCP high performance contention point 
---------------------------------------

                 Key: DBCP-206
                 URL: http://issues.apache.org/jira/browse/DBCP-206
             Project: Commons Dbcp
          Issue Type: Bug
    Affects Versions: 1.2.1
         Environment: Java 5, Linux RHEL 4.3 (although same thing happens on Windows) DBCP
1.2.1, commons-pool 1.3, MySQL 4 & drivers 3.1.6.
            Reporter: Phil Steitz
             Fix For: 1.3


>From mail to commons-dev from Michael.Veprinsky@dig.com on 29-Nov-06

Hello!
I am using DBCP for very light weight queries and it seems that I came
across synchronization bottleneck. I am not sure, maybe I am doing
something wrong or maybe there is an easy workaround.

It seems that both on DataSource.getConnection and on Connection.close()
there are synchronized blocks (for .close() it's in GenericObjectPool.
returnObject and for getConnection it's in borrowObject)
Seems that primary reason for contention is setAutocommit that occurs
both on borrow and on return. I am trying to reduce number of
setAutocommits going over the network on driver level but a) it only
reduces problem and b) does not seem like a solid long term solution.

Contention happens at ~2-3 k/sec. If I remove pool from the equation I
get 30k+. Have anybody else seen this problem? Are there standard ways
to deal with it (pools with threadlocal, different pool implementations
that deal with passivate/activate outside of synchronized blocks, etc)?

Example: seem below (very end). If I move getConnection() and .close()
outside of the main loop I get ~ order of magnitude performance boost.

My setup:
Java 5, Linux RHEL 4.3 (although same thing happens on Windows) DBCP
1.2.1, commons-pool 1.3, MySQL 4 & drivers 3.1.6.

Configuration for DBCP:
username=...
password=...
maxActive=900
maxIdle=900
url=jdbc:mysql://hostname/disl?elideSetAutoCommits=true&useLocalSessionS
tate=true&poolPreparedStatements=true
driverClassName=com.mysql.jdbc.Driver

Test source (runs in multiple threads):
for(int i=0;i<lcount;i++){
       Connection conn1= ds.getConnection();
       String sql= "select value from user_permission where user_id=?
and permission_id=1";
       PreparedStatement stmt=conn1.prepareStatement(sql);
       stmt.setInt(1, UsernameGenerator.getUserId(id, lcount, i));
       ResultSet res=stmt.executeQuery();
       stmt.close();
       conn1.close();
}


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org