Fusionauth with Pgbouncer

johnmiller

Hi,

I am using FA 1.41.3 behind Pgbouncer with the following configuration:

max_clients: 3000
max_db_connections: 10
pool_mode: session

FA is working fine but I see the following errors in the fusionauth-app.log:

### Error querying database.  Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 2008ms.
### The error may exist in io/fusionauth/api/domain/InstanceMapper.java (best guess)
### The error may involve io.fusionauth.api.domain.InstanceMapper.retrieve
### The error occurred while executing a query
### Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 2008ms.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at jdk.internal.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:357)
	at jdk.proxy2/jdk.proxy2.$Proxy51.selectOne(Unknown Source)
	at org.apache.ibatis.session.SqlSessionManager.selectOne(SqlSessionManager.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at jdk.proxy2/jdk.proxy2.$Proxy58.retrieve(Unknown Source)
	at io.fusionauth.api.license.FusionAuthLicenseMetaDataManager.localLookup(FusionAuthLicenseMetaDataManager.java:50)
	at com.inversoft.license.v2.BaseConfigurationLicenseMetaDataManager.lookup(BaseConfigurationLicenseMetaDataManager.java:30)
	at com.inversoft.license.v2.local.DefaultLocalLicenseSystem.load(DefaultLocalLicenseSystem.java:35)
	at com.inversoft.license.v2.DefaultLicenseProvider$LocalReloader.run(DefaultLicenseProvider.java:131)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
	at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 2008ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
	at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:139)
	at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)
	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:337)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	... 23 common frames omitted
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:907)
	at org.postgresql.jdbc.PgConnection.setNetworkTimeout(PgConnection.java:1649)
	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:561)
	at com.zaxxer.hikari.pool.PoolBase.isConnectionDead(PoolBase.java:168)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:170)
	... 35 common frames omitted
WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@6d045a91 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@4e4d434b (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
ERROR io.fusionauth.api.service.system.DefaultAsyncTaskManager - An exception occurred while managing an async task.
org.apache.ibatis.exceptions.PersistenceException:

My suspicion is that the error is related to the Pgbouncer. Can you please suggest what I need to change to fix this error?

BR,
John

dan

Hiya @johnmiller ,

FusionAuth uses Hikari to handle connection pooling. I'm not familiar with how they interact, but this SO answer seemed useful: https://stackoverflow.com/questions/56581582/hikaricp-apache-dbcp2-and-pgbouncer

If you are using PgBouncer, then you definitely don't need HikariCP around.

But unfortunately, FusionAuth can't be configured not to use HikariCP.

Can you explain why you need pgbouncer and how that's helping you?