生产环境数据库连接15分钟断开问题排查_druid隔一段时间阻塞15分钟

1、背景介绍：

前段时间，生产跑批出现过一个问题，报错如下：

按照以往经验，初步定位是连接失效了，后期生产复现发现时间都很接近，为此引发一个问题， 这个接近15分钟的时间，到底是哪里定义的？
本地尝试复现，但是本地多次尝试没法复现。为此只能从整个sql 调用链路分析。

2、背景分析

初步分析，大的层面会有三个模块可能设置了该超时时间，

是应用层设置了超时时间
是应用服务器到数据库服务器，哪里设置了超时时间，中间网络偶发性抖动，导致连接断开
是数据库服务器设置了超时时间，访问时间过长，数据库服务器主动断开连接，导致客户端报错
spring管理的事务超时，即一个事务做了太多操作，导致事务超时

3、问题排查，配置分析

具体到每个模块：
在第一个模块，底层用到orm 是mybatis ，从druid里面获取连接，通过jdbc diiver访问数据库服务器，我们应用是通过spring管理的，那么直观上可能这四个地方设置了该超时时间，回顾项目相关参数：

mybatis 查询超时
defaultStatementTimeout：表示在MyBatis配置文件中默认查询超时间，单位秒，不设置则无线等待，如果一些sql需要执行超过defaultStatementTimeout可以通过Mapper文件单独的sql的timeout进行配置。
回顾项目相关配置确认，没有该配置项设置
druid 配置超时时间
maxWait：表示从数据库连接池取链接，连接池没有可用连接时的等待时间，默认值0，表示无限等待，单位毫秒，建议60000
核对项目相关配置，发现当时设置的maxwait 为一分钟，故此排除这个时间的可能

druid:
      initialSize: 10
      maxActive: 40
      maxWait: 60000
      minEvictableIdleTimeMillis: 300000
      minIdle: 10
      testOnBorrow: true
      testOnReturn: false
      testWhileIdle: true
      timeBetweenEvictionRunsMillis: 60000
      validationQuery: SELECT 'x'
      validationQueryTimeout: 3000
jdbc 层面超时配置
 connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间，默认值0，表示不设置超时，单位毫秒，建议30000
 socketTimeout：表示客户端和MySQL数据库建立socket后，读写socket时的等待的超时时间，linux系统默认的socketTimeout为30分钟，可以不设置超时时间
 这两个参数都是配置在我们连接数据库url地址上面的，经核对，当时生产上的参数地址为 
jdbc:mysql://x.x.x.x:3306/playbackdb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=PRC
可以看到，当时报错的地址也没有这个时间设置。那么如果默认的话，即为30分钟，但是生产是15分钟就抛异常了，所以猜测不是这个参数导致超时异常 
事务超时
 事务超时用于控制事务执行的超时，执行时间是事务内所有代码执行总和，单位为秒。 
4. 重新思考
 
重新回头看上面抛出的异常，直观上看到是我们的jdbc 连接，访问数据库的的上一个成功packet 是15分钟之前，那就是这个连接 15 分钟没有和数据库交互？为此我们猜测有下面几种可能 
 从druid连接池拿到的连接一个失效连接，这个连接15分钟之前和数据库成功交互，后来网络抖动或者什么导致连接断开，但是我们应用层获取到连接之后没有检查连接是否有效，导致调用的时候报错15分钟之前发包成功，后续发包失败报错
 从上面参数可以看到，我们配置了 testonborrow属性以及依赖属性，那意味着连接池获取的时候会判断连接是否生效，这里需要确定，你配置的参数正常应用到了连接池中，关于参数相关配置不生效，这里不再赘述，可以看我之前博客
 经过确认，连接池参数正常设置生效，所以这种可能暂时排除
 
 应用层拿到了正常的连接，但是访问的数据库太慢 ，中间网络偶发性抖动，导致访问到一半的数据断开，报错发包失败。这种在实际业务场景中是极有可能的，但是既然是偶发的，不应该时间也是随机的么，我们生产复现多次时间都是15分钟上下接近，这好像又排除了嫌疑，那么有没有可能，因为我们底层交互都是tcp交互，生产环境服务器和数据库服务器交互的底层tcp 时间是15分钟，超时后，tcp断了连接，和运维沟通后，确认我们生产tcp的超时时间是两小时 。。。。。。
 
 那么我们服务器到数据库服务器，中间会不会有其他中间件，导致了这个报错呢，经过和运维进一步沟通，发现我们生产环境服务器到数据库服务器，有一层负载均衡lvs，经过确认，阿里云上面这层lvs 上面有一个超时时间默认就是15分钟
  
至此，基本定位到是这个时间，为了验证是否确认，和运维沟通，临时去掉这层lvs 后调用正常，不会报错 
注意： 因为我们是数仓生产的一个跑批验证环境，没有实时业务进件，当时刚好有个几百G的大表，所以可以和运维沟通临时调整验证猜测。但是真实业务场景，更多的是解决数据访问慢导致的超时问题，不管是优化表结构，还是优化sql。而不是本末倒置，采取一些激进的方式达成目标。 
5.配置参数设置优先级思考
 
