pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPgbouncer pool_mode and application behavior
3+ messages / 2 participants
[nested] [flat]
* Pgbouncer pool_mode and application behavior
@ 2022-06-13 14:04 Kristjan Mustkivi <sonicmonkey@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Kristjan Mustkivi @ 2022-06-13 14:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pgbouncer pool_mode and application behavior
@ 2022-06-13 14:33 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Kristjan Mustkivi <sonicmonkey@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Ribe @ 2022-06-13 14:33 UTC (permalink / raw)
To: Kristjan Mustkivi <sonicmonkey@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Pgbouncer pool_mode and application behavior
@ 2022-06-14 05:53 Kristjan Mustkivi <sonicmonkey@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 3+ messages in thread
From: Kristjan Mustkivi @ 2022-06-14 05:53 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-06-14 05:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-13 14:04 Pgbouncer pool_mode and application behavior Kristjan Mustkivi <sonicmonkey@gmail.com>
2022-06-13 14:33 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-06-14 05:53 ` Kristjan Mustkivi <sonicmonkey@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox