Have you known enough about DBCP？

Have you known enough about DBCP？

【本文如需转载，请注明作者及出处】

 

I think a lot of people have misunderstanding about DBCP. What makes them think so?
Cause' DBCP seems don't want to release database connection after use. Finally, all available connections got exhausted, and our application runs out of services.

DBCP is weak and can not bear production pressures, according to them. It’s worse that there are a lot of guys on the internet said so.


In fact, DBCP can absolutely take the job!

Firstly, its release number has reached 1.2.2. Base on the Apache Version Numbering Project's theory, any products whose version number has evolved up to 1, it is eligible to be used in production environment.

Second, I've run into such problem from my previous working experience, and my ex-colleague and I have made extensive tests on various Connection Pool products, such as Proxool, c3p0 and DBCP.
Result shows that DBCP behaves very well not only at responsiveness, stability, but also at performance. Once the network resume, it auto-reconnects instantly even after more than 8 hours network disconnection.


Third, I do the test again, but this time it’s only between DBCP and c3p0. The test starts from running 3000 to 5000 threads, which infinitely execute a query lasting at least 1 second. Effort have been made to let DBCP and C3p0 start equally, like the same initial pool size 0, the same max pool size and the same time out, etc.

 

 

Finally, I try to give you a detail introduction on the configurations of DBCP, focusing on the important parameters.

 

 All information comes from http://commons.apache.org/dbcp/configuration.html



Part1, Besides the Normal parameters:

username                           The connection username to be passed to our JDBC driver to establish a connection.

password                           The connection password to be passed to our JDBC driver to establish a connection.

url                                       The connection URL to be passed to our JDBC driver to establish a connection.

driverClassName                The fully qualified Java class name of the JDBC driver to be used.


Part2, the following are related to performances:

initialSize                            The initial number of connections that are created when the pool is started.

maxActive                           The maximum number of active connections that can be allocated from this pool at the same time, or non-positive for no limit.

maxIdle                              The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.

minIdle                               The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.

maxWait                             The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.

poolPreparedStatements    Enable prepared statement pooling for this pool.

maxOpenPreparedStatements  The maximum number of open statements that can be allocated from the statement pool at the same time, or zero for no limit.



Part3, but setting the above parameters or combining only some of the them won’t help you get rid of exhaustion of connection problem due to poorly written code, maybe that's source of problems. The following configuration matters:


validationQuery                        The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.


timeBetweenEvictionRunsMillis The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.


minEvictableIdleTimeMillis         The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).


removeAbandoned Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection.


removeAbandonedTimeout Timeout in seconds before an abandoned connection can be removed. the above two parameter must be set at the same time to make connection pool manageable.If we set


validationQuery="SELECT SYSDATE FROM DUAL"
removeAbandoned=true
removeAbandonedTimeout=30
timeBetweenEvictionRunsMillis=500
minEvictableIdleTimeMillis=60000


then, we should interpreted these as:


1) If there’s poorly written code which forget to close connections after use, and it lasts for 30 seconds, it will be removed from the pool!

2) There’ll be an evictor thread up to monitor connection objects. If one of the connection objects becomes idle and last for 5000 milliseconds, it will be marked as removable. After another short nap of 500 milliseconds, that's at between 60000 and 60500 milliseconds, if the same connection is still found idle, it will be removed from the pool.



So a typical configuration for oracle would be like this:


driverClassName=driverclass
url=dburl
username=user
password=password


maxActive=100
maxWait=-1
defaultAutoCommit=true
minIdle=0
maxIdle=30
poolPreparedStatements=true

validationQuery="SELECT SYSDATE FROM DUAL"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle = "false"
timeBetweenEvictionRunsMillis=500
removeAbandoned=true
removeAbandonedTimeout=30
minEvictableIdleTimeMillis=60000


1) It means when the pool starts up, poolPreparedStatements ability is enabled, transaction will be auto committed and there is no connections in it.

2) Any connections will be created on demand. If a new connection is requested, after creation, the new one will be tested against "SELECT SYSDATE FROM DUAL" before returning it to user, if it failed validation, yielding no result, the connection will be dropped, and another connection will be created and validated again until it pass.

3) If suddenly, floods of requests come in, the pool will create as many connections as it can to serve the request until the size reach 100. If requests continue to arrive, they will have wait before some connections finished the previous serving.

