IDLE connections in user store are not released in PG.

[lopesrodriguesn]

Description: We're actually use a postgresql database to store the tenant User Stores and we're facing an issue with increasing number of IDLE connections. the number keeps increasing until a process releases them every 10 hours (could be the "default time period for a SSO session" as it is the only parameter set to 10 hours by default.

I tried to set a bunch of parameters on both /usr/lib/wso2/wso2is-5.10.0/repository/conf/deployment-tpl.toml and /usr/lib/wso2/wso2is-5.10.0/repository/tenants/2/userstores/validation.xml and nothing seems to limit them.

All idle connections are the "SELECT 1" used to test de connectivity to DB. parameters in tenant xml:

<?xml version="1.0" encoding="UTF-8"?>

jdbc:postgresql://10.99.7.40:5432/doxtreem_wso2_validation

  <Property name="userName">XXXXXXXXXXXX</Property>
  <Property name="password">XXXXXXXXXXXXXXX</Property>
  <Property name="driverName">org.postgresql.Driver</Property>
  <Property name="Disabled">false</Property>
  <Property name="ReadOnly">false</Property>
  <Property name="timeBetweenEvictionRuns">5000</Property>
  <Property name="maxActive">20</Property>
  <Property name="maxIdle">10</Property> 
  <Property name="minIdle">5</Property>
  <Property name="maxWait">10000</Property>
  <Property name="testWhileIdle">true</Property>
  <Property name="testOnReturn">true</Property>
  <Property name="testOnBorrow">true</Property>
  <Property name="removeAbandoned">true</Property>
  <Property name="removeAbandonedTimeout">10</Property>
  <Property name="ReadGroups">true</Property>
  <Property name="WriteGroups">true</Property>

...

/usr/lib/wso2/wso2is-5.10.0/repository/conf/deployment-tpl.toml:

[database.identity_db] type = "postgre" hostname = "${WSO2_DB_IP}" name="${WSO2_DB_NAME}" username="${WSO2_DB_USERNAME}" password="${POSTGRES_PASSWORD}" port=${WSO2_DB_PORT} driver="org.postgresql.Driver" maxActive=20 maxWait=${WSO2_DB_MAX_WAIT} maxIdle=10 timeBetweenEvictionRunsMillis=3000 minIdle=7 testWhileIdle=true testOnReturn=true testOnBorrow=true validationQueryTimeout=5 removeAbandonedTimeout=true

I don't what else I can do. As We want to install several stores on the same DB, we have to find a way not to set PG max_connections to 1000 :(

Affected Product Version: IS 5.10.0 (docker) OS, DB, other environment details and versions:
debian 10 PG 12 Steps to reproduce: use PG as userstore.

Related Issues:

[ruwanta]

Hi @bouksy You could get better help from the slack channel for help requests like this.

https://bit.ly/wso2isslack

https://join.slack.com/t/wso2is/shared_invite/enQtNzk0MTI1OTg5NjM1LTllODZiMTYzMmY0YzljYjdhZGExZWVkZDUxOWVjZDJkZGIzNTE1NDllYWFhM2MyOGFjMDlkYzJjODJhOWQ4YjE

[tinto09]

Dear, I have the same problem. They found some solution or workaround. Thank you