Avoid SET ROLE/SET SCHEMA in Shared Pool
Description
Environment
None
Potential Workaround
None
continues
Checklist
hideTestRail: Results
Activity
Show:
Done
Details
Details
Assignee
Steve Ellis
Steve EllisReporter
Mikhail Fokanov
Mikhail FokanovPriority
Development Team
Core: Platform
Fix versions
Release
Ramsons (R2 2024)
TestRail: Cases
Open TestRail: Cases
TestRail: Runs
Open TestRail: Runs
Created November 21, 2023 at 1:54 PM
Updated October 13, 2024 at 6:14 PM
Resolved May 15, 2024 at 3:48 PM
TestRail: Cases
TestRail: Runs
Purpose/Overview:
In https://folio-org.atlassian.net/browse/MODINVSTOR-1124 the performance degradation is observed, when the shared connection pool is used. It is expected for the case when the overall number of connections for shared pool is less, but it is less also when the overall number of connections is same. The root cause is that:
1. If connections are separate for each tenant, then you do not need to constantly switch to the desired tenant after receiving a connection. But in such case , there will be too many of them (number of modules * number of tenants).
2. If a shared connection pool is created, then it cannot be as productive as a non-shared one, since in this case the connections have to constantly switch between tenants (each time before the query execution). And hence we see the performance degradation in the test results.
Approach:
If there are free connection in the pool, which was previously used for the desired tenant (for which the connection is being requested), this connection should be taken instead of random one and in such case the "SET ROLE '" + schemaName + "'; SET SCHEMA '" + schemaName + "'" should not be executed for it.
This changes should be implemented in PostgresClient.
It is not simple to do because the PgConnection is vertex class and it is hard to set some "marker" (e.g. modify the PgConnectOptions.properties) for it or identify it somehow (to have mapping between connection and last used schema in PostgreClient).
The best approach is to identify it by the Java Object (e.g. put the PgConnection as the key of the HashMap).
The Vert.x pool closes an idle connection after some time and deletes it from the pool, we don't want to keep references in the HashMap to closed and deleted connection, otherwise we may get out of memory.