Thread: pgbouncer best practices

pgbouncer best practices

From
Rita
Date:
I have an  application that does many db calls  from a server farm. I've increased my max connections on postgresql to 1000 and tuned the server accordingly. However, I still get can't connect to postgresql some times. I installed pgbouncer on few servers in the farm. I pointed the traffic to pgbouncer and things helped dramatically. My question are: is there a ratio of max connections and pool i should use in my pgbouncer config? 

--
--- Get your facts first, then you can distort them as you please.--

Re: pgbouncer best practices

From
Ben Chobot
Date:
Rita wrote on 7/7/23 9:23 AM:
I have an  application that does many db calls  from a server farm. I've increased my max connections on postgresql to 1000 and tuned the server accordingly. However, I still get can't connect to postgresql some times. I installed pgbouncer on few servers in the farm. I pointed the traffic to pgbouncer and things helped dramatically. My question are: is there a ratio of max connections and pool i should use in my pgbouncer config?

Does your application allow for transaction pooling? pgBouncer can unlock a lot more efficiency if you can cycle server slots on transaction boundaries.

Re: pgbouncer best practices

From
Gurjeet Singh
Date:
On Fri, Jul 7, 2023 at 9:24 AM Rita <rmorgan466@gmail.com> wrote:
>
> My question are: is there a ratio of max connections and pool i should use in my pgbouncer config?

Short answer: No, there's no recommended ratio for PG max_connections
and pgbouncer pool size.

In pgbouncer, a client connection is matched (forwarded) to a server
connection in the pool. But if all the server connections in the pool
are already busy servicing other client connections, then any newly
active client connections are made to wait until a server connection
is available.

Depending on your workload, there are recommendations for how large to
set the max_connections on Postgres. But for connection poolers like
pgbouncer, there really isn't a recommended upper limit. Since every
connection (whether idle or active) takes up memory (~2kB), the only
upper limit is how much RAM you have available for pgbouncer to use.

It's been a while since I used pgbouncer, so there might be
inconsistencies here. Someone with more current knowledge may want to
correct me.

Best regards,
Gurjeet
http://Gurje.et



Re: pgbouncer best practices

From
Rita
Date:
I am not sure if it allows transaction pooling. 

On Fri, Jul 7, 2023 at 12:37 PM Ben Chobot <bench@silentmedia.com> wrote:
Rita wrote on 7/7/23 9:23 AM:
I have an  application that does many db calls  from a server farm. I've increased my max connections on postgresql to 1000 and tuned the server accordingly. However, I still get can't connect to postgresql some times. I installed pgbouncer on few servers in the farm. I pointed the traffic to pgbouncer and things helped dramatically. My question are: is there a ratio of max connections and pool i should use in my pgbouncer config?

Does your application allow for transaction pooling? pgBouncer can unlock a lot more efficiency if you can cycle server slots on transaction boundaries.


--
--- Get your facts first, then you can distort them as you please.--

Re: pgbouncer best practices

From
Ben Chobot
Date:
Rita wrote on 7/8/23 4:27 AM:
I am not sure if it allows transaction pooling.

Well, take the time to figure it out. With transaction pooling enabled, you can get away with a much lower number of server connections. For example, our application regularly has thousands of clients connected to pgbouncer and is quite happy with ~30 server connections (roughly the core count of the db server). If we couldn't use transaction pooling we'd be fighting with how to keep those thousands of connections from wasting a bunch of resources on our db.

https://www.pgbouncer.org/features.html

Re: pgbouncer best practices

From
Rita
Date:
Thanks for the tips. So far, I am very happy with PGbouncer. Just increased number of db connections. Great piece of software!

Is there a way to see how many queued connections there are? Looking at the stats I can't seem to figure that out.

On Sat, Jul 8, 2023 at 9:46 AM Ben Chobot <bench@silentmedia.com> wrote:
Rita wrote on 7/8/23 4:27 AM:
I am not sure if it allows transaction pooling.

Well, take the time to figure it out. With transaction pooling enabled, you can get away with a much lower number of server connections. For example, our application regularly has thousands of clients connected to pgbouncer and is quite happy with ~30 server connections (roughly the core count of the db server). If we couldn't use transaction pooling we'd be fighting with how to keep those thousands of connections from wasting a bunch of resources on our db.

https://www.pgbouncer.org/features.html


--
--- Get your facts first, then you can distort them as you please.--

Re: pgbouncer best practices

From
Gurjeet Singh
Date:
On Sun, Jul 9, 2023 at 6:28 AM Rita <rmorgan466@gmail.com> wrote:
>
> Is there a way to see how many queued connections there are? Looking at the stats I can't seem to figure that out.

pgbouncer exposes quite a few statistics via the SHOW commands [1].
See if any of those help.

[1]: https://www.pgbouncer.org/usage.html#show-commands

Best regards,
Gurjeet
http://Gurje.et