Use alongside with PgPool/PgBouncer

[grillorafael]

Hi All,

My use case is that I have one db instance accessed by many Scala apps and I want to have a connection pool between apps and instances of the app.

Each app is using hikariCp with a maxPool of 10 and I have ~10 apps using it. I have a PgPool in front but it doesn't seem to be reusing connection that much. It seems like each app instance is trying to maintain it's own pool alive all the time making PgPool useless.

How to deal with this use case?

[guizmaii]

Did you find a solution ?

[grillorafael]

Hey @guizmaii. Not yet.

I believe making the connection short lived might be one solution to the problem but it kind of defeats the purpose of Hikari I believe

[ernitishkumar]

Consider PgPool as a Database instead of Connection Pooler. Hikari is talking to PgPool thinking it as a DB not as a Connection pooler. PgPool lends connection to clients similarly how PostgreSQL Server will do.

[apollotonkosmo]

Hey everybody,

I have the same question, is using HikariCP with PGBounder actually any good? Is the one a replacement to the other or can they be used together? Thank you very much George

[grillorafael]

So our use case is that we have many instances of the same app, each instance have hikari but they all connect to a pgbouncer.

Seems to be able to improve a lot connections to the database compared with many instances of hikari

[apollotonkosmo]

We have the same case, a lot of dockers with hikaricp and connect to pgbouncer. Is that optimal? We have 10 pool for eachinstance and 400 pool for pgbouncer.

[ernitishkumar]

But @grillorafael How do you stopped HikariCp from pooling DB connections in the app ? Because if you are using both HikariCP in apps and pgbouncer as an external pooler than How the pooling is working since Hikari also caches connections and so do the pgbouncer !

[furlongce]

I have the same question. Does anyone have any input?

[ppasi07]

Watching this thread! We have the same issue.

[furlongce]

Anyone?

[munish-dhiman]

We are also using Hikari along with PGBouncer. Whenever our instance gets a request after remaining idle for some time, we are getting the following error.

Caused by: org.postgresql.util.PSQLException: Connection attempt timed out

[azraino]

Hi Guys, Any news about this issue?

[lfbayer]

You might have better luck getting answers on stackoverflow, as this isn't strictly an issue with HikariCP. Since the HikariCP maintainers don't have much experience with PGBouncer, they can't really give much advice.

[basilmusa]

You don't need an application-level load balancer if you are using PgBouncer. PgBouncer handles all connection pooling to the database. If you use HikarciCP with PgBouncer you will just interfere with what PgBouncer does best.

[shubham-mittal30]

We are using PGbouncer and Hikari CP for postgres db in spring boot application. We face Could not open JPA entity manager. Could not acquire JDBC connection. issues a lot. Any fixes or recommendations for this??

[ruslangm]

Hi All, we are using Hikari CP alongside with PgBouncer under quite heavy load. This combination works quite good and it's OK to have a proxy at the client and one at the server. You can read more in details about it here: https://gitter.im/pgbouncer/pgbouncer?at=5d5ef69dfa99e74f6a73b833 http://www.pgbouncer.org/faq.html#should-pgbouncer-be-installed-on-the-web-server-or-database-server

But to achieve maximum performance from this combination I would suggest you to use Transaction Pooling mode (not the default session one) on PgBouncer. This is the most optimal way if you have many users for your Postgres DB because it achieves optimal connection reusage (again, from our experience). Also it's possible to use Transaction Pooling mode with HikariCP (and not with Apache DBCP, Vibur or c3p0) because it doesn't use Prepared Statement caching: https://github.com/brettwooldridge/HikariCP#statement-cache

If you have any questions, feel free to @ me.

[sharkymcdongles]

Hi All, we are using Hikari CP alongside with PgBouncer under quite heavy load. This combination works quite good and it's OK to have a proxy at the client and one at the server. You can read more in details about it here: https://gitter.im/pgbouncer/pgbouncer?at=5d5ef69dfa99e74f6a73b833 http://www.pgbouncer.org/faq.html#should-pgbouncer-be-installed-on-the-web-server-or-database-server

