[midPoint] DatabaseTable Resource Connector, too many opened connections

[AXP]

Hello František,

thanks for reply. In the meanwhile I tried with success to set 
idle_session_timeout parameter on Postgres side:
alter system set idle_session_timeout=5min;

Now the number of opened connections in idle are constantly near to 30,
but in the logs I found the following warning, thrown when a scheduled 
task try to reuse an old connection in idle (closed by postgres):

[midPointScheduler_Worker-6] WARN (com.zaxxer.hikari.pool.PoolBase): 
HikariPool-1 - Failed to validate connection 
org.postgresql.jdbc.PgConnection at 586485d6 (This connection has been 
closed.). Possibly consider using a shorter maxLifetime value.

I try to setup your suggested fields, found in "Connector Pool" tab of 
"Connector Configuration" of each resource. I Hope this let to avoid 
also the above warning.

About the best solution proposed "one resource for all tables":
I could try to configure a resource like this but, where I specify all 
the tables? I found only one possible table.
I thought a possible solution to merge all tables in a database view 
but: how keep trace that a single account is coming from a specific 
system? (now implicitly maintained by resource name)

Regards,
AXP



On 26/03/2024 09:57, František Dvořák wrote:
> Hello,
>
> you could try to tune connection pool parameters in the resources.
>
> For example using minIdle=0 (default is 1), lower maxIdle (default is
> 10), and shorter minEvictableIdleTimeMillis (default is 120 * 1000).
>
> Explanation of the parameters are here: [1]
>
> But the best could be probably to use one resource for all tables - if
> it is in the same database and there are the same credentials for
> access to each table.
>
> Cheers,
>    František
>
> [1]https://github.com/Evolveum/ConnId/blob/master/java/connector-framework/src/main/java/org/identityconnectors/common/pooling/ObjectPoolConfiguration.java
>
> On Mon, 2024-03-25 at 19:26 +0100, AXP via midPoint wrote:
>>   Hello,
>>   
>>   In my project I have over than 500 Resources integrated by using
>> DatabaseTable Resource Connector, all have its own database table in
>> a only one Postgres database schema instance (not the same used by
>> midpoint application).
>>   I scheduled for each a reconciliation task once per day, delayed in
>> a way that no more than two tasks runs in parallel.
>>   
>>   I see that after some hours of uptime, the number of opened
>> connections to postgres increase up to database limit, that is 100,
>> and will no longer released, causing the following error at next task
>> execution:
>>   
>> org.identityconnectors.framework.common.exceptions.ConnectorException
>> : org.postgresql.util.PSQLException: FATAL: remaining connection
>> slots are reserved for roles with the SUPERUSER attribute
>>   
>>   How can I avoid this and force to release connection after task
>> completion?
>>   
>>   Notice that each resource have its own database configuration, that
>> opens a new connection at reconciliation start, I suppose that by
>> sharing a connection pool among all resources may resolve, but I
>> don't find any documentation other than:
>>   
>>   "It is not yet clear how to set up data sources for embedded
>> Tomcat."
>>   
>> https://docs.evolveum.com/midpoint/devel/guides/environment/embedded-
>> tomcat/
>>   
>>    I'm using midpoint embedded in a docker container, that make use of
>> tomcat embedded
>>   
>>   Thanks in advance,
>>   AXP
>>    
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com
>> https://lists.evolveum.com/mailman/listinfo/midpoint
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20240326/65a32997/attachment.htm>