postgresql - jdbc-HikariCP : Seeing low insert thoughput while DB/App cpu % is still low

Question

我设置了 doobie + hikaricp，其设置与默认设置类似。通过调试日志记录，我可以看到有多少连接处于事件/空闲状态，这也反射(reflect)了 pg_stat_activity 通过此查询返回的内容

SELECT
  pid,
  query,
  query_start,
  state
FROM pg_stat_activity;

HikariPool-1 - Pool stats (total=10, active=4, idle=6, waiting=0)

我的应用程序主要处理来自流的消息，方法是在提交前通过由 3 次插入和 2 次选择组成的事务将它们存储在数据库中。

下面是如何使用 hikariCP 创建事务处理程序:

val hikariConfig = new HikariConfig()
hikariConfig.setJdbcUrl(dbUrl)
hikariConfig.setDriverClassName("org.postgresql.Driver")
hikariConfig.setUsername(dbUser)
hikariConfig.setPassword(dbPassword)
hikariConfig.setMaximumPoolSize(10)
hikariConfig.setAutoCommit(false)
hikariConfig.addDataSourceProperty("socketTimeout", "30")

val dataSource: HikariDataSource     = new HikariDataSource(hikariConfig)
val transactor: HikariTransactor[IO] = HikariTransactor[IO](dataSource)

然后这个事务处理器被传递到任何地方来处理数据库事务:

import doobie._
import doobie.implicits._
import doobie.postgres.implicits._

val query = for {
  _ <- sql"insert into foo (id, value) values (fooId, 'b')".update.run
  _ <- sql"insert into bar (id, value) values (barId, 'b')".update.run
  _ <- sql"select * from bar where id = $barId".query[Bar].unique
  _ <- sql"insert into bazz (id, value1, value2) values (bazzId, fooId, barId)".update.run
  _ <- sql"select * from bazz where id = $barId".query[Bazz].unique
} yield ()

query.transact(transactor).unsafeToFuture()

我看到的问题是查询看起来很快但插入吞吐量很慢。

我注意到的几点:

1. 检查 pg_stat_activity，我看到许多 COMMIT 查询(多于插入)，每个查询耗时约 90 毫秒 - 200 毫秒。

   SELECT pid, now() - pg_stat_activity.query_start AS duration, query, query_start, state FROM pg_stat_activity where state <> 'idle' 按持续时间排序；

2. 上面的 pg_stat_activity 查询一次只返回 4-8 行，大部分结果是 query = 'COMMIT'。

3. 从空流开始，吞吐量可以高达每分钟 30k 条消息。但是，在 15-20 分钟后，吞吐量下降到每分钟 3k 条消息，并且不会重置恢复，直到流为空数小时后重新启动应用程序或扩展应用程序的更多实例不会提高吞吐量。

数据库和应用程序的 CPU (10%)/内存使用率 (16%) 都很低，因此理论上吞吐量应该可以更高，哪些方面值得研究？

目前使用doobie 0.5.4, hikariCP 0.5.4

更新:根据 Laurenz 的建议，在 rds 数据库上提高了 commit_delay 和 commit_siblings 设置，这将高吞吐量的时间从 30 分钟延长到 60 分钟，然后才长时间减少与以前一样的吞吐量。我开始测试提高 max_wal_size 但这似乎导致更高的吞吐量(有时非常低)不接近原始启动率。

Answer 1

如果 COMMIT 花费很长时间，则清楚地表明您的 I/O 系统已过载。

如果问题不是 I/O 量，则可能是来自许多事务的许多 WAL 同步请求。

一些选项:

• 以更少、更大的事务执行工作。

• 如果您可以承受在发生崩溃时丢失半秒的已提交事务，请将 synchronous_commit 设置为 off。

  <

• 如果不能选择丢失已提交的事务，请调整 commit_delay 和 commit_siblings。这会使事务花费的时间稍长，但可以减少 I/O 负载。