Running out of SQL connections with Quarkus and hibernate-reactive-panache

Question

I've got a Quarkus app which uses hibernate-reactive-panache to run some queries and than process the result and return JSON via a Rest Call. For each Rest call 5 DB queries are done, the last one will load about 20k rows:

    public Uni<GraphProcessor> loadData(GraphProcessor graphProcessor){
    return myEntityRepository.findByDateLeaving(graphProcessor.getSearchDate())
            .select().where(graphProcessor::filter)
            .onItem().invoke(graphProcessor::onNextRow).collect().asList()
            .onItem().invoke(g -> log.info("loadData - end"))
            .replaceWith(graphProcessor);
}

//In myEntityRepository
public Multi<MyEntity> findByDateLeaving(LocalDate searchDate){
    LocalDateTime startDate = searchDate.atStartOfDay();
    return MyEntity.find("#MyEntity.findByDate",
            Parameters.with("startDate", startDate)
                    .map()).stream();

}

This all works fine for the first 4 times but on the 5th call I get

 11:12:48:070 ERROR [org.hibernate.reactive.util.impl.CompletionStages:121] (147) HR000057: Failed to execute statement [$1select <ONE OF THE QUERIES HERE>]: $2could not load an entity: [com.mycode.SomeEntity#1]: java.util.concurrent.CompletionException: io.vertx.core.impl.NoStackTraceThrowable: Timeout
    at <16 internal lines>
io.vertx.sqlclient.impl.pool.SqlConnectionPool$1PoolRequest.lambda$null$0(SqlConnectionPool.java:202) <4 internal lines>
    at io.vertx.sqlclient.impl.pool.SqlConnectionPool$1PoolRequest.lambda$onEnqueue$1(SqlConnectionPool.java:199) <15 internal lines>
Caused by: io.vertx.core.impl.NoStackTraceThrowable: Timeout

I've checked https://quarkus.io/guides/reactive-sql-clients#pooled-connection-idle-timeout and configured quarkus.datasource.reactive.idle-timeout=1000

That itself did not make a difference. I than added quarkus.datasource.reactive.max-size=10

I was able to run 10 Rest calls before getting the timeout again. On a pool setting of max-size=20 I was able to run it 20 times. So it does look like each Rest call will use up a SQL connection and not release it again.

Is there something that needs to be done to manually release the connection or is this simply a bug?

Answer 1

The problem was with using @Blocking on a reactive Rest method. See https://github.com/quarkusio/quarkus/issues/25138 and https://quarkus.io/blog/resteasy-reactive-smart-dispatch/ for more information.

So if you have a rest method that returns e.g. Uni or Multi, DO NOT use @Blocking on the call. I had to initially add it as I received an Exception telling me that the thread cannot block. This was due to some CPU intensive calculations. Adding @Blocking made that exception go away (in dev-mode but another problem popped up in native mode) but caused this SQL pool issue.

The real solution was to use emitOn to change the thread for the cpu intensive method:

 .emitOn(Infrastructure.getDefaultWorkerPool())
 .onItem().transform(processor::cpuIntensiveMethod)