Overview

• This document contains the results of testing  Data Import for MARC Bibliographic records with an create and update jobs on the Quesnelia [ECS] release on qcon environment. 

PERF-859 - Getting issue details... STATUS  

Summary

• Data Import tests finished successfully on qcon environment using the PTF - Create 2 profile and file with 10k, 25k and 50k records.
• Data Import test for PTF - Updates Success - 2 profile with 25k records file on 3 tenants concurrently finished with errors for two records. 
• Comparing with previous testing results Poppy and Quesnelia releases
  • Data Import processed DI MARC Bib Create jobs including test on 2 and 3 tenants concurrently without errors for Quesnelia releases.
  • Data Import processed DI MARC Bib Update job with 25k file for 3 tenants concurrently with two errors.
  • Data Import durations for Create job has performance improvement around 50% for Quesnelia releases.
  • Data Import durations for Update job stayed in the same time range in Average for Quesnelia releases.
• When comparing the durations of the 'main' job on the first tenant and observing how the second and third jobs affect and slow down the main job, it becomes evident that processing times are twice as slow for three tenants compared to two tenants when handling a 10k file.
• Additionally, for a DI MARC Bib Update job with 25k file , the duration increases by only 6%.

Test Runs and Results

This table contains durations for Data Import. 

This table contains durations for Data Import by each tests. 

Comparison

This table compares the DI durations of the 'main' job on the first tenant and how the second and third jobs affect and slow down the main job during Data Import.

This table contains сompare durations for second and third jobs compare to the main job for Data Import. 

This table contains durations comparison between Poppy and Quesnelia releases.

Service CPU Utilization

Here we can see that mod-data-import used 105% CPU in spike and mod-inventory used 95% CPU in spike.

Service Memory Utilization

Here we can see that all modules show a stable trend.

DB CPU Utilization

DB CPU was 96%.

DB Connections

Max number of DB connections was 1650.

DB load

Top SQL-queries

  INSERT INTO cs00000int_mod_search.consortium_instance (tenant_id, instance_id, json, created_date, updated_date)
  VALUES ($1, $2, $3::json, $4, $5)
  ON CONFLICT (tenant_id, instance_id)
  DO UPDATE SET json = EXCLUDED.json, updated_date = EXCLUDED.updated_date

insert into "marc_records_lb" ("id", "content") values (cast($1 as uuid), cast($2 as jsonb)) on conflict ("id") do update set "content" = cast($3 as jsonb)

INSERT INTO cs00000int_0002_mod_source_record_manager.events_processed (handler_id, event_id) VALUES ($1, $2)

INSERT INTO cs00000int_0001_mod_source_record_manager.journal_records (id, job_execution_id, source_id, source_record_order, entity_type, entity_id, entity_hrid, action_type, action_status, error, action_date, title, instance_id, holdings_id, order_id, permanent_location_id, tenant_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)

insert into "marc_records_lb" ("id", "content") values (cast($1 as uuid), cast($2 as jsonb)) on conflict ("id") do update set "content" = cast($3 as jsonb)

Service CPU Utilization

Here we can see that nginx-okapi used 195% CPU in spike, mod-inventory used 150% CPU in spike and mod-data-import used 140% CPU in spike.

Service Memory Utilization

Here we can see that all modules show a stable trend.

DB CPU Utilization

DB CPU was 96%.

DB Connections

Max number of DB connections was 1650.

DB load

Top SQL-queries

WITH deleted_rows AS (     delete from marc_indexers mi     where exists(         select 1         from marc_records_tracking mrt         where mrt.is_dirty = true           
and mrt.marc_id = mi.marc_id           and mrt.version > mi.version     )     returning mi.marc_id), deleted_rows2 AS (     delete from marc_indexers mi     
where exists(         select 1         from records_lb         where records_lb.id = mi.marc_id           and records_lb.state = 'OLD'     )     returning mi.marc_id) 
INSERT INTO marc_indexers_deleted_ids SELECT DISTINCT marc_id FROM deleted_rows UNION SELECT marc_id FROM deleted_rows2

insert into "marc_records_lb" ("id", "content") values (cast($1 as uuid), cast($2 as jsonb)) on conflict ("id") do update set "content" = cast($3 as jsonb)

INSERT INTO cs00000int_mod_search.consortium_instance (tenant_id, instance_id, json, created_date, updated_date)
  VALUES ($1, $2, $3::json, $4, $5)
  ON CONFLICT (tenant_id, instance_id)
  DO UPDATE SET json = EXCLUDED.json, updated_date = EXCLUDED.updated_date

