Overview

In scope of PERF-673. PTF did run experiments on a Mobius-like environment (with 61 tenants, without the consortia feature enabled) using the DB_MAXSHAREDPOOLSIZE parameter on mod-inventory-storage and mod-circulation-storage to check the behavior of the system and detecting any performance degradation or improvements.

PTF will analyse DB_MAXSHAREDPOOLSIZE performance based on CheckIn/CheckOut tests (61 tenant with 5 concurrent users each) with different value of parameter on mod-inventory-storage, and as addition on mod-circulation-storage. Changing of DB_MAXSHAREDPOOLSIZE parameter value will give visibility if we can achieve same performance of CICO workflow with changing values of parameter only on one module.

The overall goal was to check if applying of shared connection pools will reduce the connection usage mentioned in Multi tenants ECS report [in progress]#Results without performance degradation of CICO (in this particular set of experiments). 

NOTE: Shared pool feature is enabled by replacing DB_MAXPOOLSIZE parameter with DB_MAXSHAREDPOOLSIZE in task definition of specific module

NOTE: Shared pool feature should reduce connections usage per service/per tenant:

• How it's working now (on example of mod-inventory-storage): mod-inventory-storage has 2 running tasks (containers),  with defined DB_MAXPOOLSIZE = 20. Each of 61 tenants will open 20 connections on each of containers, so, 61 tenants x 20 connections x 2 containers = 2440 connections. This is the maximum number of connections that can be opened by 61 tenants by mod-inventory-storage service.
• How it should work with DB_MAXSHAREDPOOLSIZE: each module will have its own connection pool with the maximum number specified in value of DB_MAXSHAREDPOOLSIZE. All tenants talking to a module will use the connections from the module's pool. 

Summary

• Shared pool feature leads to performance degradation (with parameter value equals to 500,700,1000,2000,2500 and with 2500 on mod inventory-storage+ 500 on mod-circulation-storage) even with DB_MAXSHAREDPOOLSIZE equals 2500 (which, based on our calculations, should be the max number of connections, in theory, defined by the DB_MAXPOOLSIZE parameter when using individual pools per module per tenant);
• Unexpectedly mod-users has increasing CPU usage from test to test;
• By changing DB_MAXSHAREDPOOLSIZE from 500 to 700, from 700 to 1000, from 1000 to 2000 and from 2000 to 2500 we can't see much differences in DB connections chart during tests.
• DB CPU utilisation grew to ~50% during all of a tests with a shared pool of mod-inventory-storage, while during baseline test (without shared pool) it was ~20%;
• DB CPU utilisation grew to ~70% during a test with shared pools of mod-inventory-storage and mod-circulations-storage;
• When adding one more module (mod-circulation-storage) shared pool CICO performance degrades even further;
• Most consuming DB CPU query during tests with shared poll is SET ROLE, SET SCHEMA. It looks like this queries is happening with addition to each query on DB. 
• See CheckIn/CheckOut response times comparison in dependency to shared pool parameter on chart below:

Note: CI/CO primary - is response times of CI/CO on a primary tenant that has 3M inventory records. CI/CO secondary - is average response times between all secondary tenants (which has less data that primary one). See exact data volumes in Appendix

• No memory leak found

Test Runs 

Results

Test #1-6 Shared pool on mod-inventory-storage (500,700,1000,2000,2500)

Screenshoots below contains all tests with only mod-inventory-storage shared pool enabled. In order baseline, 500 connections, 700,1000,2000,2500

Memory Utilization

CPU Utilization 

Note: no major differences between CPU usages on modules except mod-users that has increasing CPU usage. 

RDS metrics 

Note: Baseline test has 20% DB CPU usage, tests with enabled shared pool on mod-inventory-storage (with various values) has ±45-50% DB CPU usage.

Note: even with increasing of CPU usage connections used doesn't seems to grow with increasing shared pool parameter.

Note: there is a big difference between baseline test and tests with enabled shared pool. we can observe increased CPU usage

Note: SET ROLE,SET SCHEMA looks like is consuming most of CPU time. 

Test #7 Shared pool on mod-inventory-storage (2500)+ mod-circulation-storage (500)

Memory Utilization

CPU Utilization 

RDS metrics 

Note: DB CPU usage increased in comparison to previous tests and now it's ±70% with only having shared pool on mod-inventory-storage and mod-circulation-storage

Appendix

See infrastructure and code base details in Multi tenants ECS report [in progress]#Appendix

Data Volume on tenants: