Performance Testing Results for Async Migration of Inventory Item Records
Overview
Performance testing was conducted for the async migration that populates the “order” field for existing Inventory item records as part of the API-based reordering functionality. The goal was to verify that the migration can handle a LOC-sized dataset in a multi-tenant environment and complete within the expected time threshold (under 8 hours), ensuring system readiness for the Trillium release.
This document presents the results of testing conducted in a prepared environment similar to the production environment. The testing process executes Async migration job with different dataset. Testing activities were conducted in a asptc environment.
The goals of testing:
Determine the time it takes to complete a job
Ticket:
Test runs
Test_1 - job executuon on tenant with 7387 items, xlarge DB, 2 mod-inventory-storage tasks
Test_2 - job executuon on tenant with 433092 items xlarge DB, 2 mod-inventory-storage tasks
Test_3 - job executuon on tenant with 7227721 items 4xlarge DB, 2 mod-inventory-storage tasks
Test_4 -job executuon on tenant with 7227721 items xlarge DB, 4 mod-inventory-storage tasks
Test_5 -job executuon on tenant with 7227721 items 2xlarge DB, 4 mod-inventory-storage tasks
Summary
Performance testing of the async migration for populating the “order” field showed stable and predictable scaling behavior with increasing data volumes. Small and medium datasets (Test_1 and Test_2) completed quickly within seconds and minutes, while large-scale processing (Test_3) exceeded the expected 8-hour threshold, taking over 10 hours with the baseline configuration.
An attempt to optimize execution time by increasing parallelism (Test_4) — scaling the number of tasks for mod-inventory-storage from 2 to 4, which also increased the number of Kafka consumers — resulted in database overload. This caused significant contention and led to timeout errors, ultimately failing the test.
To address this, the database capacity was increased in Test_5 (from xlarge to 2xlarge) while keeping higher parallelism. This resolved the performance bottleneck, allowing the migration to complete successfully and reducing execution time by approximately 50% (from ~10 hours to ~5 hours).
Overall, results demonstrate that the migration performance is highly dependent on database capacity when increasing concurrency. With proper resource scaling, the solution meets the performance requirement of completing within 8 hours for LOC-sized datasets.
Results
Duration times table for Test_1, Test_2, Test_3, Test_4 and Test_5
Test # | Number of Items | DB size | Number of mod-inventory-storage tasks | Status | Duration |
Test_1 | 7387 | xlarge | 2 | Complited | 38 sec |
Test_2 | 433092 | xlarge | 2 | Complited | 36 min |
Test_3 | 7227721 | 4xlarge | 2 | Complited | 10 hours 3 min |
Test_4 | 7227721 | xlarge | 4 | Failed |
|
Test_5 | 7227721 | 2xlarge | 4 | Complited | 5 hours 12 min |
Resource Utilization for Test_4(showing database overload and job failed)
DB CPU utilization was near saturation for most of the test, peaking at ~97%.
DB Connections
DB max number of connections reached 1,600.
DB Load
Resource Utilization for Test 5 (successful test with 5 hours duration)
DB CPU in the average was 41%, the max reached 83%.
DB Connections
DB max number of connections reached 1,800.
DB Load
Top SQL-queries
# | TOP 5 SQL statements |
|---|---|
1 | UPDATE cs00000int_0001_mod_inventory_storage.item SET jsonb = $1 WHERE id = ($1::jsonb->>?)::uuid |
2 | SELECT jsonb FROM cs00000int_0001_mod_inventory_storage.item WHERE holdingsrecordid = $1 ORDER BY lower(jsonb ->> ?::text) FOR UPDAT |
3 | SELECT jsonb FROM cs00000int_mod_finance_storage.transaction_totals_view WHERE (jsonb->>?) = $1 AND jsonb->>? = ANY ($2) AND (jsonb->>?) = ANY ($3) |
4 | autovacuum: VACUUM cs00000int_0001_mod_inventory_storage.item |
5 | COMMIT |
Appendix
Infrastructure
PTF -environment - asptc
9 r7g.2xlarge EC2 instances located in US East (N. Virginia)us-east-1Database instance - asptc-restored-cluster
db.r7g.xlarge for Test_1
db.r7g.xlarge for Test_2
db.r7g.4xlarge for Test_3
db.r7g.xlarge for Test_4
db.r7g.2xlarge for Test_5Option groups - default:aurora-postgresql-16;
DB instance parameter group - default.aurora-postgresql16;DB cluster parameter group - fse-aurora-postgresql16
OpenSearch - ptf-loc
Version Info- OpenSearch 2.19Service software version Info - OpenSearch_2_19_R20260218 (latest)
Data nodes
Availability Zone(s)- 2-AZ without standby
Instance type - r7g.xlarge.search
Number of data nodes - 4
Storage type - EBS
EBS volume type - General Purpose (SSD) - gp3
EBS volume size (GiB) - 500
Provisioned IOPS - 4500 IOPS
Provisioned Throughput (MiB/s) - 250 MiB/sMaster nodes
Instance type - m7g.large.search
Number of master nodes - 3MSK - ptf-loc-1
Total number of brokers - 6
Apache Kafka version 3.9.x
Metadata mode - KRaft
Cluster configuration name - fse-kafka-config
Revision - 31
Brokers Info:
Broker size - kafka.m7g.large
Brokers per zone - 3
Total number of brokers - 6
Storage Info:EBS storage volume per broker - 300 GiB
Cluster storage mode - EBS storage
Modules memory and CPU parameters: