Overview

• This document contains comparing results of testing  Data Import for MARC Bibliographic records with create and update jobs on the Quesnelia [non-ECS] release on qcp1 environment with two different versions of  mod-source-record-storage 5.8.0and5.9.0-SNAPSHOT.387 (moved json parsing function from DB to module). 

PERF-924 - Getting issue details... STATUS  

Summary

• Data Import tests finished successfully on qcp1 environment using the PTF - Create 2 and PTF - Updates Success - 6 profiles with 10k, 100k and 200k file records.
• DI duration growth correlates to the number of records imported.
• Comparing between  mod-source-record-storage 5.8.0 and mod-source-record-storage 5.9.0-SNAPSHOT.387
  
  • Data Import durations have performance degradation of around 35% for all Data-imports jobs.
  • Server and Database metrics at the same level for both versions of mod-source-record-storage module.
• No memory leaks are observed.

Test Runs and Results

This table contains durations for Data Import. 

Comparison

This table contains durations comparison between  mod-source-record-storage 5.8.0 and mod-source-record-storage 5.9.0-SNAPSHOT.387.

Service CPU Utilization

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

Service Memory Utilization

Here we can see that all modules show a stable trend except mod-source-record-manager.

DB CPU Utilization

DB CPU was 90%.

CPU (User) usage by broker

Disk usage by broker

DB Connections

Max number of DB connections was 918.

DB load

Top SQL-queries

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 fs09000000_mod_source_record_manager.events_processed (handler_id, event_id) VALUES ($1, $2)

INSERT INTO fs09000000_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 fs09000000_mod_inventory_storage.instance (id, jsonb) VALUES ($1, $2) RETURNING jsonb

INSERT INTO fs09000000_mod_inventory_storage.item (id, jsonb) VALUES ($1, $2) RETURNING jsonb

Service CPU Utilization

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

Service Memory Utilization

Here we can see that all modules show a stable trend except mod-source-record-manager.

CPU (User) usage by broker

Disk usage by broker

DB CPU Utilization

DB CPU was 95%.

DB Connections

Max number of DB connections was 942.

DB load

Top SQL-queries

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

INSERT INTO fs09000000_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)

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 fs09000000_mod_inventory_storage.item (id, jsonb) VALUES ($1, $2) RETURNING jsonb

INSERT INTO fs09000000_mod_inventory_storage.instance (id, jsonb) VALUES ($1, $2) RETURNING jsonb

Appendix

Infrastructure

PTF -environment qcp1

• 10 m6i.2xlarge EC2 instances located in US East (N. Virginia)us-east-1

• 1 database  instance, writer

  Name	Memory GIB	vCPUs	max_connections
  db.r6g.xlarge	32 GiB	4 vCPUs	2731

• Number of records in DB:
  •  fs09000000
    • instances - 25901331
    • items - 27074913
    • holdings - 25871735
• Open Search ptf-test
  • Data nodes
    • Instance type - r6g.2xlarge.search
    • Number of nodes - 4
    • Version: OpenSearch_2_7_R20240502
  • Dedicated master nodes
    • Instance type - r6g.large.search
    • Number of nodes - 3
• MSK tenant
  
  • 4 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

Methodology/Approach

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

Test set №1:

• Test 1: Manually tested 25k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.
• Test 2: Manually tested 100k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.
• Test 3: Manually tested 200k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.
• Test 4: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.
• Test 5: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.
• Test 6: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

Test set №2:

• Test 7: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.
• Test 8: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.
• Test 9: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.
• Test 10: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.
• Test 11: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.
• Test 12: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

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

select file_name, job_Profile_name, started_date,completed_date, completed_date - started_date as duration ,status
from fs09000000_mod_source_record_manager.job_execution order by started_date desc limit 2000;