我使用mysql连接池与最大活动连接250和setRemoveAbandonedTimeout 300秒 . 当我运行查询> 250时,我开始收到此错误:“无法在60秒内获取连接,没有可用[size:250; busy:250; idle:0; lastwait:60000] . ”
执行查询后,我关闭用于执行查询的ResultSet,Statement和Connection .
我的设置中是否有任何遗漏或它应该表现的方式我需要管理连接?
为了调试我已经进行了一些检查以避免上述错误 . 这是源代码:
泳池属性:
PoolProperties p = new PoolProperties();
p.setUrl(dbUrl+path+"?autoReconnect=true");
p.setUsername(username);
p.setPassword(password);
p.setUrl("jdbc:mysql://localhost:3306/"+path+"?autoReconnect=true");
p.setDriverClassName("org.mariadb.jdbc.Driver");
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(250);
p.setInitialSize(10);
p.setMaxWait(60000);
p.setMaxAge(1);
p.setRemoveAbandonedTimeout(300);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(false);
p.setRemoveAbandoned(true);
p.setFairQueue(true);
p.setAbandonWhenPercentageFull(80);
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
datasource = new DataSource();
datasource.setPoolProperties(p);
获取声明:
private Statement getStatement() throws SQLException
{
Connection connection = datasource.getConnection();
return connection.createStatement();
}
执行选择查询:
public ResultSet ExecuteSelectQuery(String sql) throws Exception
{
System.out.println(" QUERY: "+sql );
System.out.println(" ACTIVE CONNECTIONS :" + datasource.getActive());
QueriesCount.incrementAndGet();
while(datasource.getActive() > 240){
try{
Thread.sleep(1000);
System.out.println("WAITING FOR CONNECTION " +datasource.getActive());
}catch(Exception e){
}
}
return getStatement().executeQuery(sql);
}
发布资源:
public void CloseResultSet(ResultSet rs)
{
try
{
if(rs!=null)
{
Statement s = rs.getStatement();
if(s!=null)
{
Connection c = s.getConnection();
s.close();
rs.close();
try{
if(c!= null)
c.close();
c = null;
s = null;
rs = null;
}
catch(Exception e){
PALogger.ERROR(e);
}
}
else{
rs.close();
rs = null;
}
}
}
catch (Exception ex)
{
}
finally{
System.out.println("Query Count " + QueriesCount.get() + " " + this.path);
}
}