Slowly leaking connections - How to get a grip?

[ErrorProne]

Hi everyone,

over the past months we've built an application which controls an assembly line. We are now slowly starting into production and recognized that we leak DB connections and I can't get a grip on it.

The blue line is the "agroal_active_count" and green "agroal_available_count" from the metrics extension. The cut on the 27. is me restarting the application.

What I've seen so far:

• It looks like the DB pool is very slowly not reusing connections, since the active count is increasing despite the fact that there are available connections.
• I would have expected the active count to decrease over the night. The available count matches the working hours in the factory pretty good and after that should the DB pool not release some of the connections?
• Looking at postgres activity output I can see that we get more and more connections but they are all idle and not associated with any transaction or active query.
• We have not a single leaked connection warning in the log and all method calls finish in <1s
• We only use @transactional and in some places QuarkusTransaction.run()

Our datasource configuration is pretty straight forward and default

  datasource:
    db-kind: postgresql
    jdbc:
      url: "jdbc:postgresql://localhost:5432/wcs?assumeMinServerVersion=9.0"
      max-size: 100

Does anyone have any pointers or ideas what settings to tune to get more information about what is going on?

Environment:

• quarkus 2.10.2.Final
• openJDK 17.0.4 (with ZGC)
• PostgreSQL 14
• UberJar-Deployment

[gsmet]

/cc @barreiro @Sanne

[Sanne]

You have configured max-size: 100, in this case Agroal will slowly build up the number of active connections up to having 100.
So I'm not sure if this is a "leak" - but it seems to grow suspiciously indeed.

Is it possible to verify how your system would behave if you were to set a lower number? Is it ever going to stabilize at the maximum, or does it get in trouble?

It looks like the DB pool is very slowly not reusing connections, since the active count is increasing despite the fact that there are available connections.

How do you know there are "available connections"? the green line on your plots look very low, close to zero in many instants, so I'd expect it indeed to need to enlarge the pool further.

Are you sure there are no long running processes that are actively using them?

[ErrorProne]

I'm still on my journey to pin this further down. It is somehow related to the SSE endpoint if the request is cancelled. But I've found a second endpoint which did something similar and did not have the problem that connections remained as active in the pool.
So it looks like there is more to it

[Sanne]

I suspect you're hitting some limitations in mixing reactive programming with the blocking JDBC datasource; in particular it's a bit tricky to define were a scope is terminated when having a reactive stream, but I'd love to be able to reproduce it to see if we can do anything to make this easier.

Do you think you could narrow it down to a reproducer? I suspect this relates to how exactly the JDBC datasource is accessed from the reactive stream - indeed forgetting @Transactional or otherwise marking it as a blocking operation would result in erroneus behaviour; I need to ensure if we can make this less errorprone.

[Sanne]

I don't think it's the same issue, but there's some similarities with #27166

[ErrorProne]

Do you think you could narrow it down to a reproducer?

I'll try to extract the essential part to reproduce it. But I'm not sure if I've time for it today.

[Sanne]

Thanks! and no rush