Performance degradation with using DB_MAXSHAREDPOOLSIZE parameter on mod-inventory-storage
Description
CSP Request Details
CSP Rejection Details
Potential Workaround
is continued by
Checklist
hideTestRail: Results
Activity
Mikhail Fokanov October 20, 2023 at 9:12 AMEdited
@Sobha Duvvuri, @Roman_Fedynyshyn, @Denis
The explanation of the problem: 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).
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.
@Julian Ladisch, I see only one enhancement that can be implemented in RMB. If there are free connection in the pool, which was previously used for the desired tenant, 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 would be done in PostgresClient .
But it is extremely tricky, because the PgConnection is vertex class and it is virtually impossible to set some properties (e.g. modify the PgConnectOptions.properties) for it or identify it somehow (to have mapping between connection and last used schema in PostgreClient). Maybe it can be done by means of wrapper for the vertx connection.
Also it worth to mention, that it is not architecturally clear, because connections in the pool are supposed to be identical (e.g. immutable) by design. But, the way I see it, this is the only thing that could be done.
Details
Details
Assignee
Reporter
Priority
P2
Overview:
PTF team has discovered that there is performance degradation with using DB_MAXSHAREDPOOLSIZE parameter on mod-inventory-storage while testing CheckIn/CheckOut workflow on Mobius like cluster (61 tenants without consortia enabled).
With increasing parameter value (500,700,1000,1500,2000,2500) we can't see much improvements between each of a test and comparing to baseline test (without this parameter) it's always worse. Most of CPU time being consumed by SET ROLE,SET SCHEMA queries which looks like is happening with every query coming to DB.
Steps to Reproduce:
Run CICO without DB_MAXSHAREDPOOLSIZE parameter, gather data like response times of CICO, RDS CPU usage, connections, etc.
Run CICO with DB_MAXSHAREDPOOLSIZE and compare results.
Expected Results:
No performance degradation
Actual Results:
PTF team contacted several tests with various values on shared pool parameter for mod-inventory-storage (500,700,1000,1500,2000,2500). and even with 2500 connections shared pool was not able to achieve same performance on CICO. Moreover DB CPU usage increases up to 50% (while it's 20 on baseline test). connections rate don't change with increasing of parameter value
Additional Information:
report with additional information can be found here
https://folio-org.atlassian.net/wiki/display/FOLIJET/DB_MAXSHAREDPOOLSIZE+experiments
Interested parties:
@Sobha Duvvuri ,@Julian Ladisch , @Denis , @Mikhail Fokanov , @Taras Spashchenko , @Martin Tran , @Denis Anischenko