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

Test #	Test Conditions	Duration	Load generator size (recommended)	Load generator Memory(GiB) (recommended)	Notes (Optional)
1.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	baseline (no shared pool)
2.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =500
3.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =700
4.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =1000
5.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =2000
6.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =2500
7.	5 users CICO on 61 tenants	30 mins	t3.xlarge	9	mod-inventory-storage shared pool =2500 mod-circulation-storage shared pool = 500

Results

#	test 5 users CICO on 61 tenants	primary\secondary		primary\secondary
#	test 5 users CICO on 61 tenants	CI(seconds)		CO(seconds)
1.	baseline (no shared pool)	1,191	0,928	1,87	1,337
2.	mod-inventory-storage shared pool =500	1,648	1,299	2,367	1,972
3.	mod-inventory-storage shared pool =700	1,538	1,278	2,435	1,938
4.	mod-inventory-storage shared pool =1000	1,5	1,271	2,49	1,932
5.	mod-inventory-storage shared pool =2000	1,59	1,290	2,442	1,970
6.	mod-inventory-storage shared pool =2500	1,578	1,272	2,385	1,941
7.	mod-inventory-storage shared pool =2500+ mod-circulation-storage shared pool = 500	1,962	1,746	3,107	2,560

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

Set ROLE

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:

tenant	instances	Holdings	Items
fs00001137	4012391	5308741	6591570
mob01	219789	199455	199372
mob02	209942	189865	189837
mob03	209781	188143	188073
mob04	199573	178064	177989
mob05	189503	167518	167440
mob06	169970	149895	149881
mob07	169942	149850	149839
mob08	159997	139991	139989
mob09	160002	139993	139992
mob010	159964	139962	139961
mob011	159960	139948	139946
mob012	160001	139993	139992
mob013	160001	139998	139991
mob014	159991	139985	139985
mob015	20001	20001	20001
mob016	20001	20001	20001
mob017	20001	20001	20001
mob018	20001	20001	20001
mob019	20001	20001	20001
mob020	20001	20001	20001
mob021	20001	20001	20001
mob022	20001	20001	20001
mob023	20001	20001	20001
mob024	20001	20001	20001
mob025	20001	20001	20001
mob026	20001	20001	20001
mob027	20001	20001	19999
mob028	20001	20000	20000
mob029	20001	20001	20001
mob030-mob061	10001	10001	10001

Browser not supported