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

package com.apt.dbcp.test;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * Java JDBC Connection Pool Tool.
 * 
 * @author Simon Lv
 * 
 */
public class ConnectionPool {

	private static ConnectionPool pool;
	private final static String URL = "jdbc:mysql://113.12.104.53:3306/test";
	private final static String USER_NAME = "root";
	private final static String PASSWORD = "123456";

	private GenericObjectPool genericObjectPool;
	private ConnectionFactory connectionFactory;
	private PoolingDataSource dataSource;

	// Private constructor.
	private ConnectionPool() {
		initResources();
	}

	/**
	 * Get the ConnectionPool object instance.
	 * 
	 * @return A instance of ConnectionPool.
	 */
	public synchronized static ConnectionPool getPool() {
		if (pool == null) {
			pool = new ConnectionPool();
		}
		return pool;
	}

	/*
	 * Initialization of connection pool resources.
	 */
	private void initResources() {
		genericObjectPool = new GenericObjectPool(null);

		genericObjectPool.setMaxActive(300);
		genericObjectPool.setMaxIdle(14);
		genericObjectPool.setMaxWait(20000);
		genericObjectPool.setMinIdle(5);
		genericObjectPool
				.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW);

		connectionFactory = new DriverManagerConnectionFactory(URL, USER_NAME,
				PASSWORD);
		new PoolableConnectionFactory(connectionFactory, genericObjectPool,
				null, null, false, true);
		dataSource = new PoolingDataSource(genericObjectPool);
	}

	public Connection getConnection() {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public void closeConnection(Connection connection) {
		try {
			if (connection != null) {
				//Not a real close operation,just return to the connection pool.
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void showConnectionPool() {
		System.out.println("Max Active:" + genericObjectPool.getMaxActive());
		System.out.println("Max Idle:" + genericObjectPool.getMaxIdle());
		System.out.println("Min Idel:" + genericObjectPool.getMinIdle());

		System.out.println("Max Wait at:" + genericObjectPool.getMaxWait());

		System.out.println("Number of Active :"
				+ genericObjectPool.getNumActive());
		System.out.println("Number of Idle :" + genericObjectPool.getNumIdle());
	}
	
	public void destroyPool(){
		genericObjectPool.clear();
	}
}

# MySQL Server Instance Configuration File
[client]

port=3306

[mysql]

default-character-set=latin1

[mysqld]

# 这几个都是我增加的
back_log=500

# 这几个都是我增加的
record_buffer=16M

# 这几个都是我增加的
sort_buffer=16M

#interactive_timeout=7200

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
# 我增加到了1024个链接
max_connections=1024

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=146M

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=512

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=27M


# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=16

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=27M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
# 修改了
key_buffer_size=400M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K


#*** INNODB Specific options ***


# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=9M

# 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.
# 修改成0或者2 速度提高很快 如果是1的话 很慢 ,上面的英文解释很长,网上有很多解释
innodb_flush_log_at_trx_commit=0

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=5M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
# 修改到800M
innodb_buffer_pool_size=800M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
# 按道理应该是 innodb_buffer_pool_size 的25%~100% ,但测试下来不能增加了,想必是我主机内存就2G的问题
innodb_log_file_size=87M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=8

package com.apt.dbcp.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.UUID;
import com.apt.dbcp.util.Utils;

//Test
public class ConnectionPoolTest {

	public synchronized void testInsert() {
		ConnectionPool connectionPool = ConnectionPool.getPool();
		try {
			Connection connection = connectionPool.getConnection();
			if (connection != null) {
				PreparedStatement statement = connection
						.prepareStatement(DBConst.SESSION_INSERT);
				statement.setString(1, UUID.randomUUID().toString());
				statement.setString(2, UUID.randomUUID().toString());
				statement.setString(3, "172.17.20.3");
				statement.setInt(4, 200);
				statement.setString(5, Utils.getYHM());
				statement.setString(6, Utils.getYHM());
				statement.setInt(7, 2);

				statement.executeUpdate();
			}
			connectionPool.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void loopInsert(int times) {
		long start = System.currentTimeMillis();
		for (int i = 0; i < times; i++) {
			testInsert();
		}
		long end = System.currentTimeMillis();

		System.out.println("Take time (MS) - " + (end - start));
		System.out.println("Take time (S) - " + (end - start) / 1000);

		System.out.println("End ..");
	}
}

package com.apt.dbcp.test;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

public class ThreadGroupTest {
	private static int corePoolSize = 50;
	private static int maxPoolSize = 100;
	private static long keepAliveTime = 1;

	private static TimeUnit unit = TimeUnit.MILLISECONDS;
	private static BlockingQueue<Runnable> workQueue = new ArrayBlockingQueue<Runnable>(
			100);
	
	private static int times=3000 ;
	
	private static long produceTaskSleepTime=0;

	public static void main(String[] args) {
		ThreadPoolExecutor threadPool = new ThreadPoolExecutor(corePoolSize,
				maxPoolSize, keepAliveTime, unit, workQueue,
				new ThreadPoolExecutor.CallerRunsPolicy());
		
		for (int i = 1; i <= times; i++) {
			try {
				threadPool.execute(new Runnable() {
					ConnectionPoolTest test = new ConnectionPoolTest();
					@Override
					public void run() {
						 test.loopInsert(100);
					}
				});
				Thread.sleep(produceTaskSleepTime);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

----The 1 times,total counts > 0W----
Start..
Take time (MS) - 7438
Take time (S) - 7
End ..

----The 2 times,total counts > 1W----
Start..
Take time (MS) - 4703
Take time (S) - 4
End ..

----The 3 times,total counts > 2W----
Start..
Take time (MS) - 5093
Take time (S) - 5
End ..

//省去中间的100多次记录,看170W数据总量时的情况


----The 170 times,total counts > 169W----
Start..
Take time (MS) - 5937
Take time (S) - 5
End ..

----The 171 times,total counts > 170W----
Start..
Take time (MS) - 5875
Take time (S) - 5
End ..

----The 172 times,total counts > 171W----
Start..
Take time (MS) - 6390
Take time (S) - 6
End ..

----The 173 times,total counts > 172W----
Start..
Take time (MS) - 5625
Take time (S) - 5
End ..

//省去中间的100多次记录,看270W 左右 数据总量时的情况

----The 277 times,total counts > 276W----
Start..
Take time (MS) - 7203
Take time (S) - 7
End ..

----The 278 times,total counts > 277W----
Start..
Take time (MS) - 7968
Take time (S) - 7
End ..


//省去中间的30多次记录,看300W 左右 数据总量时的情况,会逐步递增..

----The 302 times,total counts > 301W----
Start..
Take time (MS) - 7468
Take time (S) - 7
End ..

----The 303 times,total counts > 302W----
Start..
Take time (MS) - 8125
Take time (S) - 8
End ..

//明显的逐步递增..

----The 313 times,total counts > 312W----
Start..
Take time (MS) - 10625
Take time (S) - 10
End ..

----The 314 times,total counts > 313W----
Start..
Take time (MS) - 11718
Take time (S) - 11
End ..

----The 315 times,total counts > 314W----
Start..
Take time (MS) - 13703
Take time (S) - 13
End ..


//最后 有点动不了了 1W 的数据 要几十秒
----The 338 times,total counts > 337W----
Start..
Take time (MS) - 24812
Take time (S) - 24
End ..

----The 339 times,total counts > 338W----
Start..
Take time (MS) - 26609
Take time (S) - 26
End ..

----The 340 times,total counts > 339W----
Start..
Take time (MS) - 36983
Take time (S) - 36
End ..

----The 341 times,total counts > 340W----
Start..
Take time (MS) - 42811
Take time (S) - 42
End ..

----The 342 times,total counts > 341W----
Start..
Take time (MS) - 54405
Take time (S) - 54
End ..

----The 343 times,total counts > 342W----
Start..