DB_MAXSHAREDPOOLSIZE vs DB_MAXPOOLSIZE
- 1 Overview
- 2 Summary
- 2.1 Results Summary:
- 3 Recommendations
- 4 Test Runs
- 5 Results
- 6 Appendix
- 6.1 Infrastructure
Overview
In scope of https://folio-org.atlassian.net/browse/PERF-1133 the PTF performed A|B tests which compared the performance of multiple workflows using DB_MAXPOOLSIZE and DB_MAXSHAREDPOOLSIZE parameters that are applied in the services' task definitions. Using DB_MAXSHAREDPOOLSIZE instead of DB_MAXPOOLSIZE allows modules to use the same DB connections in a module DB connections pool across all tenants. This decreases the number of connections made to the DB and reduces DB memory usage while keeping the same performance on key workflows.
All tests performed on SEGCON (Galileo-like performance testing environment having 28 tenants).
Summary
DB_MAXPOOLSIZE parameter was changed to DB_MAXSHAREDPOOLSIZE in task definitions of mod-inventory-storage, mod-circulation-storage, mod-circulation-item, mod-pubsub. The values for each service are the same as they were with DB_MAXPOOLSIZE.
Service | Pool Size |
|---|---|
mod-inventory-storage | 20 |
mod-circulation-storage | 20 |
mod-circulation-item | 50 |
mod-pubsub | 20 |
Results Summary:
CheckIn-CheckOut tests (520 VUsers 1 hr): showed much better performance on CheckIn (25% improvement) using shared pool, CheckOut -2% degradation (100ms). Considering that results are the same if not better for CheckIn-Checkout workflow under abnormal load.
Database connections usage decreased from 2.6K to 1.9-2K (-600-700 connections ) and freeable memory increased by 4-5GB.
Tests with Master Script: showed variability of results. Some workflows are better, some are a bit worse. Degradation where it happens didn’t exceeds 7.5%, improvements didn’t exceed 6%. Taking into account nature of the master script (results may vary even without infrastructure changes) it may be considered that the workflows' performances are about the same as with and without shared DB connections.
Database connections usage decreased from 3.2K to 2.7K (500 connections saved during master script) and freeable memory grew by 2.5-3 GB.
There was additional tests performed but not included in report:
Master script with applying shared pool on all services showed tremendous reduce of connections usage on DB, but very unstable performance. (various workflows was better or the same, but some was significantly worse) Indicating that applying shared pool on all services at once is not the best strategy.
Next steps after review will be to incrementally apply shared pool on next set of services and try different combination of pool values to get the same performance while reducing connection rate and save memory.
Recommendations
Adopt DB_MAXSHAREDPOOLSIZE selectively (not on all modules but on mod-inventory-storage, mod-circulation-storage, mod-circulation-item, mod-pubsub) on one or few multiple tenants testing environments.
Keep monitoring of response times and clients feedbacks of performance of related to circulation workflows.
Keep monitoring DB freeable memory and connections rate. Average connections rate should decrease as tenants will reuse the connections.
Test Runs
In this experiment two type of tests were performed. CICO with 20 vUsers per tenant (560 vUsers total) with and without applied shared pool and Master script run with and without shared pool.
To limit variability of results only few related to CICO modules were chosen to apply shared connection pool. (mod-inventory-storage, mod-circulation-storage, mod-circulation-item, mod-pubsub)
Test # | VUsers | Duration (min) | Comment |
|---|---|---|---|
1 | 560 | 60 | CICO test 20 users per tenant with default modules configurations |
2 | 560 | 60 | CICO test 20 users per tenant with applying DB shared pools on CICO related modules* |
3 | 320 | 30 | Master script with default modules configurations |
4 | 320 | 30 | Master script with applying DB shared pools on CICO related modules* |
mod-inventory-storage, mod-circulation-storage, mod-circulation-item, mod-pubsub.
Results
CheckIn-CheckOut results
Analysis:
✅ CI improved significantly: ~25% faster with shared pools.
❌ CO slightly worsened: ~2% slower with shared pools.
| RT with Default configurations (ms) | RT with applied shared pools (ms) |
|---|---|---|
CI | 4855 | 3647 |
CO | 5622 | 5728 |
RT - “response times“
During tests connections made to database decreased from 2.6K to 1.9-2K showing that connections are actually reused across tenants.
Note: with reused connections DB memory usage as well decreased and in comparison with default configuration saving in memory usage is ±4-5 GB.
Mater script results
Most of results are about the same having less than 8% difference in average response times. Assuming that results are more or less the same as Galileo Master script contains lots of probabilities and results may vary even without of changes of infrastructure.
Metric | Default | Shared connections | Δ (Default − Master) | Δ % vs Master |
|---|---|---|---|---|
CI | 1120 | 1113 | -7 | -0.6% |
CO | 2008 | 2119 | +111 | +5.5% |
DE custom | 305,627 | 300,480 | -5,147 | -1.7% |
DE default | 304,241 | 298,868 | -5,373 | -1.8% |
View acc | 56,261 | 56,298 | +37 | +0.1% |
Create order | 1,758 | 1,885 | +127 | +7.2% |
Add order line | 3,828 | 3,977 | +149 | +3.9% |
Approve order | 1,211 | 1,289 | +78 | +6.4% |
Receive order line | 2,745 | 2,926 | +181 | +6.6% |
Serials receiving | 2,733 | 2,915 | +182 | +6.7% |
Undeceive piece | 1,495 | 1,552 | +57 | +3.8% |
Open order | 486 | 476 | -10 | -2.1% |
Loan renew | 16,388 | 15,431 | -957 | -5.8% |
With applied shared connections we can see that connections rate is 2.7K while without it it’s 3.2K. (500 connections saved when using shared connections.)
Appendix
Infrastructure
PTF -environment segcon |
|---|
|