Cloudsql-proxy: Feature: Connection Pooling

[mpskovvang]

@Carrotman42 pointed out in issue #87 that connection pooling might improve connection latency.

Is it possible that the cloudsql-proxy can handle this itself? If is it best practice it would benefit a lot of projects/users.

[Carrotman42]

There's still overhead in the mysql-/postgres- level connection handshakes
(after the encrypted handshake of the Proxy completes), so while a
connection pool implementation inside the Cloud SQL Proxy would reduce
latency it can't reduce latency as well as the many existing solutions for
connection pools. If you have access to a connection pool at the
application level, it will always be more efficient to use thag than
anything that we can provide at the Proxy level.

Now, not every environment/language/driver/framework supports connection
pooling so it could make sense to do pooling at the Proxy level in some
applications and scenarios. This is a feature we have discussed internally
but unfortunately have not had the time to invest into it. I will leave
this open to track how interested others are in such a feature.

[paulwalker]

It seems at least using the built in connection pooling in GoLang's database/sql built-in package does not work properly in GCE/GKE. https://github.com/jinzhu/gorm/issues/246

[Carrotman42]

I don't think that issue is relevant here. The Proxy is unaware of the
underlying database protocol (and drivers used in Go's database/sql know
about the underlying protocol), whereas this issue is requesting connection
pooling on the Proxy level (database agnostic, just for the encrypted
channel). Unless I'm misunderstanding, that issue is about the
database-aware pooling in database/sql having some problem; I suspect that
it is related to Keep-Alive somehow.

Can you explain more about how that issue is related to the Proxy?

[paulwalker]

Now, not every environment/language/driver/framework supports connection
pooling so it could make sense to do pooling at the Proxy level in some
applications and scenarios

I'm simply pointing out a scenario, within the Google ecosystem, a language invented by Google, in a container orchestration framework created by Google, in a cloud hosting service by Google where connection pooling is not working.

Can you explain more about how this is _not_ relevant?

[Carrotman42]

Ah I see, I mistakenly thought your initial post was an example of a case where implementing this feature request would help solve that issue. From my read of that issue (specifically https://github.com/jinzhu/gorm/issues/246#issuecomment-59162133), the problems arise when you are hosting your own database on GCE (or AWS, according to the comment) rather than when using Cloud SQL and the Cloud SQL Proxy (which only works with Cloud SQL, rather than self-hosted GCE/AWS).

As far my own testing has shown (and our regular probing inside Google continues to show), connection pooling via database/sql + Cloud SQL Proxy (+ Cloud SQL) works just fine. I'd be happy to investigate any issues you can find related to that combination of technology in another issue since I don't expect that to be broken. This issue is specifically about implementing connection pooling inside the Cloud SQL Proxy to alleviate any lack of connection pooling support in other client database drivers and libraries.

[mykolasmith]

You should try using an existing SQL connection pooling application. We previously had each of our microservices running a dedicated container for cloudsql-proxy. This resulted in a lot of extra overhead and extraneous connections to the database. We opted to instead run a single instance of cloudsql-proxy and pgbouncer (https://pgbouncer.github.io/), which proxied and pooled connections to cloudsql-proxy. The result is a robust connection pool with aggregated stats that essentially acts as a proxy to the proxy that all of our microservices can connect to. We are finding that this works very well. I would argue that it is not the job of this application (cloudsql-proxy) to handle connection pooling. It should simply act as an auth layer & proxy. If you need advanced features, stick an existing solution in front of this.

[paulwalker]

@mykolasmith Thanks for the tip! May I ask, do you have any golang clients that are using cloudsql-proxy natively?

[badoet]

hi @Carrotman42 i wrote that comments quite some time ago.
just to clarify, i was connecting to the Cloud SQL not setting it up in our own instance.
i cant quite remember the details of the application setup at the time and why i derive to that conclusion, but i think there were many other possible cause to this. e.g. at that time i was also developing locally (in Singapore) and Cloud SQL was in US.
and also it was quite some time ago, so im not sure if the problem still exists

[tsloughter]

@mykolasmith curious, since I'm looking to switch to this setup now, do you use the cloudsql-proxy simply as a sidecar to the pgbouncer container in a single pod?

[schmitch]

actually to bring this issue up again, currently the biggest problem when using pgbouncer is, is that cloud sql postgres actually does not allow to read pg_shadow from a privileged user (i.e postgres user is not a superuser). This makes it super hard to have pgbouncer setup without hardcoding passwords into config files.
currently that is the biggest problem when running pgbouncer, not cloud-sql-proxy per se.

[stekole]

@mykolasmith - how do you scale that pgbouncer/cloudsql-proxy container/box?

[mykolasmith]

@stekole Kubernetes. The Deployment/Service (pgbouncer) has two containers, pgbouncer + cloudsql-proxy. By scaling up the replicas on the Deployment, we get additional pgbouncer instances that create one or more connections to the cloudsql-proxy sidecar. The connection limit for GKE is ~100 actives, so we decided to have 3 replicas of pgbouncer running, each with a 20 connection limit. This keeps our active connections pegged at 60 and handles throttling at the pgbouncer layer rather than overload our cluster.

[unquietwiki]

Hey @mykolasmith , you might want to look into pgpool-II; testing that out now with gcp + read-replicas.

• https://dba.stackexchange.com/questions/58590/pgpool-replication-not-allowing-md5-authentication
• http://www.pgpool.net/docs/pgpool-II-3.7.1/en/html/runtime-config-load-balancing.html

[nicknovitski]

The max_connections flag is now available (in beta) for Cloud SQL PostgresQL, and can be set up to 262143. That may be enough for a lot of people.

[schmitch]

actually isn't there still a quota: https://cloud.google.com/sql/docs/quotas#fixed-limits ?

[axot]

I understand using pgbouncer could improve the connection efficiency for Postgre, is there any production ready proxy for MySQL?

[GrantSheehan]

I understand using pgbouncer could improve the connection efficiency for Postgre, is there any production ready proxy for MySQL?

ProxySQL is pretty robust + battle-tested, might be worth checking out.

[enocom]

We're not planning on implementing this feature. Others like pgbouncer or HAProxy will do this much better than the proxy ever would. Alternatively, application-level connection pooling is also a good option.