INSERT INTO cs00000int_0001_mod_source_record_manager.events_processed (handler_id, event_id) VALUES ($1, $2)

insert into "marc_records_lb" ("id", "content") values (cast($1 as uuid), cast($2 as jsonb)) on conflict ("id") do update set "content" = cast($3 as jsonb)

Errors

Data Import test for PTF - Updates Success - 2 profile with 25k records file on 3 tenants concurrently finished with errors for two records. 

Felid records:

Error Logs:

io.vertx.core.impl.NoStackTraceThrowable: Failed to update MARC record in SRS, instanceId: '830273a3-459b-4a81-945e-139719dd1b71', jobExecutionId: '4b55ca82-9fb1-4d42-b116-c3d3df75b312', status code: 504, 
Record: <html> 
<head><title>504 Gateway Time-out</title></head> 
<body> 
<center><h1>504 Gateway Time-out</h1></center> 
</body> 
</html>

io.vertx.core.impl.NoStackTraceThrowable: Failed to update MARC record in SRS, instanceId: '8d74d733-7c8e-40bf-974c-a2e19a01a389', jobExecutionId: '987db8ab-91e4-43ab-9c4f-8456da2466c5', status code: 422, 
Record: {"errors":[{"message":"must not be null","type":"1","code":"jakarta.validation.constraints.NotNull.message","parameters":[{"key":"matchedId","value":"null"}]}]}

Appendix

Infrastructure

PTF - environment Quesnelia (qcon)

• 11 m6i.2xlarge EC2 instances located in US East (N. Virginia)us-east-1 [Number of ECS instances, instance type, location region]

• 1 instance of db.r6.xlarge database instance: Writer instance

• Number of records in DB:
  •  cs00000int_0001
    • instances - 6828236
    • items - 7859770
    • holdings - 7328737
  • cs00000int_0002
    
    • nstances - 1163315
    • items - 1754121
    • holdings - 1332559
  • cs00000int_0003
    
    • nstances - 1135806
    • items - 1735291
    • holdings - 1309387

• OpenSearch

  • domain: fse

  • Data nodes Instance type: r6g.xlarge.search

  • Number of nodes: 9

  • Version: OpenSearch_2_7_R20240502

• MSK - tenat

  • 4 kafka.m5.2xlarge brokers in 2 zones

  • Apache Kafka version 2.8.0

  • EBS storage volume per broker 300 GiB

  • auto.create.topics.enable=true

  • log.retention.minutes=480

  • default.replication.factor=3

  • Kafka consolidated topics enabled

Methodology/Approach

DI tests scenario (DI MARC Bib Create\Update) were started from UI.

Test set №1:

• Test 1: Manually tested 10k records files DI MARC Bib Create started on College tenant(cs00000int_0001) and Professional tenant(cs00000int_0002) concurrently, step 30%.
• Test 2: Manually tested 10k records files DI MARC Bib Create started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.
• Test 3: Manually tested 25k records files DI MARC Bib Create started on College tenant(cs00000int_0001) and Professional tenant(cs00000int_0002) concurrently, step 30%.
• Test 4: Manually tested 25k records files DI MARC Bib Create started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.
• Test 5: Manually tested 150k records files DI MARC Bib Create started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.

Test set №2:

• Test 6: Manually tested 10k records files DI MARC Bib Update started on College tenant(cs00000int_0001) and Professional tenant(cs00000int_0002) concurrently, step 30%.
• Test 7: Manually tested 10k records files DI MARC Bib Update started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.
• Test 8: Manually tested 25k records files DI MARC Bib Update started on College tenant(cs00000int_0001) and Professional tenant(cs00000int_0002) concurrently, step 30%.
• Test 9: Manually tested 25k records files DI MARC Bib Update started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.
• Test 10: Manually tested 150k records files DI MARC Bib Update started on College tenant(cs00000int_0001), Professional tenant(cs00000int_0002) and School tenant(cs00000int_0003) concurrently, step 30%.

To get status and time range for import jobs the query used: 

select file_name,started_date,completed_date, completed_date - started_date as duration ,status
from cs00000int_0001_mod_source_record_manager.job_execution order by started_date desc limit 1000;

select file_name,started_date,completed_date, completed_date - started_date as duration ,status
from cs00000int_0002_mod_source_record_manager.job_execution order by started_date desc limit 1000;

select file_name,started_date,completed_date, completed_date - started_date as duration ,status
from cs00000int_0003_mod_source_record_manager.job_execution order by started_date desc limit 1000;