问题描述
项目使用的网上的springboot封装的框架。项目初期使用一切正常,随着用户量的增加发现应用的响应速递有所下降。这两天系统开始出现间隙性无法访问。追踪日志发现报错
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 18, maxActive 200, creating 1, createElapseMillis 120000
at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1739)
at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1409)
at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:5059)
at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:680)
at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:5055)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1387)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1379)
at com.alibaba.druid.pool.xa.DruidXADataSource.getXAConnection(DruidXADataSource.java:45)
at com.atomikos.jdbc.AtomikosXAConnectionFactory.createPooledConnection(AtomikosXAConnectionFactory.java:43)
... 147 more
发现创建数据连接超时,但是实际连接数并没有到大最大连接数。找了很久发现另外一处错误,AtomikosSQLException:连接数已达到pool的上限,无法再创建新的连接
aused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.atomikos.jdbc.AtomikosSQLException: Failed to grow the connection pool
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.TProxy105.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 123 more
于是在本地使用jmeter 进行压测50并发发现,本地出现一样的问题。于是在网上找相关的错误信息。找了一堆发现没有什么有用信息。最终在一片文章中找到一样的问题,https://blog.youkuaiyun.com/qq_39002724/article/details/106671114
当使用原生druid时:活跃连接数在并发操作结束后,很快恢复到最小连接数左右
当使用atomikos时:活跃连接数在并发操作结束后,需要等一段时间才能恢复到最小连接数左右
DruidXADataSource的连接归还速度远小于原生druid,导致并发量大的情况下,由于本来占用连接就多,归还速度又慢,最终出现上述问题。
参照对应的解决方法,将oracle连接从原先的DruidXADataSource 替换成oracle原生的后,进行压测发现应用正常。
报错之前的代码:
/**
* 数据源创建模板
*/
private static DataSource createDataSource(String dataSourceName, DruidProperties druidProperties) {
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
atomikosDataSourceBean.setUniqueResourceName(dataSourceName);
atomikosDataSourceBean.setMaxPoolSize(100);
atomikosDataSourceBean.setBorrowConnectionTimeout(60);
atomikosDataSourceBean.setXaProperties(druidProperties.createProperties());
return atomikosDataSourceBean;
}
修改之后的代码:
/**
* 数据源创建模板
*/
private static DataSource createOracleDataSource(String dataSourceName, DruidProperties druidProperties){
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setUniqueResourceName(dataSourceName);
atomikosDataSourceBean.setMaxPoolSize(100);
atomikosDataSourceBean.setBorrowConnectionTimeout(60);
atomikosDataSourceBean.setXaProperties(druidProperties.createProperties());
try {
OracleXADataSource mysqlXADataSource = new OracleXADataSource();
mysqlXADataSource.setURL(druidProperties.getUrl());
mysqlXADataSource.setUser(druidProperties.getUsername());
mysqlXADataSource.setPassword(druidProperties.getPassword());
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
}catch (SQLException e){
e.printStackTrace();
}
return atomikosDataSourceBean;
}