数据库mysql调优

问题描述:

      mysql dba在mysql服务端启用了连接在空闲一定时间 (10分钟) 后就自动关闭连接(连接失效)的功能导致java端连接池在空闲一段时间后连接被自动关闭(自动失效)。为了避免这种情况出现可以在dbcp上配置空闲的时候检测连接池线程功能。
           一些新建的数据库DBA设置了超时空闲失效时间即一个连接超过多长时间空闲该连接将失效而我们的很多程序使用的是DBCP连接池

默认参数的情况下 连接池是不会自动去检测某个连接是否失效的。这样程序如果获到的是已经失效的链接将会出现报错。

解决原理
DBCP使用Apache的ObjectPool作为Connection Pool的实现在构造GenericObjectPool的时候会生成一个Inner Class Evictor实现Runnable的接口。如果属性_timeBetweenEvictionRunsMillis > 0每过_timeBetweenEvictionRunsMillis毫秒后Evictor会调用evict method检查Object的idle time是否大于属性_minEvictableIdleTimeMillis毫秒如果_minEvictableIdleTimeMillis设置为<=0则忽略使用default value 30分钟如果是则销毁该Object

数据连接池默认配置带来的坑testOnBorrow=falsecloes_wait 终于解决了
服务性能优化总结

总结

1.testOnBorrow能够确保我们每次都能获取到可用的连接但是如果设置为true则每次获取连接时候都要到数据库验证连接有效性这在高并发的时候会造成性能下降可以将testOnBorrow设置成falsetestWhileIdle设置成true + timeBetweenEvictionRunsMillis+numTestsPerEvictionRun这样能获得比较好的性能。

2.testOnBorrow和testOnReturn在生产环境一般是不开启的主要是性能考虑。失效连接主要通过testWhileIdle保证如果获取到了不可用的数据库连接一般由应用处理异常。

mysql调优中关注的参数

validationQuery
用来检测连接是否有效的sql如果validationQuery为空那么testOnBorrow、testOnReturn、testWhileIdle这三个参数都不会起作用因为这三个参数都是通过执行参数validationQuery指定的SQL来验证数据库连接的有效性配置参考validationQuery=SELECT 1

testWhileIdle
建议配置为true。对性能影响很小因为是定期检查。如果连接空闲时间大于timeBetweenEvictionRunsMillis指定的毫秒就会执行参数validationQuery指定的SQL来检测连接是否有效。

testOnBorrow
建议配置为false。获取连接时执行validationQuery检测连接是否有效这个配置会降低性能。

testOnReturn
建议配置为false。归还连接时执行validationQuery检测连接是否有效这个配置会降低性能。

timeBetweenEvictionRunsMillis
(设置的Evict线程的时间单位毫秒即每隔多少时间去检测一次空闲连接是否超时默认值为-1即不开启)
minEvictableIdleTimeMillis
(空闲逐出 连接池的时间单位毫秒默认30分钟)
numTestsPerEvictionRun
设定在进行后台对象清理时每次检查几个链接。默认值是3.
如果numTestsPerEvictionRun>=0, 则取numTestsPerEvictionRun 和池内的链接数 的较小值 作为每次检测的链接数
如果numTestsPerEvictionRun<0则每次检查的链接数是检查时池内链接的总数乘以这个值的负倒数再向上取整的结果。