r2dbc-pool: Using the r2dbc connection pool slows down the execution of parallel transactions

59 Views Asked by At

I am trying to optimize the reuse of PostgreSQL connections by implementing the r2dbc Connection Pool for jOOQ. But I noticed that transaction execution speed tends to be better whenever I use just a ConnectionFactory without ConnectionPool.

My configuration of СonnectionPool to transfer them to jOOQ DSL:

    // Connection Factory
    val dbConnections = 145 // Max number of connections
    val connectionFactory: ConnectionFactory = ConnectionFactories.get(
      ConnectionFactoryOptions
        .parse("r2dbc:postgresql://$dbHost:$dbPort/$dbName")
        .mutate()
        .option(ConnectionFactoryOptions.USER, dbUser)
        .option(ConnectionFactoryOptions.PASSWORD, dbPassword)
        .build()
      )
    // Connection Pool
    val configuration = ConnectionPoolConfiguration.builder(connectionFactory)
      .maxIdleTime(Duration.ofMinutes(30))
      .initialSize(dbConnections)
      .maxSize(dbConnections)
      .maxCreateConnectionTime(Duration.ofSeconds(1))
      .build()
    val connectionPool = ConnectionPool(configuration)
    // jOOQ DSL Context
    val dslContext = DSL.using(DefaultConfiguration().set(connectionPool).set(SQLDialect.POSTGRES))

Implementation parallel chunked insertions to DB:

    coroutineScope {
      measureTime("Uploading ${orders.count()} orders to the database using $dbConnections of database connections") {
        orders.chunked(orders.size / dbConnections).map { chunk ->
          async {
              orderDao.saveAll(chunk)
          }
        }.awaitAll()
      }
    }

Implementation of saving all items in OrderDao in a transaction:

  suspend fun saveAll(chunk: List<Order>) {
    ctx
      .transactionCoroutine { trx ->
        chunk.forEach { order ->
          val orderRecord = insertOrder(trx, order)
          insetRecordChildren(trx, order, orderRecord)
        }
      }
  }

As a result of executing the code, I get the following result:

Total orders: 19992. Data size: 103 MB. Average row size: 5 KB
Uploading 19992 orders to the database using 145 database connections: 
43.293s spent.

And when I execute the same code with the same parameters by passing the сonnectionFactory to jOOQ DSL instead of connectionPool, I get a much better result.

DSL.using(DefaultConfiguration().set(connectionFactory).set(SQLDialect.POSTGRES))
27.426s spent.

I can't figure out why using connection Pool gives a worse result. Maybe someone has an idea what I'm doing wrong. I would really appreciate it!

0

There are 0 best solutions below