4) If there’s poorly written code which forget to close connections after use, and it lasts for 30 seconds, it will be removed from the pool!

5) When the flood begins to ebb, some of the connections will become idle and have nothing to do. If any connection object becomes idle and last for 60000 milliseconds, it will be marked as removable. After another short nap of 500 milliseconds, that's at between 60000 and 60500 milliseconds, if the same connection is still found idle, it will be removed from the pool.



For commons-dbcp-1.2.2 and commons-pool-1.3 and
we have to set

minIdle=0
testWhileIdle=false

to avoid a potential deadlock as described by
https://issues.apache.org/jira/browse/DBCP-44




Here is the test:

Environment:
Hardware: AMD Turion(TM) 64 X2 Mobile 1.8G    and 1.5G Memory

Operating System: Windows XP

Database:

 

mysql : 5.0.45-community-nt MySQL Community Edition (GPL)
Max_used_connections 100

 

 

Oracle: 10G default settings

 

Pool implementaions:

1)commons-dbcp-1.2.2(commons-pool-1.3)

2)c3p0-0.9.1.2

database  dbcp/c3p0   concurrent_threads     maxActive/poolSize   10minutes queries executed

mysql        dbcp/c3p0                5000                     100                        16200/1600
oracle       dbcp/c3p0                3000                      10                         3691/1900
oracle       dbcp/c3p0                3000                      50                         20900/6500
oracle       dbcp/c3p0                5000                      50                         8400/2000
oracle       dbcp/c3p0                5000                      80                         8800/1800

 

oracle       dbcp/c3p0                5000                       100                         ORA-12519/very slow, but no ORA-12519

(ORA-12519, TNS:no appropriate service handler found)


But if you want performance gained from dbcp, you should increase the value of "PROCESSES" parameter in oracle

 

http://www.webservertalk.com/archive149-2006-2-1410832.html 写道

PROCESSES specifies the maximum number of operating system user
processes that can simultaneously connect to Oracle. Its value should
allow for all background processes such as locks, job queue processes,
and parallel execution processes.

 

Jdbc.properties
cabernet.jdbc.driverClassName=com.mysql.jdbc.Driver
cabernet.jdbc.url=jdbc:mysql://g-mobile:3306/jbpm?useUnicode=true&characterEncoding=utf8&autoReconnect=true
cabernet.jdbc.username=root
cabernet.jdbc.password=root

#dbcp
cabernet.jdbc.maxActive=100

##max.Wait means waiting until one is available
cabernet.jdbc.maxWait=-1
cabernet.jdbc.defaultAutoCommit=true
cabernet.jdbc.minIdle=0
cabernet.jdbc.maxIdle=30
cabernet.jdbc.removeAbandoned=true
cabernet.jdbc.removeAbandonedTimeout=30
cabernet.jdbc.poolPreparedStatements=true
cabernet.jdbc.validationQuery=select current_date();
cabernet.jdbc.testOnBorrow=true
cabernet.jdbc.testOnReturn=false
cabernet.jdbc.testWhileIdle=false
cabernet.jdbc.timeBetweenEvictionRunsMillis=500
cabernet.jdbc.minEvictableIdleTimeMillis=16000
cabernet.jdbc.logAbandoned=true

#c3p0
cabernet.jdbc.acquireIncrement=5
cabernet.jdbc.idleConnectionTestPeriod=3000
cabernet.jdbc.maxIdleTime=60

###checkoutTimeout=0 means waiting until one is available
cabernet.jdbc.checkoutTimeout=0
cabernet.jdbc.maxPoolSize=100
cabernet.jdbc.minPoolSize=0
cabernet.jdbc.maxStatements=1000
cabernet.jdbc.maxStatementsPerConnection=1000
cabernet.jdbc.initialPoolSize=0
cabernet.jdbc.autoCommitOnClose=true




Test Code:

package com.cabernet.dbcp;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.cabernet.BaseTestCase;

/**
 *
 * @author ginge
 *
 */
public class TestPooling extends BaseTestCase
{
    protected DataSource    datasource;

    public void testConnection() throws SQLException, InterruptedException
    {
        for (int i = 1000; i > 0; i--)
        {
            new QueryThread(this.datasource).start();

            log.debug("PoolingThread[" + i + "] is up and running.");
        }

        while (true)
            Thread.sleep(100000);
    }