But to achieve maximum performance from this combination I would suggest you to use Transaction Pooling mode (not the default session one) on PgBouncer. This is the most optimal way if you have many users for your Postgres DB because it achieves optimal connection reusage (again, from our experience). Also it's possible to use Transaction Pooling mode with HikariCP (and not with Apache DBCP, Vibur or c3p0) because it doesn't use Prepared Statement caching: https://github.com/brettwooldridge/HikariCP#statement-cache

If you have any questions, feel free to @ me.

@ruslangm

I tried using Hikari with transaction pooling, but I still get failures due to missing relations which seems quite strange as it works fine in session mode. My JDBC PSQL connection string has both: prepareThreshold=0 and preparedStatementCacheQueries=0

However, I still get weird errors from Hikari prepared statements methods(stacktrace shows the file included is preparedstatements java or something along those lines) that say there are missing relations. I suspect I need to also set these as well:

datasource:
        hikari:
          data-source-properties:
            cachePrepStmts: false
            useServerPrepStmts: false

Did you need to set this for your deployment?

I also read I should set these for jpa:

jpa:
        open-in-view: false
        generate-ddl: true
        hibernate:
          ddl-auto: update

But this didn't help either. Any guidance would be amazing!

Here is an example log snipper of what I am seeing:

{"@timestamp":"2022-04-09T18:14:04.939+00:00","@version":"1","message":"Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]","logger_name":"org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver","thread_name":"http-nio-8080-exec-10","level":"WARN","level_value":30000,"tenant_id":"tenant","caller":"ui-service","trace_id":"39717cc8ab6ca20b","user_id":"user","request_path":"/schema-service/users/user/user-roles","user_agent":"okhttp/4.2.2"}
{"@timestamp":"2022-04-09T18:14:06.039+00:00","@version":"1","message":"ERROR: relation \"custom_navigation\" does not exist\n  Position: 166","logger_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","thread_name":"http-nio-8080-exec-9","level":"ERROR","level_value":40000,"tenant_id":"tenant","trace_id":"6d0ed0111b7a7abf","user_id":"user","request_path":"/schema-service/custom-navigations","user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36"}
{"@timestamp":"2022-04-09T18:14:06.042+00:00","@version":"1","message":"Caught unhandled exception while handling a request","logger_name":"com.mls.services.springbot.starter.errorhandling.GlobalErrorHandlerAdvice","thread_name":"http-nio-8080-exec-9","level":"ERROR","level_value":40000,"stack_trace":"<#fb549f04> o.p.u.PSQLException: ERROR: relation \"custom_navigation\" does not exist\n  Position: 166\n\tat o.p.c.v.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)\n\tat o.p.c.v.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)\n\tat o.p.c.v.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)\n\tat o.p.jdbc.PgStatement.executeInternal(PgStatement.java:473)\n\tat o.p.jdbc.PgStatement.execute(PgStatement.java:393)\n\tat o.p.j.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)\n\tat o.p.j.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)\n\tat c.z.h.p.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat c.z.h.p.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat o.h.e.j.i.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)\n\t... 161 common frames omitted\nWrapped by: <#f2586c9c> o.h.e.SQLGrammarException: could not extract ResultSet\n\tat o.h.e.i.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)\n\tat o.h.e.i.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)\n\tat o.h.e.j.s.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)\n\tat o.h.e.j.s.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)\n\tat o.h.e.j.i.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)\n\tat o.h.loader.Loader.getResultSet(Loader.java:2303)\n\tat o.h.loader.Loader.executeQueryStatement(Loader.java:2056)\n\tat o.h.loader.Loader.executeQueryStatement(Loader.java:2018)\n\tat o.h.loader.Loader.doQuery(Loader.java:948)\n\tat o.h.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)\n\tat o.h.loader.Loader.doList(Loader.java:2849)\n\tat o.h.loader.Loader.doList(Loader.java:2831)\n\tat o.h.loader.Loader.listIgnoreQueryCache(Loader.java:2663)\n\tat o.h.loader.Loader.list(Loader.java:2658)\n\tat o.h.l.h.QueryLoader.list(QueryLoader.java:506)\n\tat o.h.h.i.a.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)\n\tat o.h.e.q.s.HQLQueryPlan.performList(HQLQueryPlan.java:219)\n\tat o.h.i.SessionImpl.list(SessionImpl.j...","tenant_id":"tenant","trace_id":"6d0ed0111b7a7abf","user_id":"user","request_path":"/schema-service/custom-navigations","user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36"}
{"@timestamp":"2022-04-09T18:14:06.042+00:00","@version":"1","message":"Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]","logger_name":"org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver","thread_name":"http-nio-8080-exec-9","level":"WARN","level_value":30000,"tenant_id":"tenant","trace_id":"6d0ed0111b7a7abf","user_id":"user","request_path":"/schema-service/custom-navigations","user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36"}
{"@timestamp":"2022-04-09T18:14:07.086+00:00","@version":"1","message":"ERROR: relation \"ext_user\" does not exist\n  Position: 318","logger_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","thread_name":"http-nio-8080-exec-2","level":"ERROR","level_value":40000,"tenant_id":"tenant","caller":"ui-service","trace_id":"455a8a66d417b070","user_id":"user","request_path":"/schema-service/users/user/user-roles","user_agent":"okhttp/4.2.2"}
{"@timestamp":"2022-04-09T18:14:07.089+00:00","@version":"1","message":"Caught unhandled exception while handling a request","logger_name":"com.mls.services.springbot.starter.errorhandling.GlobalErrorHandlerAdvice","thread_name":"http-nio-8080-exec-2","level":"ERROR","level_value":40000,"stack_trace":"<#183fdbd0> o.p.u.PSQLException: ERROR: relation \"ext_user\" does not exist\n  Position: 318\n\tat o.p.c.v.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)\n\tat o.p.c.v.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)\n\tat o.p.c.v.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)\n\tat o.p.jdbc.PgStatement.executeInternal(PgStatement.java:473)\n\tat o.p.jdbc.PgStatement.execute(PgStatement.java:393)\n\tat o.p.j.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)\n\tat o.p.j.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)\n\tat c.z.h.p.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat c.z.h.p.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat o.h.e.j.i.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)\n\t... 165 common frames omitted\nWrapped by: <#70d26834> o.h.e.SQLGrammarException: could not extract ResultSet\n\tat o.h.e.i.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)\n\tat o.h.e.i.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)\n\tat o.h.e.j.s.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)\n\tat o.h.e.j.s.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)\n\tat o.h.e.j.i.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)\n\tat o.h.loader.Loader.getResultSet(Loader.java:2303)\n\tat o.h.loader.Loader.executeQueryStatement(Loader.java:2056)\n\tat o.h.loader.Loader.executeQueryStatement(Loader.java:2018)\n\tat o.h.loader.Loader.doQuery(Loader.java:948)\n\tat o.h.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)\n\tat o.h.loader.Loader.doList(Loader.java:2849)\n\tat o.h.loader.Loader.doList(Loader.java:2831)\n\tat o.h.loader.Loader.listIgnoreQueryCache(Loader.java:2663)\n\tat o.h.loader.Loader.list(Loader.java:2658)\n\tat o.h.l.h.QueryLoader.list(QueryLoader.java:506)\n\tat o.h.h.i.a.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)\n\tat o.h.e.q.s.HQLQueryPlan.performList(HQLQueryPlan.java:219)\n\tat o.h.i.SessionImpl.list(SessionImpl.java:1414)...","tenant_id":"tenant","caller":"ui-service","trace_id":"455a8a66d417b070","user_id":"user","request_path":"/schema-service/users/user/user-roles","user_agent":"okhttp/4.2.2"}

