SmartClient connection pooling

[claudiobosticco]

Hello, in my tomcat cluster I've got more than one connection pool, shared via jndi by more than one application. So SmartClient connection pooling is disabled.

I've just realized that I can also enable it by setting sql.myDb.pool.enabled to true in server.properties.

It's not entirely clear to me what this setting will affect and if it's recommended to enable it.

[Isomorphic]

We're not quite clear on what you're asking - that setting enables pooling, and does so on a per-DB basis.

We would generally recommend just leaving pooling enabled (the default setting). With pooling enabled, the worst case is a redundant pool, which will have no measurable impact on performance. SmartClient's pooling has been proven in extremely high volume environments, so why rely on a separate pooling provider?

[claudiobosticco]

Hello, since I have several applications that use the same Oracle user, I have always used shared connection pools between applications, defined in the server.xml of Tomcat, as letting DBCP2 manage the pool seemed like the simplest and most efficient thing to do in this situation.
Obviously, using JNDI, SmartClient's pooling is disabled.
Now I've realized I can activate it (for each DB - sql.pool.enabled:true doesn't seem to have an effect in this case), and from what you're telling me, it seems not only possible but even advisable. Or maybe I'm getting it wrong?

I wonder how it would work: would SmartClient create a connection pool for each application, which would still go and borrow connections from the DBCP2 pool managed by the server, common to all applications? And would this bring advantages? What parameters would the SmartClient pool use? Its defaults or the same as the global pool?

Or would I have no advantage in my case? Instead, to leverage SmartClient's pooling, should I have exclusively one pool per application?

In the latter case, my problem is that I would need to understand how to size the individual pools of the applications.

I would also like to apologize for the many questions, perhaps naive.

[Isomorphic]

Basically, if you leave the JNDI pooling off and enable SmartClient pooling, you'd expect to see SmartClient grab connections from JNDI and keep them so they would no longer be available to other applications on the same server.

This will end up being similar to just having application-specific pooling instead of shared JNDI pooling.

This may mean more total connections to the DB, however, you really need to have a lot of connections before this matters.

SmartClient pooling uses Hikari, which is basically well-known to be the best pooling system. However, Hikari would no longer be directly in charge of the connections - when it frees connections, it's returning them to JNDI. So, since some of Hikari's efficiency has to do with being very smart about when and whether to directly sever connections to the database, those efficiencies would probably lost in this scenario.

Overall, you most likely will not notice a performance difference. Connection pooling behavior is critical in certain edge cases, for example, where a single DB is being hammered by lots of different apps and is close to its resource limits, so it's critical for every app to make due with a minimum number of connections and recycle connections quickly when unused.

However, most of the time and for most apps, nuances in pooling behavior make no measurable difference.