如何解决在使用Hikari连接池时出现的“Socket read timed out”错误。

huangapple go评论75阅读模式
英文:

How to solve "Socket read timed out" when using hikari connection pool

问题

我正在使用Play Framework(版本2.8.0),Java(版本1.8)以及Oracle数据库(版本12C)开发应用程序。

每天只有零次或一次对数据库的访问,但我遇到了以下错误。

java.sql.SQLRecoverableException: IO错误:套接字读取超时
    at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919)
    at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005)
    at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:138)
    at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:447)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketTimeoutException: 套接字读取超时
    at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174)
    at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101)
    at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80)
    at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98)
    at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)
    at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908)
    ... 6 common frames omitted

db {
default {
driver=oracle.jdbc.OracleDriver
url="jdbc:oracle:thin:@XXX.XXX.XXX.XX:XXXX/XXXXXXX"
username="XXXXXXXXX"
password="XXXXXXXXX"
hikaricp {
  dataSource {
    cachePrepStmts = true
    prepStmtCacheSize = 250
    prepStmtCacheSqlLimit = 2048
      }
     }
  }
}

看起来是由于数据库连接处于非活动状态引起的。我应该如何解决这个问题?
如果需要其他信息,请告诉我。

英文:

I am developing an application using play framework (version 2.8.0), java(version 1.8) with an oracle database(version 12C).

There is only zero or one hit to the database in a day, I am getting below error.

java.sql.SQLRecoverableException: IO Error: Socket read timed out
    at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919)
    at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005)
    at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:138)
    at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:447)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketTimeoutException: Socket read timed out
    at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174)
    at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139)
    at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101)
    at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80)
    at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98)
    at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)
    at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908)
    ... 6 common frames omitted

db {
default {
driver=oracle.jdbc.OracleDriver
url="jdbc:oracle:thin:@XXX.XXX.XXX.XX:XXXX/XXXXXXX"
username="XXXXXXXXX"
password="XXXXXXXXX"
hikaricp {
  dataSource {
    cachePrepStmts = true
    prepStmtCacheSize = 250
    prepStmtCacheSqlLimit = 2048
      }
     }
  }
}

It seems it is causing due to inactive database connection, How can I solve this?
Please let me know if any other information is required?

答案1

得分: 2

你可以为JDBC启用TCP keepalive - 通过设置JVM指令或在连接字符串中添加"ENABLE=BROKEN"来实现。

但是:

  • 通常情况下,思科/Juniper在连接不活动超过一个小时时会断开TCP连接。
  • 而Linux内核在两小时后(tcp_keepalive_time)开始发送keepalive探测。因此,如果你决定打开TCP keepalive,你还需要以root身份更改内核可调参数为较低的值(10-15分钟)。
  • 此外,HikariCP不应保持任何连接超过30分钟 - 默认情况下是这样的。

因此,如果您的防火墙、Linux内核和HikariCP都使用默认设置,那么您的系统中不应该出现此错误。

请参阅HikariCP 官方文档

> maxLifetime:
> 此属性控制池中连接的最大生命周期。正在使用的连接永远不会被弃用,只有在关闭连接时才会被移除。针对每个连接,会应用轻微的负面衰减,以避免在池中发生大规模连接消失。我们强烈建议设置此值,并且它应该比数据库或基础架构设定的连接时间限制短几秒钟。值为0表示没有最大生命周期(无限生命周期),当然还要受到idleTimeout设置的影响。允许的最小值为30000毫秒(30秒)。默认值:1800000(30分钟)

英文:

You can enable TCP keepalive for JDBC - either be setting JVM directive or by adding "ENABLE=BROKEN" into connection string.

But:

  • Usually Cisco/Juniper cuts off TCP connection when it is inactive for more that on one hour.
  • While Linux kernel starts sending keepalive probes after two hours(tcp_keepalive_time). So if you decide to turn tcp keepalive on, you will also need root to change this kernel tunable to lower value(10-15 minutes)
  • Moreover HikariCP should not keep open any connection for longer than 30 minutes - by default.

So if your FW, Linux kernel and HikariCP all use default settings, then this error should not occur in your system.

See HikariCP official documentation

> maxLifetime:
> This property controls the maximum lifetime of a connection in the
> pool. An in-use connection will never be retired, only when it is
> closed will it then be removed. On a connection-by-connection basis,
> minor negative attenuation is applied to avoid mass-extinction in the
> pool. We strongly recommend setting this value, and it should be
> several seconds shorter than any database or infrastructure imposed
> connection time limit. A value of 0 indicates no maximum lifetime
> (infinite lifetime), subject of course to the idleTimeout setting. The
> minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30
> minutes)

答案2

得分: 0

我已在配置文件中添加了以下HikariCP的配置,并且它正常工作。

## 数据库连接池

play.db.pool = hikaricp
play.db.prototype.hikaricp.connectionTimeout = 120000
play.db.prototype.hikaricp.idleTimeout = 15000
play.db.prototype.hikaricp.leakDetectionThreshold = 120000
play.db.prototype.hikaricp.validationTimeout = 10000
play.db.prototype.hikaricp.maxLifetime = 120000
英文:

I have added the below configuration for hickaricp in configuration file and it is
working fine.

## Database Connection Pool

play.db.pool = hikaricp
play.db.prototype.hikaricp.connectionTimeout=120000
play.db.prototype.hikaricp.idleTimeout=15000
play.db.prototype.hikaricp.leakDetectionThreshold=120000
play.db.prototype.hikaricp.validationTimeout=10000
play.db.prototype.hikaricp.maxLifetime=120000

huangapple
  • 本文由 发表于 2020年6月29日 14:53:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/62632709.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定