[ruslangm]

Hey Brett, sorry for the late reply, I lost your message in my inbox.

Let me check this carefully and I’ll come back later.

Btw, do u have telegram or facebook messenger account? Probably it would be a bit more convenient to text about the problem in one of this.

On Apr 9, 2022 at 9:11 PM, <Bryan @.***)> wrote:

Hi All, we are using Hikari CP alongside with PgBouncer under quite heavy load. This combination works quite good and it's OK to have a proxy at the client and one at the server. You can read more in details about it here: https://gitter.im/pgbouncer/pgbouncer?at=5d5ef69dfa99e74f6a73b833 http://www.pgbouncer.org/faq.html#should-pgbouncer-be-installed-on-the-web-server-or-database-server

But to achieve maximum performance from this combination I would suggest you to use Transaction Pooling mode (not the default session one) on PgBouncer. This is the most optimal way if you have many users for your Postgres DB because it achieves optimal connection reusage (again, from our experience). Also it's possible to use Transaction Pooling mode with HikariCP (and not with Apache DBCP, Vibur or c3p0) because it doesn't use Prepared Statement caching: https://github.com/brettwooldridge/HikariCP#statement-cache

If you have any questions, feel free to @ me.

@ruslangm (https://github.com/ruslangm)

I tried using Hikari with transaction pooling, but I still get failures due to missing relations which seems quite strange as it works fine in session mode. My JDBC PSQL connection string has both: prepareThreshold=0 and preparedStatementCacheQueries=0

However, I still get weird errors from Hikari prepared statements methods(stacktrace shows the file included is preparedstatements java or something along those lines) that say there are missing relations. I suspect I need to also set these as well:

datasource: hikari: data-source-properties: cachePrepStmts: false useServerPrepStmts: false

Did you need to set this for your deployment?

I also read I should set these for jpa:

jpa: open-in-view: false generate-ddl: true hibernate: ddl-auto: update

But this didn't help either. Any guidance would be amazing!

— Reply to this email directly, view it on GitHub (https://github.com/brettwooldridge/HikariCP/issues/1042#issuecomment-1094098800), or unsubscribe (https://github.com/notifications/unsubscribe-auth/ACVS5E6BBLVJKLMYD7S6HOTVEHB57ANCNFSM4EIBDUEQ). You are receiving this because you were mentioned.Message ID: @.***>

[sharkymcdongles]

Okay I figured out the problem. For some reason, hikari had a default schema set, but hibernate did not. I added this and now it works:

  jpa:
    open-in-view: false
    properties:
      hibernate:
        cache:
          use_query_cache: false
        default_schema: navigation_schema_service

[ruslangm]

OK, cool. Glad you made it work eventually.

On Apr 11, 2022 at 2:23 PM, <Bryan @.***)> wrote:

Okay I figured out the problem. For some reason, hikari had a default schema set, but hibernate did not. I added this and now it works:

jpa: open-in-view: false properties: hibernate: cache: use_query_cache: false default_schema: navigation_schema_service

— Reply to this email directly, view it on GitHub (https://github.com/brettwooldridge/HikariCP/issues/1042#issuecomment-1094931742), or unsubscribe (https://github.com/notifications/unsubscribe-auth/ACVS5E3UXYV7RTTM3XLTXSDVEQDUBANCNFSM4EIBDUEQ). You are receiving this because you were mentioned.Message ID: @.***>

[ernitishkumar]

HikariCP sits within the application. Connections pooled by it is readily supplied to the demanding SpringBoot application which shares same process in the ram of a single server. Compared to PgBouncer/PgPool connection pooler which is outside the server/process(a different) one. Connections supplied by PgBouncer/PgPool will be over a network and will introduce a considerable lag in a highly traffic application. Generating considerable connection acquired time over time.

Hence in a distributed environment it makes sense to have individual connection pooler using HikariCP in each instance of the application, preferring performance over resource utilization. For example Suppose An application has two instances deployed on different servers A & B, each having its own pooled connection of 10. i.e 20 Database connections.

Another scenario using external poolers over network like pgBouncer/PgPool having only 10 Database pooled connections serving both A & B instance, but generating considerable network lag in a high traffic environment degrading performance.

Solutions to this will minimizing network lag in acquiring db connections from outside connection pooler !