我使用的是Tomcat6.0.29,带有Tomcat7的连接池和MySQL。测试我的应用程序时,它没有重用池中的任何东西,但最终创建了一个新的池,最终导致我无法使用数据库,因为当池的最大活动大小设置为20时,池中有数百个休眠连接。
请参考此处:
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+--------+---------+------+-------+------------------+
| 2 | root | localhost:51877 | dbname | Sleep | 9 | | NULL |
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 5 | root | localhost:49213 | dbname | Sleep | 21 | | NULL |
| 6 | root | localhost:53492 | dbname | Sleep | 21 | | NULL |
| 7 | root | localhost:46012 | dbname | Sleep | 21 | | NULL |
| 8 | root | localhost:34964 | dbname | Sleep | 21 | | NULL |
| 9 | root | localhost:52728 | dbname | Sleep | 21 | | NULL |
| 10 | root | localhost:43782 | dbname | Sleep | 21 | | NULL |
| 11 | root | localhost:38468 | dbname | Sleep | 21 | | NULL |
| 12 | root | localhost:48021 | dbname | Sleep | 21 | | NULL |
| 13 | root | localhost:54854 | dbname | Sleep | 21 | | NULL |
| 14 | root | localhost:41520 | dbname | Sleep | 21 | | NULL |
| 15 | root | localhost:38112 | dbname | Sleep | 13 | | NULL |
| 16 | root | localhost:39168 | dbname | Sleep | 13 | | NULL |
| 17 | root | localhost:40427 | dbname | Sleep | 13 | | NULL |
| 18 | root | localhost:58179 | dbname | Sleep | 13 | | NULL |
| 19 | root | localhost:40957 | dbname | Sleep | 13 | | NULL |
| 20 | root | localhost:45567 | dbname | Sleep | 13 | | NULL |
| 21 | root | localhost:48314 | dbname | Sleep | 13 | | NULL |
| 22 | root | localhost:34546 | dbname | Sleep | 13 | | NULL |
| 23 | root | localhost:44928 | dbname | Sleep | 13 | | NULL |
| 24 | root | localhost:57320 | dbname | Sleep | 13 | | NULL |
| 25 | root | localhost:54643 | dbname | Sleep | 29 | | NULL |
| 26 | root | localhost:49809 | dbname | Sleep | 29 | | NULL |
| 27 | root | localhost:60993 | dbname | Sleep | 29 | | NULL |
| 28 | root | localhost:36676 | dbname | Sleep | 29 | | NULL |
| 29 | root | localhost:53574 | dbname | Sleep | 29 | | NULL |
| 30 | root | localhost:45402 | dbname | Sleep | 29 | | NULL |
| 31 | root | localhost:37632 | dbname | Sleep | 29 | | NULL |
| 32 | root | localhost:56561 | dbname | Sleep | 29 | | NULL |
| 33 | root | localhost:34261 | dbname | Sleep | 29 | | NULL |
| 34 | root | localhost:55221 | dbname | Sleep | 29 | | NULL |
| 35 | root | localhost:39613 | dbname | Sleep | 15 | | NULL |
| 36 | root | localhost:52908 | dbname | Sleep | 15 | | NULL |
| 37 | root | localhost:56401 | dbname | Sleep | 15 | | NULL |
| 38 | root | localhost:44446 | dbname | Sleep | 15 | | NULL |
| 39 | root | localhost:57567 | dbname | Sleep | 15 | | NULL |
| 40 | root | localhost:56445 | dbname | Sleep | 15 | | NULL |
| 41 | root | localhost:39616 | dbname | Sleep | 15 | | NULL |
| 42 | root | localhost:49197 | dbname | Sleep | 15 | | NULL |
| 43 | root | localhost:59916 | dbname | Sleep | 15 | | NULL |
| 44 | root | localhost:37165 | dbname | Sleep | 15 | | NULL |
| 45 | root | localhost:45649 | dbname | Sleep | 1 | | NULL |
| 46 | root | localhost:55397 | dbname | Sleep | 1 | | NULL |
| 47 | root | localhost:34322 | dbname | Sleep | 1 | | NULL |
| 48 | root | localhost:54387 | dbname | Sleep | 1 | | NULL |
| 49 | root | localhost:55147 | dbname | Sleep | 1 | | NULL |
| 50 | root | localhost:47280 | dbname | Sleep | 1 | | NULL |
| 51 | root | localhost:56856 | dbname | Sleep | 1 | | NULL |
| 52 | root | localhost:58369 | dbname | Sleep | 1 | | NULL |
| 53 | root | localhost:33712 | dbname | Sleep | 1 | | NULL |
| 54 | root | localhost:44315 | dbname | Sleep | 1 | | NULL |
| 55 | root | localhost:54649 | dbname | Sleep | 14 | | NULL |
| 56 | root | localhost:41202 | dbname | Sleep | 14 | | NULL |
| 57 | root | localhost:59393 | dbname | Sleep | 14 | | NULL |
| 58 | root | localhost:38304 | dbname | Sleep | 14 | | NULL |
| 59 | root | localhost:34548 | dbname | Sleep | 14 | | NULL |
| 60 | root | localhost:49567 | dbname | Sleep | 14 | | NULL |
| 61 | root | localhost:48077 | dbname | Sleep | 14 | | NULL |
| 62 | root | localhost:48586 | dbname | Sleep | 14 | | NULL |
| 63 | root | localhost:45308 | dbname | Sleep | 14 | | NULL |
| 64 | root | localhost:43169 | dbname | Sleep | 14 | | NULL |
它为每个请求恰好创建10个,这是minIdle & InitialSize属性,如下所示。
下面是嵌入到jsp页面中的示例测试代码。代码不是我的应用程序中的代码,只是用来查看问题是否与我的代码有关,但问题仍然存在。
Context envCtx;
envCtx = (Context) new InitialContext().lookup("java:comp/env");
DataSource datasource = (DataSource) envCtx.lookup("jdbc/dbname");
Connection con = null;
try {
con = datasource.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from UserAccount");
int cnt = 1;
while (rs.next()) {
out.println((cnt++)+". Token:" +rs.getString("UserToken")+
" FirstName:"+rs.getString("FirstName")+" LastName:"+rs.getString("LastName"));
}
rs.close();
st.close();
} finally {
if (con!=null) try {con.close();}catch (Exception ignore) {}
}
这是我的context.xml文件:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/dbname"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="20"
minIdle="10"
maxWait="10000"
initialSize="10"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors=
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username=""
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true&useUnicode=true&characterEncoding=utf8"/>
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>META-INF/context.xml</WatchedResource>
</Context>
我确信我可以使用较低数量的removeAbandonedTimeout,它将清除所有这些休眠连接,但这并不能解决真正的问题,不是吗?有人知道我做错了什么吗?非常感谢。
发布于 2012-04-22 19:17:47
关于您的代码的简短说明:不仅是连接,而且ResultSet和语句也应该在Finally块中关闭。BRPocock提供的方法应该可以很好地工作。
但这并不是每个请求需要10个连接的实际原因!每次请求获得10个连接的原因是因为您将minIdle设置为10,这意味着您在创建DataSource时强制每个set具有10个连接。(尝试将minIdle设置为5,您会看到每个请求将有5个连接。)
在您的案例中,问题在于,每次请求时,都会创建一个新的DataSource:
DataSource datasource = (DataSource) envCtx.lookup("jdbc/dbname");
我不确定查找到底是如何工作的,但根据mysql的进程列表,我非常确信,对于每个请求,您都会创建一个新的数据源。如果有Java Servlet,那么应该在主Servlet的init()方法中创建DataSource。然后,您可以从那里获得连接。
在我的例子中,我做了其他事情,因为我有多个DataSources (多个数据库),所以我使用以下代码来获取我的数据源:
private DataSource getDataSource(String db, String user, String pass)
{
for(Map.Entry<String, DataSource> entry : datasources.entrySet())
{
DataSource ds = entry.getValue();
if(db.equals(ds.getPoolProperties().getUrl()))
{
return ds;
}
}
System.out.println("NEW DATASOURCE CREATED ON REQUEST: " + db);
DataSource ds = new DataSource(initPoolProperties(db, user, pass));
datasources.put(db, ds);
return ds;
}
数据源依赖于equals方法,这并不是很快,但它确实有效。我只保留一个包含我的数据源的全局HashMap,如果我请求一个尚不存在的数据源,我会创建一个新的数据源。我知道这非常有效,因为在日志中,我只能在每个数据库中看到一次NEW DATASOURCE CREATED ON REQUEST: dbname
消息,即使多个客户端使用相同的数据源。
发布于 2011-12-28 16:23:29
您应该尝试使用连接提供程序,创建一个包含声明为静态的数据源提供程序的类,而不是在每次调用时都查找它。您的InitialContext也是如此。也许是因为你每次都会创建一个新的实例。
发布于 2014-12-10 03:33:08
我遇到这个问题是因为我正在使用Hibernate,并且无法用@Transactional
注释我的一些方法。这些连接从未返回到池中。
https://stackoverflow.com/questions/5802100
复制相似问题