数据库超时排查

背景：

   项目是用的springboot，毗连池用的是hikaricp，且数据库毗连做了LB设置，之前就是常常会有数据库出现标题，专家给到的解决方案。
  数据毗连io超时报错，排查了当时数据库各项指标都无显示非常，且也没有获取到当时的queryId，给出的解决方案是增加重试机制 ，但是成本太高，故本身根据日期排查下标题，日志如下
  

错误信息:

1. org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.<MSG>Read timed out

复制代码

这条日志信息表明在与PostgreSQL数据库通讯时发生了I/O错误，具体是读操作超时 (Read timed out)。
具体调用栈跟踪:

1. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:383)

复制代码

这个调用来自PostgreSQL JDBC 驱动程序，具体是在实行查询的时候抛出的非常。

1. at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)

复制代码

位于 PgStatement 类，该类负责实行SQL语句。

1. at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)

复制代码

这个方法负责实行平凡的SQL语句。

1. at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)

复制代码

表示这是通过PreparedStatement实行的SQL语句。

1. at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)

复制代码

具体实行的是更新操作（executeUpdate 表示是INSERT、UPDATE或DELETE等更新操作）。
           JDBC4的isValid方法来测试毗连是否可用，是通过向数据库服务器发送一个ping哀求来实现的。这个ping哀求的实现方式大概因数据库厂商而异，但通常包罗向数据库服务器发送一个简朴的网络数据包，以测试毗连是否正常。
          JDBC4的isValid方法的原理是基于底层网络毗连的有用性进行检测，它利用了底层协议的心跳机制来检测毗连的有用性。当调用isValid方法时，JDBC驱动程序会发送一个心跳包到数据库服务器，等候数据库服务器的响应。假如在指定的超时时间内收到了响应，则以为毗连是有用的，否则以为毗连已经失效。 

1. at org.postgresql.jdbc.PgConnection.isValid(PgConnection.java:1465)

复制代码

这里可以看到驱动程序在检查毗连是否有用。（到这里就可以定位是校验获取的毗连有用性出了标题，也就可以明确了当时为什么查不到queryId了）

1. at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:161)

复制代码

HikariCP毗连池在检查某个毗连是否仍然活跃。

1. at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
2. at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
3. at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)

复制代码

HikariCP毗连池尝试获取一个毗连。这个毗连大概因为前述错误而失败。

1. at com.baomidou.dynamic.datasource.ds.ItemDataSource.getConnection(ItemDataSource.java:56)
2. at com.baomidou.dynamic.datasource.ds.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:48)

复制代码

显示利用的是动态数据源管理工具（比方MyBatis-Plus Dynamic Datasource)，尝试从HikariCP毗连池获取毗连。

1. at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159)
2. at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117)
3. at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)

复制代码

Spring JDBC 工具类尝试获取毗连，从而对数据库进行操作。

1. at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80)
2. at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67)

复制代码

MyBatis在Spring管理的事务中打开一个毗连。同样反映出当前利用了Spring事务管理。

1. at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:345)
2. at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:93)

复制代码

MyBatis实行器获取毗连并准备实行SQL语句。

1. at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:68)
2. at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)

复制代码

MyBatis实行查询操作，并从数据库中获取效果。
总结

从上述日志信息中，我们可以看到操作流程如下：

• 应用程序利用 MyBatis 和 Spring 获取数据库毗连。
  
• 利用 HikariCP 作为毗连池管理数据库毗连。
  
• 在 HikariCP 中检查毗连是否有用时，发生了 I/O 错误（读超时）。
  
• 这导致 PostgreSQL 驱动程序抛出 PSQLException 非常。
  

这通常表示网络标题、数据库设置标题或者客户端设置标题。需要进一步检查网络毗连、数据库以及毗连池的干系设置来排查具体缘故原由。


具体的缘故原由没定位出来，但是现在可以通过设置提升用户体感

2. hikari:
4.         # 数据库连接有效性校验超时时间(ms)  默认是5秒
6.         validation-timeout: 500

复制代码

---

HikariCP 是怎么检查毗连是否有用

HikariCP 是一个高性能的 JDBC 毗连池，在管理数据库毗连的过程中，它提供了一些机制来检查毗连是否有用，以包管毗连的可用性和稳定性。下面将具体先容 HikariCP 如何检查毗连是否有用：
1. Connection Test Options

