Re: Recommendations for PGBouncer interacting with HikariCP

On Fri, 23 Aug 2019 at 17:54, Steven Schlansker <stevenschlansker(at)gmail(dot)com>
wrote:

>
> > On Aug 23, 2019, at 1:39 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> >
> > On Wed, 21 Aug 2019 at 21:03, Joseph Hammerman <
> jhammerman(at)squarespace(dot)com> wrote:
> > Hi pgsql-jdbc email distribution list,
> >
> > We have recently added PGBouncer into our stack. We have done this in
> part, to protect the database. Our Java userbase is accustomed to using
> HikariCP / Hibernate for connection pooling. They would prefer to continue
> to use the connection pooler, in order to not have GC churn on connection
> spin up and tear down (in addition to additional latency introduced by
> having to perform the three-way handshake more often).
> >
> >> Is there any published material / does anyone have any knowledge of how
> these tools should interact or not interact?
> >
> > JDBC should work fine with pgbouncer.
>
> Most things do, although there are some caveats. For example if you have
> anything attached to your server session *outside* of a txn
> (say, server-side prepared statements or session vars)
> and you configure your pgbouncer for transaction pooling, you can end up
> creating some extremely difficult to understand behaviors.
>
>
> https://pgbouncer.github.io/faq.html#how-to-use-prepared-statements-with-transaction-pooling
>
> So you do have to be a little bit careful.
>
> >
> >> HikariCP wants to keep many long running idle_in_transaction
> connections open, which saturates the backend connection pool.
> >
> > That's strange why is it keeping them in transaction ? One would think
> once you closed (returned the connection) it should commit it.
>
> My personal experience working with JDBC and HikariCP is that 99.9% of the
> time, "idle in transaction" means that some programmer somewhere
> actually did not close their resource. I did end up finding numerous
> bugs, both in my $WORK's code as well as in Java libraries we use, although
> to the
> best of my memory the roots of problems we found were never in either
> PgJDBC nor HikariCP.
>
> To diagnose these nasty issues, we took a pretty extreme method: on every
> checkout from the HikariCP pool, we generate a UUID and
> print a tagged stack trace to a file. We then set `application_name` to
> "app_name-$UUID".
>
> Excellent idea!

> When our DBAs found an "idle in txn" connection that we did not know where
> it came from, we would immediately have the app and could
> then look up the stack trace of the last allocation site using the
> application_name in pg_stat_activity.
>
> From there it was usually straightforward, if painful, to diagnose the
> problem: "Oh I opened a Hibernate session and handed it off to a Reactive
> actor that threw an exception but it never got handled and so the
> Connection was never closed and the whole thing got leaked. Whoops. But
> look at how *fast* Reactive is!!!"
>

Interesting

>
> Hope that gives you some ideas.
>

Thanks for the insight

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com