使用DBCP 数据库连接池遇到的两个比较怀疑的问题

项目本身准备使用Hibernate的,但后来客户要求使用传统的JDBC连接池的方式,于是就找了DBCP和C3P0。 DBCP测下来可以用,但仅仅是可以用,我觉得问题还比较多,对它不熟悉但也不能花很多时间仔细研究.

package com.apt.dbcp.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import java.util.UUID;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.apt.dbcp.util.Utils;

public class PoolManager {

	private static String DRIVER = "com.mysql.jdbc.Driver";
	private static String URL = "jdbc:mysql://132.14.14.13:3306/test";
	private static String NAME = "root";
	private static String PASSWORD = "123456";

	@SuppressWarnings("unchecked")
	private static Class driverClass = null;
	private static GenericObjectPool connectionPool = null;
	
	private static PoolManager manager;

	private PoolManager() {
		
		//Register Driver Class in Memory
		registerDriver();
		
		//Init pool
		initPool();
	}
	
	public static PoolManager getPoolManager(){
		if(manager==null){
			manager = new PoolManager();
		}
		return manager;
	}

	/**
	 */
	private static synchronized void registerDriver() {
		if (driverClass == null) {
			try {
				driverClass = Class.forName(DRIVER);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Init Pool.
	 */
	private static void initPool(){
		if(connectionPool !=null){
			destoryPool();
		}
		try {
			connectionPool = new GenericObjectPool(null);
			connectionPool.setMaxActive(500);
			connectionPool.setMaxIdle(10);
			connectionPool.setMaxWait(2000);
			connectionPool.setMinIdle(5);

			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
					URL, NAME, PASSWORD);

			System.out.println("Max :" + connectionPool.getNumActive());
			System.out.println("Idle :" + connectionPool.getNumIdle());

			@SuppressWarnings("unused")
			PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
					connectionFactory, connectionPool, null, null, false, true);

			Class.forName("org.apache.commons.dbcp.PoolingDriver");
			PoolingDriver driver = (PoolingDriver) DriverManager
					.getDriver("jdbc:apache:commons:dbcp:");
			driver.registerPool("dbpool", connectionPool);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 */
	public static void destoryPool() {
		try {
			PoolingDriver driver = (PoolingDriver) DriverManager
					.getDriver("jdbc:apache:commons:dbcp:");
			driver.closePool("dbpool");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager
					.getConnection("jdbc:apache:commons:dbcp:dbpool");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 
	 * @param conn
	 */
	public static void freeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	public void testQuery(int times) {
		try {
			long start = System.currentTimeMillis();
			for (int i = 0; i < times; i++) {
				Connection conn = PoolManager.getConnection();
				if (conn != null) {
					Statement statement = conn.createStatement();
					ResultSet rs = statement
							.executeQuery("select * from testTBL");
					int c = rs.getMetaData().getColumnCount();
					while (rs.next()) {
						for (int x = 1; x <= c; x++) {
							// System.out.print(rs.getObject(x)+"\t");
						}
					}
					rs.close();
				}
				PoolManager.freeConnection(conn);
			}
			long end = System.currentTimeMillis();

			System.out.println("\nIn " + times + " loops:");
			System.out.println("Take time (S):" + (end - start));
			System.out.println("Take time (MS):" + (end - start) / 1000);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void testInsert() {
		try {
			Connection conn = PoolManager.getConnection();
			if (conn != null) {
				PreparedStatement statement = conn
						.prepareStatement(DBConst.SESSION_INSERT);
				statement.setString(1, UUID.randomUUID().toString());
				statement.setString(2, UUID.randomUUID().toString());
				statement.setString(3, "182.47.10.144");
				statement.setInt(4, new Random().nextInt(100));
				statement.setString(5, Utils.getYHM());
				statement.setString(6, Utils.getYHM());
				statement.setInt(7, 2);

				statement.executeUpdate();
			}
			PoolManager.freeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package com.apt.dbcp.test;

public class MainTest {

	static void test() {
		long start = System.currentTimeMillis();

		PoolManager manager = PoolManager.getPoolManager();

		for (int i = 0; i < 1000; i++) {
			manager.testInsert();
		}
		long end = System.currentTimeMillis();
		System.out.println(Thread.currentThread().getName() + "Take time (MS):"
				+ (end - start));
		System.out.println(Thread.currentThread().getName() + "Take time (S):"
				+ (end - start) / 1000);
	}

	public static void main(String[] args) throws Exception {
		test();
	}
}

-----------------------------------------------------------------------------------
代码有点杂乱,也没有考虑什么结构. 见谅！请忽略数据库结构 内容很简单！
-----------------------------------------------------------------------------------
控制台输出如下:

 Max :0
Idle :0
mainTake time (MS):11844
mainTake time (S):11

------------------------------------------------------------------------------
有几个问题.
第1,同样的代码和结构在不同的电脑上运行结果完全不一样. 在家中使用同样的配置内存2G,1万条全部插入平均6~秒,(不完全是这个代码,类似) 但同样的代码在公司电脑上运, 本地数据库 1千条几乎平均10秒. 我觉得是我使用DBCP的问题,也有可能和MySQL 的配置有关,但这个效率--我 我 我 汗颜.

但的确是同样的代码和数据库.晚上还得回家仔细测测这个案例.

第2,最严重的是,我怀疑程序在启动的时候并没有像想象那样预先先分配若干个可以直接使用的连接对象.
我检查过 -> mysql -> show processlist; 
除了MySql 本地连接外, processlist中就没有其他的记录.
所以是不是几乎或者根本就没有创建已经可以连接的Connection对象呢,这个代码的写法?

-------------------------------------------------------------------------------
Apache 关于DBCP 提供的代码教程我也在试着琢磨并测试运行,基本的连接增删改查都没有问题,但是就是上面的两个问题我觉得没有弄清楚.

另外C3P0，Hibernate 等各种实现方式就不讨论了,目前已经定下了就是DBCP,不能改了. 所以有条件要上,没有条件也要创造条件上.

希望实际应用过DBCP的朋友指点指点,能够找出问题,我也可以少走点弯路,现在就是时间紧还得看他们的官网.

你公司的MySQL的是不是多人共用？

测试环境目前是自己用自己本机的MySQL,毕竟考虑到分配Connection的问题.

所以不会出现多个程序同时访问同一个数据库并同时申请分配连接.

回来也试了一下,同样的代码.
SQLServer, 1万条数据,8秒.
MySQL    , 1千条数据,8秒.

然后就一直用SQLServer测.每次1万.每次重新运行
第1次 8秒
第2次 10秒
第3次 13秒
第4次 15秒
第5次 28秒
第6次 31秒
第.....

和DBCP没有一点关系，关键是DB的IO性能问题。

我无语了！就改了一个配置，MYSQL 1万条插入就变成6秒钟了！
2万条数据插入11秒！ 我测了几次了！

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=2

还没有仔细看为什么，先贴个暂时解决目前这个问题的消息出来。

innodb_flush_log_at_trx_commit=0

如果改成0,又试了下.
4万条插入要17秒！
2万条数据才要8秒！
1万条 要 5秒！

我查了一下资料,直接贴出来.

<<<<
innodb_flush_log_at_trx_commit  （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
>>>>

这个问题暂时可以解决了，但是也会静下心来看看为什么！

另外回到刚才的问题-我虽然设置了
              connectionPool.setMaxActive(500);  
            connectionPool.setMaxIdle(10);  
            connectionPool.setMaxWait(2000);  
            connectionPool.setMinIdle(5);  

但是打印出来的,和在MySQL-> show processlist 上反映的还是不能看到有已经创建好的连接！应该这些连接在程序运行之后和结束之前应该是已经和数据库保持了连接的。

这个怎么解释呢？

如果你的PROCESSLIST没有看到记录，并且在关闭程序之前的话，哪么肯定有问题。无论这个Connection是否是重用的，都应该有记录。

| Id   | User | Host           | db          | Command | Time | State  | Info

              |
+------+------+----------------+-------------+---------+------+--------+--------
--------------------------------------------------------------------------------
--------------+
|    2 | root | localhost:1833 | textmessage | Query   |    0 | NULL   | show pr
ocesslist
              |
| 1073 | root | localhost:3705 | textmessage | Query   |    0 | update | INSERT
INTO SESSION_HISTORY (UUID,SESSION_ID,CLIENT_ADDRESS,STATUS,CREATE_TIME,END_TIME
,DEVICE_ID) V |
+------+------+----------------+-------------+---------+------+--------+--------

每次只能看到一条！ 我估计也是有问题！ 现在就在找问题在哪里。

你可以设置最小连接数。再看看！