Обсуждение: pgbouncer best practices
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.--
Rita wrote on 7/7/23 9:23 AM:
Does your application allow for transaction pooling? pgBouncer can unlock a lot more efficiency if you can cycle server slots on transaction boundaries.
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.
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
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.--
Rita wrote on 7/8/23 4:27 AM:
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
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
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.--
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