CODEWITHSUNDEEP
postgresqlgoogle-cloud-sql
Pieter Laeremans
Pieter Laeremans

Reputation: 21

How to find connection leaks on PostgreSQL cloud sql

I’m using Postgres provisioned by Google Cloud SQL, Recently we see the number of connections to increase by a lot.

Had to raise the limit from 200 to 500, then to 1000. In Google Cloud console Postgres reports 800 currenct connections.

However I have no idea where these connections come from. We have one app engine service, with not a lot of traffic at the moment accessing it, another application hosted on kubernetes. And a dozen or so batch jobs that connect to it. Clearly there must be some connection leakage somewhere.

Is there any way I can see from where these connections originate ?

All applications connecting to it are Java based at the moment. They use the HikariCP connection pool. I’m considering changing the “test query”upon connection to insert a record in a log table. Hence I could perhaps find out from where the connections originate.

But are there better ways available?

Thanks,

Upvotes: 0

Views: 1420

Answers (1)

Maxim
Maxim

Reputation: 4441

Consider monitoring connection activity with pg_stat_activity, i.e: SELECT * from pg_stat_activity;.

As per the documentation:

Connections that show an IP address, such as 1.2.3.4, are connecting using IP. Connections with cloudsqlproxy~1.2.3.4 are using the Cloud SQL Proxy, or else they originated from App Engine. Connections from localhost are usually to a First Generation instance from App Engine, although that path is also used by some internal Cloud SQL processes.

Also, take a look at the best practices for managing database connections that contain information on opening and closing connections, connection count, or on how to set a connection duration in the Java programming language.

Upvotes: 1

Related Questions