    public DataSource getDatasource()
    {
        return datasource;
    }

    public void setDatasource(DataSource datasource)
    {
        this.datasource = datasource;
    }

    @Override
    protected String[] getConfigLocations()
    {
        // TODO Auto-generated method stub
        return new String[] { "classpath*:applicationContext-resources.xml", "classpath*:applicationContext-jbpm.xml",
                "classpath*:applicationContext-hibernate.xml" };
    }

}

package com.cabernet.dbcp;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.cabernet.BaseTestCase;

/**
 * 
 * @author ginge
 *
 */
public class TestPooling extends BaseTestCase
{
	protected DataSource	datasource;

	public void testConnection() throws SQLException, InterruptedException
	{
		for (int i = 1000; i > 0; i--)
		{
			new QueryThread(this.datasource).start();

			log.debug("PoolingThread[" + i + "] is up and running.");
		}

		while (true)
			Thread.sleep(100000);
	}

	public DataSource getDatasource()
	{
		return datasource;
	}

	public void setDatasource(DataSource datasource)
	{
		this.datasource = datasource;
	}

	@Override
	protected String[] getConfigLocations()
	{
		// TODO Auto-generated method stub
		return new String[] { "classpath*:applicationContext-resources.xml", "classpath*:applicationContext-jbpm.xml",
				"classpath*:applicationContext-hibernate.xml" };
	}

}

 

 

 

 

 

package com.cabernet.dbcp;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Random;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author ginge
 *
 */
public class QueryThread extends Thread
{

    private Log     log     = LogFactory.getLog(this.getClass());
    DataSource      datasource;

    private Random  random  = new Random();

    QueryThread(DataSource datasource)
    {
        this.datasource = datasource;
    }

    @Override
    public void run()
    {
        // TODO Auto-generated method stub
        Connection connection = null;

        int i = 0;
        while (true)
        {
            if (i < Integer.MAX_VALUE)
                i++;
            else
                i = 0;

            try
            {
                connection = datasource.getConnection();
            } catch (Exception e)
            {
                log.error("getConnection exception :", e);
            }

            try
            {
                if (connection != null)
                {
                    String query = "select * from jbpm_log where id_ = " + i;
                    connection.createStatement().execute(query);
                    log.debug("Thread[" + this.getId() + "] executeing [" + query + "]");
                    sleep(1000);
                    this.close(connection);
//                  this.forgetToCloseSomeConnections(connection);
                }
            } catch (Exception e)
            {
                log.error("query exception :", e);
            }

        }
    }

    private void close(Connection connection)
    {
        try
        {
            connection.close();
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    private void forgetToCloseSomeConnections(Connection connection)
    {
        if (random.nextInt(20) != 9)
            try
            {
                connection.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        else
        {
            if (log.isWarnEnabled())
            {
                log.warn("Connection not closed.");
            }
        }
    }
}

package com.cabernet.dbcp;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Random;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * @author ginge
 *
 */
public class QueryThread extends Thread
{

	private Log		log		= LogFactory.getLog(this.getClass());
	DataSource		datasource;

	private Random	random	= new Random();

	QueryThread(DataSource datasource)
	{
		this.datasource = datasource;
	}

	@Override
	public void run()
	{
		// TODO Auto-generated method stub
		Connection connection = null;

		int i = 0;
		while (true)
		{
			if (i < Integer.MAX_VALUE)
				i++;
			else
				i = 0;

			try
			{
				connection = datasource.getConnection();
			} catch (Exception e)
			{
				log.error("getConnection exception :", e);
			}

			try
			{
				if (connection != null)
				{
					String query = "select * from jbpm_log where id_ = " + i;
					connection.createStatement().execute(query);
					log.debug("Thread[" + this.getId() + "] executeing [" + query + "]");
					sleep(1000);
					this.close(connection);
//					this.forgetToCloseSomeConnections(connection);
				}
			} catch (Exception e)
			{
				log.error("query exception :", e);
			}

		}
	}

	private void close(Connection connection)
	{
		try
		{
			connection.close();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	private void forgetToCloseSomeConnections(Connection connection)
	{
		if (random.nextInt(20) != 9)
			try
			{
				connection.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		else
		{
			if (log.isWarnEnabled())
			{
				log.warn("Connection not closed.");
			}
		}
	}
}