CODEWITHSUNDEEP
springpostgresqlspring-bootgoogle-cloud-platformhikaricp
hasan.alkhatib
hasan.alkhatib

Reputation: 1559

GCP Postgres connections consumed when stressing spring boot service (using SQL Cloud proxy)

I'm working on a Spring Boot service to post data and persist it in a GCP SQL Postgres database. The problem is when I stress the service with requests, I get an SQL exception about consuming all available connections:

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

I figured out the issue and I added a proper hikari configuration to limit used connections and set a limit for when to close idle connections, here is my properties.yml configuration:

 type: com.zaxxer.hikari.HikariDataSource
 hikari:
   initializationFailTimeout: 30000
   idle-timeout: 30000
   minimum-idle: 5
   maximum-pool-size: 15
   connection-timeout: 20000
   max-lifetime: 1000

The service works fine with this setup when I run it locally with the same database, but it consumes all available connections when I run it from my cloud setup, and then get the same exception.

IMPORTANT! I'm using SQL cloud proxy to connect to the database.

Here are screenshots for the database available connections:

1- before running the service

enter image description here

2- after running the service locally

enter image description here

3- after running the service from the cloud enter image description here

Upvotes: 0

Views: 998

Answers (1)

hasan.alkhatib
hasan.alkhatib

Reputation: 1559

After a few days of investigating this problem, we found a solution that is mitigating the problem but not solving it completely (the ideal solution is mentioned in the end).

If you want to keep using SQL Cloud proxy, then you need to accept the fact that you don't have full control over your database connections configuration, as SQL cloud proxy might keep those connections alive for more than you've configured it (source).

To mitigate this problem, we used SQL Cloud proxy 1.19.2 from the docker registry, and we used this hikari configuration:

hikari:
  idle-timeout: 30000 # maximum amount of time (in milliseconds) that a connection is allowed to sit idle in the pool
  minimum-idle: 1 # minimum number of idle connections that HikariCP tries to maintain in the pool, including both idle and in-use connections. If the idle connections dip below this value, HikariCP will make a best effort to restore them quickly and efficiently
  maximum-pool-size: 15 # maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend
  connection-timeout: 20000 #maximum number of milliseconds that a client will wait for a connection
  max-lifetime: 20000 # maximum lifetime in milliseconds of a connection in the pool after it is closed.

The proper solution in this case, is to use a Shared VPC to have a private connection to your database where you will rely on your database driver to make those connections.

Upvotes: 2

Related Questions