背景:
版本是1.1.9 Druid 多数据源配置
测试,开发环境都没问题 一上生产就出现该错误
前端定时一分钟请求一次
上线在公司私有云,这是第一个项目(采坑)
错误日志
at com.jccfc.servicemonitor.web.payment.service.impl.LoanAdvanceServiceImpl.getPayLoanDataTodayPerHours(LoanAdvanceServiceImpl.java:324)
at com.jccfc.servicemonitor.web.payment.service.impl.LoanAdvanceServiceImpl.getLoanDataPerHoursH5(LoanAdvanceServiceImpl.java:122)
at com.jccfc.servicemonitor.web.payment.timetask.PullCorePayDataTask.scheduler(PullCorePayDataTask.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
### The error may exist in class path resource [mybatis/payplat/PaymentChannelOrderMapper.xml]
### The error may involve com.jccfc.servicemonitor.web.payment.mapper.payplat.PaymentChannelOrderMapper.getLoanDataTodayPerHours
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
...
23 common frames omitted
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
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:84)
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 org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
...
28 common frames omitted
Caused by: java.sql.SQLException: interrupt
at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:770)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1280)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1276)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
...
38 common frames omitted
Caused by: java.lang.InterruptedException: null
at java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireInterruptibly(AbstractQueuedSynchronizer.java:898)
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireInterruptibly(AbstractQueuedSynchronizer.java:1222)
at java.util.concurrent.locks.ReentrantLock.lockInterruptibly(ReentrantLock.java:335)
at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:768)
原因:
因为私有云网卡和阿里云的不匹配导致了 只要是请求超过1k的就会响应不了,能连接上数据库但是没有响应
为什么会报interrupt ,因为前端每分钟会请求一次,但是连接池数量有限且设置了maxWait属性,在每次请求数据库之后,数据 库没有响应,但是连接也没有释放,导致一段时间后连接池连接用完,在maxWait时间内又不能重新获取连接导致interrupt
Druid常用配置:
1.1 maxActive :连接池支持的最大连接数。一般取值20就可以了,一般把maxActive设置成可能的并发量就行了设 0 为没有限制。
1.2 maxIdle : 连接池中最多可空闲maxIdle个连接 ,这里取值为20,表示即使没有数据库连接时依然可以保持20空闲的连接,而不被清除,随时处于待命状态。设 0 为没有限制。已经不再使用,配置了也没效果
1.3 minIdle: 连接池中最小空闲连接数,当连接数少于此值时,连接池会创建连接来补充到该值的数量
1.4 initialSize: 初始化连接数目
1.5 maxWait: 连接池中连接用完时,新的请求等待时间,毫秒,这里取值-1,表示无限等待,直到超时为止,也可取值9000,表示9秒后超时。超过时间会出错误信息
1.6 removeAbandoned: 是否清除已经超过“removeAbandonedTimout”设置的无效连接。如果值为“true”则超过“removeAbandonedTimout”设置的无效连接将会被清除。设置此属性可以从那些没有合适关闭连接的程序中恢复数据库的连接。
1.7 removeAbandonedTimeout: 活动连接的最大空闲时间,单位为秒 超过此时间的连接会被释放到连接池中,针对未被close的活动连接
1.8 minEvictableIdleTimeMillis: 连接池中连接可空闲的时间,单位为毫秒 针对连接池中的连接对象
1.9 timeBetweenEvictionRunsMillis / minEvictableIdleTimeMillis: 每timeBetweenEvictionRunsMillis毫秒检查一次连接池中空闲的连接,把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止
1.10 useUnfairLock: 是否启用非公平锁
1.11 testWhileIdle: 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
1.12 testOnBorrow: 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
1.13 testOnReturn: 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
1.14 validationQuery: 在连接池返回连接给调用者前用来对连接进行验证的查询 SQL,要求为一条查询语句
1.15 validationQueryTimeout: SQL 查询验证超时时间(秒),小于或等于 0 的数值表示禁用
1.16 poolPreparedStatements: 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭
1.17 maxOpenPreparedStatements: 要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100
1.18 keepAlive:Druid数据库连接池从1.0.28开始,添加了druid.keepAlive属性,默认关闭 打开druid.keepAlive之后,当连接池空闲时,池中的minIdle数量以内的连接,空闲时间超过m
参考博客: