Обсуждение: Pgbouncer pool_mode and application behavior
Hi! Seeking advice on quite likely a really basic topic, but please excuse my ignorance. In our current setup, a number of java servers are connecting to the postgres instance via a hikari pool. There are bursts of activity where several javas require more connections than normal. Hikari creates the number of sessions per user it is allowed to, but it is not enough. The server engineers demand to increase the hikari pool sizes, but all that does is cause the connections to be denied as pg max_connections is a hard limit. I figured to add a pgbouncer between postgres and hikari to have pool_mode as transaction and to be able to control idle and idle in transaction queries. Then it turned out the servers are relying on session mode for advisory locking and prepared queries. Am I correct to guess, that pgbouncer in this case in pool_mode=session does not help at all and in order to improve throughput, server engineers really must implement support for transaction mode that pgbouncer provides? Best regards, -- Kristjan Mustkivi Email: kristjan.mustkivi@gmail.com
> On Jun 13, 2022, at 8:04 AM, Kristjan Mustkivi <sonicmonkey@gmail.com> wrote: > > Am I correct to guess, that pgbouncer in this case in > pool_mode=session does not help at all and in order to improve > throughput, server engineers really must implement support for > transaction mode that pgbouncer provides? You are correct that session mode will not solve the problem you described. There are 3 possibilities: - as you mentioned, changed clients to be compatible with pgbouncer transaction pooling - increase pg max_connections - increase timeouts, use retries, etc so that the servers are more resilient to being temporarily denied connections
Thank you, Scott! On Mon, Jun 13, 2022 at 5:33 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote: > > > On Jun 13, 2022, at 8:04 AM, Kristjan Mustkivi <sonicmonkey@gmail.com> wrote: > > > > Am I correct to guess, that pgbouncer in this case in > > pool_mode=session does not help at all and in order to improve > > throughput, server engineers really must implement support for > > transaction mode that pgbouncer provides? > > You are correct that session mode will not solve the problem you described. There are 3 possibilities: > > - as you mentioned, changed clients to be compatible with pgbouncer transaction pooling > > - increase pg max_connections > > - increase timeouts, use retries, etc so that the servers are more resilient to being temporarily denied connections -- Kristjan Mustkivi Email: kristjan.mustkivi@gmail.com