Slick+HikariCP+Play导致Postgres在经常重新启动Play后连接不足

Question

我正在使用Slick和HikariCP开发一个用于连接池的Play应用程序。在重新启动几次Play之后，我的开发Postgres服务器的连接耗尽，显示

db_1  | 2019-11-19 21:06:46.583 GMT [395] FATAL:  remaining connection slots are reserved for non-replication superuser connections
db_1  | 2019-11-19 21:06:46.886 GMT [396] FATAL:  remaining connection slots are reserved for non-replication superuser connections
db_1  | 2019-11-19 21:06:48.167 GMT [397] FATAL:  remaining connection slots are reserved for non-replication superuser connections

我用SQL查询SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;进行了监控，空闲连接的数量似乎确实快速增加。我想解决这个问题，这样我在开发或生产中就不会有泄漏的连接。

关于如何修复空闲连接泄漏，有什么建议吗？

设置

build.sbt

我的build.sbt具有以下依赖项，

"com.typesafe.play" %% "play-slick" % "4.0.2",
"com.typesafe.play" %% "play-slick-evolutions" % "4.0.2",
"com.typesafe.slick" %% "slick-codegen" % "3.3.2",
"com.typesafe.slick" %% "slick" % "3.3.2",
"org.slf4j" % "slf4j-nop" % "1.7.26",
"com.typesafe.slick" %% "slick-hikaricp" % "3.3.2",
"org.postgresql" % "postgresql" % "42.2.8",

Application.conf

我的postgres配置存储在我的application.conf中

slick {
dbs {
default {
profile="slick.jdbc.PostgresProfile$"
db {
connectionPool = "HikariCP" //use HikariCP for our connection pool
profile = "org.postgresql.Driver"
dataSourceClass = "org.postgresql.ds.PGSimpleDataSource" //Simple datasource with no connection pooling. The connection pool has already been specified with HikariCP.
properties = {
serverName = "localhost"
portNumber = "5432"
databaseName = "website"
user = "websiteserver"
password = "397c9140fb0e2424396510b8d6e29a07aa1a92420027d3750ef1faed87bb617a"
}
}
numThreads = 10
connectionTimeout = 6000 // In the hope that this resolves the connection errors.
leakDetectionThreshold=60000 // In the hope that this resolves the connection errors.
}
}
}

播放应用程序

在我的play 2.7.3应用程序中，我使用加载数据库配置

@Singleton
class PersonRepositoryImpl @Inject() ()(implicit ec: PersonExecutionContext)
extends PersonRepository {
// We want the JdbcProfile for this provider
private val db = Database.forConfig("slick.dbs.default.db")
private val persons = TableQuery[PersonTable]
def create(p: Person)(implicit mc: MarkerContext): Future[PersonData] = db.run {
// Some operations on persons
} 
}

我尝试了许多不同的配置，但似乎都无法解决我面临的连接泄漏问题。

Answer 1

当您需要Database.forConfig作为依赖项时，您将其作为私有val调用。您应该利用play-slick依赖注入数据库配置提供程序：

@Singleton
class PersonRepository @Inject() (dbConfigProvider: DatabaseConfigProvider)(implicit ec: ExecutionContext) {
// We want the JdbcProfile for this provider
private val dbConfig = dbConfigProvider.get[JdbcProfile]
...
}

https://github.com/playframework/play-samples/blob/2.8.x/play-scala-slick-example/app/models/PersonRepository.scala#L15

另请参阅文档：

虽然您可以通过访问SlickApi手动获取DatabaseConfig实例，但我们为运行时DI用户(Guice、Scaldi、Spring等(提供了一些助手，用于在控制器中获取特定实例。

以下是如何为默认数据库(即配置中名为default的数据库(注入DatabaseConfig实例的示例：

class Application @Inject() (protected val dbConfigProvider: DatabaseConfigProvider, cc: ControllerComponents)(
implicit ec: ExecutionContext
) extends AbstractController(cc)
with HasDatabaseConfigProvider[JdbcProfile] {
}