数据库连接池配置-系统数据库慢排查

数据库连接池配置-系统数据库慢排查

报错信息：
Health check failedorg.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-1086-exec-86] 
Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:50; busy:25; idle:0; lastwait:10000].

Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-1086-exec-86] 
Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:50; busy:25; idle:0; lastwait:10000].

1.接下来我们分析数据库，用SHOW FULL PROCESSLIST快照的方式每5秒刷一下进程列表，发现列表这两个语句在长时间运行着，最高有达到80多秒。杀掉之前马上又上来了。可以确定是由于这两个语句长时间查询把连接数占满了。

//查看所有进程
show processlist;
SHOW FULL PROCESSLIST;
//查询是否锁表
show OPEN TABLES where In_use > 0;
//查看被锁住的
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
//等待锁定
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
kill 12041

#连接池最大连接数   最大连接数（ maxActive ）：连接池中最大允许的连接数；
spring.datasource.max-active=200
#空闲池中最大连接数
spring.datasource.max-idle=50
#空闲池中最小连接数  最小连接数（ minIdle ）：连接池中保持的最小连接数；
spring.datasource.min-idle=10
#初始连接数（ initialSize ）：连接池初始建立的连接数；
spring.datasource.initial-size=10
#连接在池中空闲最小时间后被清除   连接的空闲时间（minEvictableIdleTimeMillis）：连接在池中的最小空闲时间，超过该时间将被清除；
spring.datasource.min-evictable-idle-time-millis=60000
#隔多久时间清回收废弃连接         清除连接的时间间隔（timeBetweenEvictionRunsMillis）：定期清理连接的时间间隔。
spring.datasource.time-between-eviction-runs-millis=30000
#每次调用检测池里连接的可用性，假如连接池中的连接被数据库关闭了，应用通过连接池getConnection时会重新创建
#连接的有效性检查（testOnBorrow）：从连接池中获取连接时，是否对连接的有效性进行检查；
spring.datasource.testOnBorrow=true
spring.datasource.validation-query=SELECT 1
#移除被遗弃的连接
spring.datasource.remove-abandoned=true
#设置超时时间
spring.datasource.tomcat.remove-abandoned-timeout=60

DEMO模板配置

spring.datasource.min = 5
spring.datasource.max = 30
spring.datasource.validation-query = SELECT 1
spring.datasource.max-wait = 10000
spring.datasource.max-idle = 10
spring.datasource.min-idle = 5
spring.datasource.max-active = 50
spring.datasource.initial-size = 5
spring.datasource.test-on-borrow = true
spring.datasource.test-while-idle = true
spring.datasource.remove-abandoned = true
spring.datasource.remove-abandoned-timeout = 180
spring.datasource.time-between-eviction-runs-millis = 18800

spring.datasource.tomcat.min = 5
spring.datasource.tomcat.max = 30
spring.datasource.tomcat.validation-query = SELECT 1
spring.datasource.tomcat.max-wait = 10000
spring.datasource.tomcat.max-idle = 10
spring.datasource.tomcat.min-idle = 5
spring.datasource.tomcat.max-active = 50
spring.datasource.tomcat.initial-size = 5
spring.datasource.tomcat.test-on-borrow = true
spring.datasource.tomcat.test-while-idle = true

spring.datasource.tomcat.remove-abandoned = true
spring.datasource.tomcat.remove-abandoned-timeout = 180

spring.datasource.tomcat.time-between-eviction-runs-millis = 18800
spring.datasource.url = jdbc:sqlserver://xxxxx.sqlserver.rds.aliyuncs.com:端口;databaseName=数据库名称
spring.datasource.username = 用户名
spring.datasource.password = 密码
spring.datasource.driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver

mysql连接超时的属性设置
https://www.cnblogs.com/oktokeep/p/16858680.html

