Connection closed errors with managed Postgresql on DigitalOcean

aivanov

Hi!
We have the same problem FA work with AWS Proxy PosgreSQL manage DB. The last case was with the version FA 1.29.2
Please advise how to debug this problem from the FA side.
Sample exception:

maciej.wisniowski

@joshua thanks for pointing to these issues. I'm not sure if this is related but who knows. According to what aivanow said it seems this might be issue with all managed databases.

I've tried tuning some values, changed maxlifetime for HikariCP to 30000, changed minimum pool size to 2 connections, but the errors are still appearing.

My FA is 1.27.2

joshua

@maciej-wisniowski said in Connection closed errors with managed Postgresql on DigitalOcean:

it seems this might be issue with all managed databases.

Essentially the managed database must communicate well with Hikari (used by FusionAuth). If the managed DB closes connections without informing Hikari, then errors will result.

Thanks,
Josh

aivanov

@joshua @maciej-wisniowski
Hi! We continue to have these errors ( Error querying database. Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 2000ms.)
Please, can you recommend something for debugging this?

joshua

@aivanov

My advice would be further database tuning and configuration management for any managed database that is not performing as expected. You might want to try and shorten the max life even more on the connection pools in FusionAuth (HikariCP) (Think 5 seconds for a connection pool to be active).

For instance, you can review the Hikari Connection Pool Timeouts (FusionAuth side) and set them to a shorter duration than the "timeouts" on DigitalOcean's managed database (in other words, if Hikiri keeps DB connections open for 10 mins and DigitalOcean's managed DB kills DB connections after 8mins, then you will want to adjust Hikari connections to 7mins. You will incur a performance cost, however (restarting and killing connection pools is costly from a performance perspective).

Related documentation is below.

https://fusionauth.io/docs/v1/tech/reference/configuration/

database.connection-timeout [Integer] Defaults to 2,000
The number of milliseconds that FusionAuth will attempt to connect to the database before failing.

database.idle-timeout [Integer] Defaults to 120,000
The number of milliseconds that FusionAuth will leave an idle database connection in its connection pool before recreating it.

Also, as referenced above -

made sure we have enough connections available to the managed database (only about 20 from 47 are used) but the issue still appears.

My understanding is that more connections does not guarantee a better outcome.

If anything else occurs to me, I will post back. I hope this helps!

Thanks,
Josh

aivanov

@joshua Hello!
Thanks for advice!
We use aws rds service, not digital ocean.
Our fusionauth insnacess works with the rds base through a rds proxy. Rds proxy has a config parametr: Idle client connection timeout and it 30 min now.
I understand correctly that we need to set it less than the value DATABASE_MAX_LIFETIME (now is 240 sec)?

joshua

@aivanov

An RDS proxy adds a layer of complexity that I do not have much experience with personally, but I understand the purpose of why you would add it to a DB service.

I would recommend that you review the HikariCP documentation as it outlines the different configuration options available and their impact. Some of the configuration options within HikariCP are interconnected (ie - changing one impacts others). The configuration options here roughly translate to the configuration options that are presented to you in the fusionauth.properties file.

https://github.com/brettwooldridge/HikariCP#gear-configuration-knobs-baby

What are you are suggesting seems reasonable ( at quick glance ), but as the architecture for DB's/applications can be complex, there may be other points to consider. You are welcome to experiment. The most important paradigm to keep in mind is that if HikariCP is managing your DB connection pool, but you have a layer (proxy, DB, or otherwise) that is closing connections and not telling HikariCP, you may run into technical issues or performance impacts.

Thanks,
Josh

aivanov

Thanks @joshua !
Could you tell how to monitor the HikariCP connection pool? Fusionauth launched in docker.

joshua

@aivanov To my understanding, this configuration is not directly exposed via API.

You can however listen in on the status and Prometheus endpoints of FusionAuth.

https://fusionauth.io/docs/v1/tech/installation-guide/monitor/

Thanks,
Josh
FusionAuth

aivanov

Thanks @joshua !