在上面分析问题的过程中，我们提到了一堆参数设置，为此，简单回顾一下上面几个维度的时间相关参数 
高级别的timeout依赖于低级别的timeout，只有当低级别的timeout无误时，高级别的timeout才能确保正常。例如，当socket timeout出现问题时，高级别的statement timeout和transaction timeout都将失效。 
1. Transaction Timeout
 
Spring提供的transaction timeout配置非常简单，它会记录每个事务的开始时间和消耗时间，当特定的事件发生时就会对消耗时间做校验，当超出timeout值时将抛出异常。
 假设某个事务中包含5个statement，每个statement的执行时间是200ms，其他业务逻辑的执行时间是100ms，那么transaction timeout至少应该设置为1，100ms(200 * 5 + 100)。 
2. Statement Timeout
 
statement timeout用来限制statement的执行时长，timeout的值通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) API进行设置。不过现在开发者已经很少直接在代码中设置，而多是通过框架来进行设置。
 在iBatis中，statement timeout的默认值可以通过sql-map-config.xml中的defaultStatementTimeout 属性进行设置。同时，你还可以设置sqlmap中select，insert，update标签的timeout属性，从而对不同sql语句的超时时间进行独立的配置。 
3. Socket timeout
 
设置的是jdbc I/O socket read and write operations的超时时间，防止因网络问题或数据库问题，导致driver一直阻塞等待。默认为0，即永远等待，所以这个参数一定要设置不为0！
 JDBC的socket timeout在数据库被突然停掉或是发生网络错误(由于设备故障等原因)时十分重要。由于TCP/IP的结构原因，socket没有办法探测到网络错误，因此应用也无法主动发现数据库连接断开。如果没有设置socket timeout的话，应用在数据库返回结果前会无期限地等下去，这种连接被称为dead connection。
 为了避免dead connections，socket必须要有超时配置。socket timeout可以通过JDBC设置，socket timeout能够避免应用在发生网络错误时产生无休止等待的情况，缩短服务失效的时间。不推荐使用socket timeout来限制statement的执行时长，因此socket timeout的值必须要高于statement timeout，否则，socket timeout将会先生效，这样statement timeout就变得毫无意义，也无法生效。 
4. connection timeout
 
建立socket连接的超时时间，单位为ms。在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，默认为0，即永远等待，所以这个参数一定要设置不为0！
 ps:: 笔者之前遇见过，因为没设置导致生产环境数据库爆发性增长，不到半小时几万个连接 
5. os socket timeout
 
这个是操作系统级别的socket设置(如果jdbc socket timeout没有设置，而os级别的socket timeout有设置，则使用系统的socket timeout值)。这里不止包含 操作系统底层的tcp超时，还包括应用服务器和数据库服务器两边的 负载均衡等中间件的超时时间 
不同级别的timeout越往下优先级越高，也就是说如果下面的配置比上面的配置值小的话，则会优先触发timeout，那么相当于上面的配置值就"失效"了。 
参考博客：
 https://www.csdn.net/tags/OtTaQg3sODQyMzQtYmxvZwO0O0OO0O0O.html
 https://www.jianshu.com/p/fe7d28c50c7c
 https://github.com/alibaba/druid/issues/1260
 https://www.oschina.net/question/1450045_2157629
 http://t.zoukankan.com/yinliang-p-10996210.html
 https://blog.csdn.net/yuxiao97/article/details/119742632
 https://blog.csdn.net/u014155356/article/details/82865451
 https://www.jb51.net/article/225175.htm
				问题描述我正在做的这个项目，数据库是跨区并且不由自己管理的。防火墙会每隔一段时间就自动断开数据库连接。 
于是需要对application.properties的datasource进行配置。配置及具体含义#初始化连接
spring.datasource.initial-size=10
#最大空闲连接
spring.datasource.max-idle=20
#最小空闲连接
spring.data
				解决方法：配置time-between-eviction-runs-millis和min-evictable-idle-time-millis
spring:
 datasource:
  url: xxx
  username: xxx
  password: xxx
  driverClassName: xxx
  tomcat:
    time-between-eviction-runs-...
Spring Boot + MyBatis连接MySQL数据库，静默15分钟后就会自动断开，日志中报错如下：
Failed to validate connection com.mysql.jdbc.JDBC4Connection@5eb7b0fb
(No operations allowed after connection closed.)
2. 解决方法
在SpringBoot的配置文件application.yml中的
database：
部分加入如下：
    hikari:
				你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：撤销：Ctrl/Command + Z
重做：Ctrl/Command + Y
加粗：Ctrl/Command + B
斜体：Ctrl/Command + I
标题：Ctrl/Command + S
				这两天遇到一个问题，早上来了登录我们的项目网站第一次登录总是登不上去，第二次就好了，我看了一下后台的报错信息是这样的：
  The last packet successfully received from the server was 57,704,088 
  milliseconds ago.  The last packet sent successfully to the server