HikariCP 提供了几个参数来设置毗连的检测和验证，这些设置项资助确保毗连池中的毗连是可用的：

• connectionTestQuery    测试语句，不保举设置  
  
• validationTimeout      默认5秒   验证是否有用的超时时间
  
  
  
  • 最小设置为1秒毫秒数据会转为秒
    
  • final int validationSeconds = (int) Math.max(1000L, validationTimeout) / 1000;
    
  
  
• idleTimeout       默认10分钟
  
  
  
  • 这个属性控制毗连池中空闲毗连的最大空闲时间，只有当毗连池中毗连数目大于最小毗连数目(minimumIdle)时会收效
     
    
  
  
• maxLifetime        默认30分钟
  
  
  
  • 这个属性控制毗连池中一个毗连的最大生存时间，当一个毗连的生存时间大于这个值且没有正在被利用时，将会被关掉 与idleTimeout区别
    max-lifetime控制毗连的总的生命周期，无论当前毗连数是否大于最小毗连数目，都会关掉生命周期完结的毗连，idle-timeout只控制空闲且大于最小毗连数目的那部分毗连
    
  
  
• connectionTimeout    默认30秒   
  
  
  
  • 此属性控制客户端等候来自毗连池的毗连的最大毫秒数。假如高出这个时间而没有毗连可用，将抛出SQLException。最低可担当的毗连超时时间是250毫秒。默认值:30000(30秒)
    
  
  
• leakDetectionThreshold      默认0  不开启毗连泄漏检测 
  

2. 无需显式设置的默认活动

HikariCP 默认情况下利用 JDBC 驱动程序提供的 isValid 方法来验证毗连的有用性。isValid 方法通过尝试与数据库进行简朴的通讯验证毗连是否有用。
3. 设置 connectionTestQuery

在某些情况下，具体的 JDBC 驱动程序大概不支持 isValid 方法，或者你想利用自定义的查询语句来验证毗连。这时可以利用 connectionTestQuery 参数。

1. HikariConfig config = new HikariConfig();
2. config.setJdbcUrl("jdbc:postgresql://localhost:5432/yourdb");
3. config.setUsername("yourusername");
4. config.setPassword("yourpassword");
5. config.setConnectionTestQuery("SELECT 1");
6. HikariDataSource dataSource = new HikariDataSource(config);

复制代码

4. 工作方式

当 HikariCP 需要验证毗连时，它会实行以下步调：

• 默认利用 isValid 方法：
  
  1. boolean isValid = connection.isValid(validationTimeout);

  复制代码
  这个方法会利用数据库驱动提供的 isValid 方法，可以设置 validationTimeout 来指定超时。假如毗连在规定时间内响应，那么这个毗连被以为是有用的。
  
• 利用自定义 connectionTestQuery：
  假如设置了 connectionTestQuery，HikariCP 会实行该查询来验证毗连。这个查询应该是快速并且无副作用的，比方 SELECT 1。假如实行成功，这个毗连被以为是有用的。具体代码逻辑如下：
  
  1. try (Statement statement = connection.createStatement()) {
  2.     statement.executeQuery(connectionTestQuery);
  3.     // If the query executes successfully, the connection is valid
  4. } catch (SQLException e) {
  5.     // If query execution fails, the connection is considered invalid
  6. }

  复制代码
  
  

5. 利用 validationTimeout

HikariCP 提供了 validationTimeout 参数来设置毗连验证的超时时间：

1. config.setValidationTimeout(5000);  // 设置验证超时时间为5秒

复制代码

6. 毗连池中的毗连检查周期

HikariCP 在以下情况会进行毗连检查：

• 获取新毗连：当应用哀求新毗连时，先检查当前毗连是否有用。
  
• 闲置毗连：利用 idleTimeout 设置检查闲置毗连是否应该被移除。
  
• 最大毗连生命周期：利用 maxLifetime 设置确保毗连在规定的生命周期内利用，高出时间一律关闭，以制止埋伏的资源泄漏或者某些数据库的限制。
  

1. config.setIdleTimeout(600000);      // 闲置10分钟后移除连接
2. config.setMaxLifetime(1800000);     // 连接最大生命周期为30分钟
3. config.setConnectionTimeout(30000); // 连接超时设置为30秒

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。