public inbox for [email protected]
help / color / mirror / Atom feedConnection pool branch merged in psycopg3
2+ messages / 2 participants
[nested] [flat]
* Connection pool branch merged in psycopg3
@ 2021-03-12 16:10 Daniele Varrazzo <[email protected]>
2021-03-12 18:00 ` Re: Connection pool branch merged in psycopg3 Rory Campbell-Lange <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Daniele Varrazzo @ 2021-03-12 16:10 UTC (permalink / raw)
To: psycopg
Hello,
I have just merged the connection pool branch to main in psycopg3. I
am very happy about this object and I think it's an immense
improvement compared to psycopg2 pool. The documentation is already
online:
- behaviour description:
https://www.psycopg.org/psycopg3/docs/advanced/pool.html
- API: https://www.psycopg.org/psycopg3/docs/api/pool.html
In the next days I would like to create an installation for load
testing and to showcase the metrics that can be obtained from it.
I would welcome any feedback about its design, implementation,
documentation. I would also like to thank everyone who already gave a
feedback, in the previous conversations about its design: receiving it
was immensely valuable and a great guide. Thank you!
-- Daniele
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Connection pool branch merged in psycopg3
2021-03-12 16:10 Connection pool branch merged in psycopg3 Daniele Varrazzo <[email protected]>
@ 2021-03-12 18:00 ` Rory Campbell-Lange <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Rory Campbell-Lange @ 2021-03-12 18:00 UTC (permalink / raw)
To: Daniele Varrazzo <[email protected]>; +Cc: psycopg
On 12/03/21, Daniele Varrazzo ([email protected]) wrote:
> I have just merged the connection pool branch to main in psycopg3. I
> am very happy about this object and I think it's an immense
> improvement compared to psycopg2 pool. The documentation is already
> online:
>
> - behaviour description:
> https://www.psycopg.org/psycopg3/docs/advanced/pool.html
Hi Daniele
Thanks for the really well written description.
I've got a few comments having just been through some hairy rewiring of
pgbouncer pools on a busy production system.
First of all the pool stats sound great. requests_waiting and
requests_wait_ms are pretty crucial to have when things are going wrong.
pgbouncer has a reserve_pool concept with an associated
reserve_pool_timeout. These are rather odd because although you can help
"iron out" an initial spike of connections, after they are in the pool
they full pool is available until the equivalent of max_idle. I'm
unconvinced by their purpose so (although I may well be wrong) aren't
worth having.
I see that the max_size/pool_max settings are quite different from
maximum database connections, with connections that cannot be contained
in the pool queuing for a place in the pool, and that these "non-pooled"
connections are controlled through timeout, max_waiting and so on. That
sounds really good too. I was going to suggest a max_total_connections
but max_waiting kind of does that for you when put together with
max_size etc.
Maximum connections is of considerable interest where there are more
than 1 app connecting to the database. The combined result of many
connections can quickly become devastating for the database server (as
the Oracle video you linked to neatly shows).
One major performance boost we receive, in areas where our code can
support it, is pgbouncer "transaction" rather than "session" pools. The
"session" pool type keeps a per-client connection for the lifetime of
the client's statements or until the pool connection timeout is up. This
is important where "set search_path = xyz;" and "select * from
fn_get_complex_data;" are issued in sequence and the second statement
needs to depend on the previous one. The alternative (since our code
doesn't support the third, "statement", mode) of transaction pool mode
gives us very high performance through connection re-use at a
transaction level. (I found the heroku explanation of this quite
helpful, at https://devcenter.heroku.com/articles/best-practices-pgbouncer-configuration).
Is it worth considering pool types in this way?
Regards
Rory
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-12 18:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-12 16:10 Connection pool branch merged in psycopg3 Daniele Varrazzo <[email protected]>
2021-03-12 18:00 ` Rory Campbell-Lange <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox