Connection Pooling: PgBouncer or JDBC

Why Use Connection Pooling

With recent customer additions we have had to deal with lots of scaling challenges the last few months. One puny fraction of that effort has been around tuning our database connection pools. In very short, if your database server is overwhelmed with concurrent connections it can get bogged down in context switching and resource/io waiting such that overall throughput or transactions per second can drop. In the best case things just get slow. In the worst case things completely lock up because the server just can’t service all of those connections or your client code starts throwing exceptions because the database has reached it maximum configured connection count.

One way to control the maximum number of connections hitting your database at once is via connection pooling. This can ensure a consistent load that your database can dispatch quickly to keep a better overall throughput going. You can also match your aggregate pool sizes to the maximum connection count configured in your database server to ensure your client code never asks for more than the DB can give. The other HUGE advantage to connection pooling is that you are essentially caching the overhead of obtaining new connections. To the database there is a set number of connections made and held open – the pooling software then distributes those pooled connections to clients that need them and when the clients are done the connection remains open and waiting to be used again. This avoids a surprisingly high amount of overhead.

There is definitely a trade off. If your application needs to have a certain number of connections simply because you have too many concurrent users it is time to tune your code/queries/indexes (HUGE gains can always be found there), push stuff into distributed caches, or buy new database hardware so you can service more connections. There are recommendations online of a connection count as low as 2-4 per core. We’ve settled in around 30 per core and that seems to be a sweet spot for stability and performance. With 8 cores we can sustain well over 1000 transactions per second on a single PostgreSQL server (running on decent but not exceptional hardware).

We use JDBC connection pooling with c3p0. Each of our application servers has its own pool. We ensure that the sum of those pool sizes matches the connections that the database allows and also equals our desired connections per core count.

We explored using PgBouncer instead. This differs in that the connection pooling happens on the server – clients just ask for as many connections as they need talking to PgBouncer instead of directly to PostgreSQL.

You cannot use both JDBC pooling and PgBouncer or other server-side pooling. Or more correctly you can but there is no point. Say you set PgBouncer to maintain a pool of 500 connections. If you have JDBC pooling in the mix that just means you have to tune your individual client JDBC pools such that the sum of their pools doesn’t exceed 500. If that is the case, why use PgBouncer? Just use PostgreSQL and set it to handle 500 connections. Instead you would drop JDBC pooling and just have your clients ask for whatever they wanted while PgBouncer did the pooling and connection management.

Comparison

In deciding to move to PgBouncer or stick with c3p0 I ran a few tests. Here are those results. The goal here was to compare the overhead of obtaining connections not to determine the overall best solution by throughput. That would require significantly different and more involved tests. We were considering a move to PgBouncer and just wanted to consider any difference in connection overhead. These tests were done with a pool size of 50 and with client code connecting to a separate database server on the same subnet and physical switch.

First Test
Ran 500 queries with each query being a simple select 1. This simple query means most of the time is connection overhead so its a good test to decide between pooling solutions.

Second Test
Ran 500 queries where the query is selected sequentially from an array of 5 distinct queries varying from very simple to very complex. Highly imperfect test, but trying to see how connection overhead factors into a more normal load.

So c3p0 definitely offers the fastest solution. Additionally PgBouncer definitely makes a difference over not using connection pooling (and thus having to obtain a new connection with each query). This makes some sense as c3p0 is closer to the client code and keeps the pooled connections local. With PgBouncer the clients have to hit the network to queue up with the server.

Conclusion

We stuck with c3p0. But in my opinion where PgBouncer really shines is on the administrative side. As we continue to add more servers a move to PgBouncer or something similar seems likely. It (and PgPool-II, another solution) offer other capabilities as well. PgPool-II even offers built-in load balancing of reads against replicated copies of a DB.

Say you have 10 application servers and 1 database server configured for 500 maximum connections. You set the individual application server pool sizes to 50. Now you need to add a new application server. You would have to tune all 10 of your old servers dropping their pool sizes down to make room for the new one. This problem gets more and more severe as you add more servers.

Also, tuning individual pool sizes per client means there is a chance that one server is starving while others have pooled connections sitting idle – the connections aren’t shared. Probably not a big issue if you are load balancing user connections to your application servers evenly but still a possibility.

As you get more and more machines the server-layer pooling with PgBouncer definitely makes more sense. It also is obviously client language neutral so if you are running in an environment that doesn’t have mature pooling solutions its a great option.