Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Odyssey – Scalable PostgreSQL connection pooler

github.com

Post reply on HN
Print view View original

21–30 of 42 posts

Re: Odyssey – Scalable PostgreSQL connection pooler

#21

Post by craigkerstiens »

nh2 wrote: post #15

Out of curiosity, is there a point in using a connection pooler if your application does not follow the PHP approach to things? That is, if you don't create a new DB connection for each HTTP request, but instead create one (or a few) connections at webserver startup time, which can serve all coming requests?

Yes, especially with Postgres. Within Postgres an idle connection still creates overhead both in terms of contention as well as resource consumption. Each connection you make even if it's not doing anything can consume around 10MB of memory from your database. A DB side pooler can help reduce that overhead by allowing only your active connections through. You can get a better idea of the details and how to monitor idle vs. active connections in this post - https://www.citusdata.com/blog/2017/05/10/scaling-connection...

Re: Odyssey – Scalable PostgreSQL connection pooler

#22

Post by arvidkahl »

I have been looking into this (and pgpool2 and pgbouncer) and what I found most suprising was both the lack of workable Docker images and any hint of a SaaS solution for this problem.

Connection Pooling as a Service, why does this not exist? What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security?

Re: Odyssey – Scalable PostgreSQL connection pooler

#23

Post by LoSboccacc »

polthrowaway wrote: post #6
LoSboccacc wrote: post #2

> Advanced transactional pooling > Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback that's my biggest issue with pgbouncer, is there a docker image for it?

is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction. i tested this on my local machine and if i drop a connection while it is inside a trans…

I’m unsure on the specifics and it might very well be an interaction between jdbc or something, what I know is that if I change the connection reuse settings from session to transaction I get leakage and eventual exhaustion, so currently I’ve a pgbouncer in session mode on every node and connect each to the backend. That uses a little more connection on the server but for now is not critical so I haven’t investigated in deep and if easier I’d just hop to an equivalent because we’re really short on hands right now.

Re: Odyssey – Scalable PostgreSQL connection pooler

#24

Post by kodablah »

michelpp wrote: post #20
kodablah wrote: post #10

Earlier quoted context omitted.

I should have clarified, I meant at the protocol level. It's basically a state machine, and I want a "break" to reset the state machine. This would flush remaining query results, close named prepared statements, rollback any in-process transactions, and put the state back at ready-for-query. In the meantime, those with connections pools (this lib, my client-side lib, etc) have to keep this info (often except prepared…

That is what DISCARD ALL does, it discards all session state.

I am talking about the protocol, not a query. What if the state of the connection is not even at ready-for-query state, how do I issue a query? There is no DISCARD ALL in the protocol [0]. You might have only read 3 rows and still have a thousand to flush, or you might have sent a Parse and are awaiting a ParseComplete. This is the type of thing that a Postgres connection pooler has to keep track of. Here's some of their code: https://github.com/yandex/odyssey/blob/master/sources/reset.... (note how it has to get back to ready-for-query state before rolling back).

0 - https://www.postgresql.org/docs/current/static/protocol-mess...

Re: Odyssey – Scalable PostgreSQL connection pooler

#25

Post by zrail »

arvidkahl wrote: post #22

I have been looking into this (and pgpool2 and pgbouncer) and what I found most suprising was both the lack of workable Docker images and any hint of a SaaS solution for this problem. Connection Pooling as a Service, why does this not exist? What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security?

Heroku sort of offers this, in so far as they will manage a pgbouncer instance in front of your Heroku Postgres database. It’s not generalized.

Re: Odyssey – Scalable PostgreSQL connection pooler

#26

Post by graystevens »

arvidkahl wrote: post #22

I have been looking into this (and pgpool2 and pgbouncer) and what I found most suprising was both the lack of workable Docker images and any hint of a SaaS solution for this problem. Connection Pooling as a Service, why does this not exist? What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security?

Latency would be the big issue here. You’d likely have to spin up instances in lots of key data centres to try and combat this...

Each AWS location, each Google Cloud location.. and that’s not considering those that are colocating their own kit.

Interesting idea though, but you’d end up having to bundle it with DBaaS, at which point you’ve got the pressure and stress of having to look after everyone else’s data.

Re: Odyssey – Scalable PostgreSQL connection pooler

#27

Post by spullara »

nh2 wrote: post #15

Out of curiosity, is there a point in using a connection pooler if your application does not follow the PHP approach to things? That is, if you don't create a new DB connection for each HTTP request, but instead create one (or a few) connections at webserver startup time, which can serve all coming requests?

There is another case where you have so many stateless servers that an individual database shard can't handle the number of connections. For example, if you have 1000 frontends that all need to talk to a sharded database and you only want to support 100 connections per shard you need to put concentrators inbetween the frontend tier and the database to reduce the number of connections.

Re: Odyssey – Scalable PostgreSQL connection pooler

#28

Post by doh »

vageli wrote: post #19
doh wrote: post #14

Earlier quoted context omitted.

At a certain scale the pooling becomes the bottleneck. PGB has to keep state of the connection and manage it’s life time. All this currently happens on a single core. So it doesn’t help you to tune postgres itself if PGB doesn’t keep up. Many solve this by running multiple instances of PGB (usually each on a dedicated processor) and use some kind of load balancing (haproxy, DNS, ...) to balance the connections. This…

Interesting to see how you guys do things. My team has taken the opposite approach, deploying pgbouncer as an ECS service with multiple containers per host, fronted by an lb.

We also did it this way before. However we paid for it in latency, and because we operate in cloud, network micro-outages. Once we moved it directly to the Postgres servers and deployed this fork, we saw pretty significant overall improvements.

Re: Odyssey – Scalable PostgreSQL connection pooler

#29

Post by foobarbazetc »

nh2 wrote: post #15

Out of curiosity, is there a point in using a connection pooler if your application does not follow the PHP approach to things? That is, if you don't create a new DB connection for each HTTP request, but instead create one (or a few) connections at webserver startup time, which can serve all coming requests?

Yes, because limiting connections by putting them in a queue is how you maintain good response time.

Re: Odyssey – Scalable PostgreSQL connection pooler

#30

Post by foobarbazetc »

LoSboccacc wrote: post #23
polthrowaway wrote: post #6

Earlier quoted context omitted.

is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction. i tested this on my local machine and if i drop a connection while it is inside a trans…

I’m unsure on the specifics and it might very well be an interaction between jdbc or something, what I know is that if I change the connection reuse settings from session to transaction I get leakage and eventual exhaustion, so currently I’ve a pgbouncer in session mode on every node and connect each to the backend. That uses a little more connection on the server but for now is not critical so I haven’t investigated…

You need to make sure you turn off prepared statements if you want to use pgbouncer in transaction mode with pgjdbc.

FWIW we use it in exactly that way to serve many thousands of rps with no issues.

Post reply on HN