How to close SQL connections of old Cloud Run revisions?

Question

Context

I am running a SpringBoot application on Cloud Run which connects to a postgres11 CloudSQL database using a Hikari connection pool. I am using the smallest PSQL instance (1vcpu/614mb/25connection limit). For the setup, I have followed these resources:

Connecting to Cloud SQL from Cloud Run

Managing database connections

Problem

After deploying the third revision, I get the following error:

FATAL: remaining connection slots are reserved for non-replication superuser connections

What I found out

Default connection pool size is 10, hence why it fails on the third deployment (30 > 25).
When deleting an old revision, active connections shown in the Cloud SQL admin panel drop by 10, and the next deployment succeeds.

Question

It seems, that old Cloud Run revisions are being kept in a "cold" state, maintaining their connection pools. Is there a way to close these connections without deleting the revisions?

In the best practices section it says:

...we recommend that you use a client library that supports connection pools that automatically reconnect broken client connections."

What is the recommended way of managing connection pools in Cloud Run, given that it seems old revisions somehow manage to maintain their connections?

Thanks!

How are you handling those connections in your code? Do you close them properly if they are not used or they remain always active? — siamsot
Hi, the connection pool is managed by Hikari (github.com/brettwooldridge/HikariCP). Currently, I'm not accessing the DB from the code, other than migrations (flyway) being run on startup. AFAIK Hikari attempts to maintain the configured number of connections (default = 10), even when they are not in use by the application. So to answer your question, I would assume they remain active. — Bastian Stein
Cloud Run does not do anything to inform your app, therefore, software shutdown hooks will not work. For Cloud run, open connection and close connection. Do not expect connection pooling, etc to work as intended. IMHO, the documentation should not recommend connection pooling for Cloud Run except for long running applications and even then you should be able to write code that follows the open then close principle. — John Hanley

kurtisvg kurtisvg · Accepted Answer · 2019-10-11T05:44:52

Currently, Cloud Run doesn't provide any guarantees on how long it will remain warm after it's started up. When not in use, the instance is severely throttled by not necessarily shutdown. Thus, you have some revisions that are holding up connections even when not being directed traffic.

Even in this situation, I disagree that with the idea that you should avoid using connection pooling. Connection pooling can lower latency, improve stability, and help put an upper limit on the number of open connections. Alternatively, you can use some of the following configuration options to help keep your pool in check:

minimumIdle - This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently.

maximumPoolSize - This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections.

idleTimeout - This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections.

If you set minimumIdle to 0, your application will still be able to use up to maximumPoolSize connections at once. However, once a connection is idle in the pool for idleTimeout seconds, it will be closed. If you set idleTimeout to something small like 1 minute, it will allow the number of connections your pool is using to scale down to 0 when not in use.

Hope this helps!

How to close SQL connections of old Cloud Run revisions?

2 Answers