参数设置
属性名	描述	默认值
driverClassName	用户名	-
url			密码	-
username	建立连接的url	-
password	数据库驱动的完整类名	-
initialSize	连接器启动时创建的初始连接数	10
maxActive	最大连接数，通常为常规访问的最大数据库并发数，建议根据后期jmx监控逐渐调优	100
maxIdle		最大空闲连接数，比较难把握的一个参数，许多连接池也已经移除了此属性(如Druid)，访问峰值比较集中的系统如考勤可以设置小一点节省大部分时段的连接资源，过小也可能导致连接频繁创建关闭也会影响性能，建议一般系统不低于maxActive的50%	100
minIdle		最小连接数,一般与initialSize一致即可	10
maxWait		连接池中连接用完时，新的请求的等待时间，超时返回异常，单位毫秒	默认30000
testWhileIdle	连接进入空闲状态时是否经过空闲对象驱逐进程同时进行校验，推荐的校验方法，依赖validationQuery	false
validationQuery	在连接返回给调用者前用于校验连接是否有效的SQL语句，必须为一个SELECT语句，且至少有一行结果	-
validationQueryTimeout	连接验证的超时时间，单位秒，注：池本身并不会让查询超时，完全是依靠JDBC驱动来强制查询超时	-
validationInterval		TomcatJDBC特有属性，检查连接可用性的时间间隔，防止testOnBorrow和testOnReturn为true时检查过于频繁，单位毫秒	30000
timeBetweenEvictionRunsMillis	空闲对象驱逐检查时间间隔，单位毫秒	5000
minEvictableIdleTimeMillis		连接被空闲对象驱逐进程驱逐前在池中保持空闲状态的最小时间，单位毫秒	60000
defaultAutoCommit	连接池所创建的连接默认自动提交状态（JDBC缺省值意思是默认不会调用setAutoCommit方法）	JDBC缺省值
jmxEnabled			是否利用 JMX 注册连接池	true
jdbcInterceptors	TomcatJDBC特有属性， QueryTimeoutInterceptor（查询超时拦截器，属性queryTimeout，单位秒，默认1秒），SlowQueryReport（慢查询记录，属性threshold超时纪录阈值单位毫秒，默认1000），多个用拦截器用;分隔，示例：QueryTimeoutInterceptor(queryTimeout=5);SlowQueryReport(threshold=3000)注：当新语句创建时，自动调用Statement.setQueryTimeout(seconds)。池本身并不会让查询超时，完全是依靠JDBC驱动来强制查询超时，更详细的信息请查看官方文档	-
testOnBorrow	连接被调用时是否校验，依赖validationQuery，对性能有一定影响，不推荐使用	false
testOnReturn	连接返回到池中是时是否校验,依赖validationQuery，对性能有一定影响，不推荐使用	false
removeAbandoned	是否清除已经超过 removeAbandonedTimeout 设置的连接，可用于排查一些事务未提交的问题(正式环境谨慎使用,对性能有一定影响),不推荐使用，可用QueryTimeOut拦截器替代	false
removeAbandonedTimeout	清除无效连接的时间，单位秒 与removeAbandoned联合使用	60
defaultReadOnly	连接池创建的连接是否是否为只读，需要说明的是设置了true只是告诉数据库连接是只读，便于数据库做一些优化(例如不安排数据库锁)，并非不能执行更新操作，只是对数据的一致性的保护并不强而已（这跟spring的只读事务类似）	JDBC缺省

2.举例：
#移除被遗弃的连接
spring.datasource.remove-abandoned=true
#设置超时时间
spring.datasource.tomcat.remove-abandoned-timeout=60
最下面两个就是确实解决掉这个BUG所需要的配置，注释也稍微写了一下，是移除被遗弃的连接，超过60秒就被判断为遗弃的连接。这里的遗弃的连接就是在代码过程中写的比如没有及时关闭的连接之类的糟糕的写法。其实针对这个问题，体感上来说也确实是这么回事，连接用完之后，超过一个设定的时间就自动删掉。这点有点像Java线程池框架中的Executors.newCachedThreadPool()，设定的是线程存活60秒就自动删掉，之前试过在60秒之内疯狂增加线程数，并将线程数设定的比较小，一段时间后就报错了，跟今天连接池的这种情况确实是很像。

3.索引问题
一看索引竟然只有 id 索引，source_id 查询走的全表扫描。

 

4.慢sql
Mysql慢SQL堆积导致数据库连接池占满
慢SQL为什么会导致系统崩溃
连接占满（我是这种情况）
用show full PROCESSLIST 发现连接数已经占满了，而且连接占用的时间都比较长，就算是一个简单的sql也等待了很长时间还是没有执行完，所以导致无法获取连接的情况。
其中有一条语句重复堆积了80来个慢SQL,查询时间为50秒~600秒不等，且有一半在500-600秒的。
用kill 进程ID 把运行较慢的SQL杀掉，再重启数据库和应用服务，连接就正常了。

解决方式
使用show full processlist查看数据库连接占用情况
对连接时长较长的连接进行分析，KILL掉该连接
查看服务器监控和MYSQL监控，分析服务器CPU、内存、磁盘IO，分析MYSQL性能
对SQL进行优化，比如索引优化