/
PTF - Export deleted MARC authority records (Quesnelia) [ECS]

PTF - Export deleted MARC authority records (Quesnelia) [ECS]

Jun 14, 2024

Overview

  • This document contains the results of testing of export deleted MARC authority records via API on the Quesnelia [ECS] release on qcon environment. 

https://folio-org.atlassian.net/browse/PERF-897 

Summary

  • Export deleted MARC authority records tests finished successfully on qcon environment using  100K, 300K and 646K records.

  • Comparing Test Duration and Response time between tests with 100K, 300K and 646K records 

    • Test Duration grew up according to grew records count.

    • Response time for 100k and 300k records almost the same, but it grew up almost in two times compared 100k and 646k records.

  • During testing, we noticed that mod-entities-links module used 53% CPU in spikes for 300k, but for 646k records used 39% CPU.

  • No memory leaks are observed.

  • After the first part of the tests, the database was cleaned up by removing deleted MARC authority records. This cleanup negatively affected the duration and response time for the 100k records in the repeated test.

Test Runs 

Test №

Scenario

Test Conditions

Results

Test №

Scenario

Test Conditions

Results

1

Export deleted MARC authority records via API

100k deleted records

Completed

2

300k deleted records

Completed

3

646k deleted records

Completed

Test Results

This table contains durations for export deleted MARC authority records and response time (Average) for GET authority-storage/authorities endpoint. 

Profile

Test Conditions

Test

Duration  sec

Response time Average sec


GET_authority-storage/authorities

Export deleted MARC authority records via API

100k

13.317

0.261

300k

29.109

0.288

646k

90.045

0.514

Export deleted MARC authority records via API

(repeated tests)

100k

25.892

0.512

300k

36.173

0.358

 

Resource utilization for Test №1

CPU RAM mod-entities-links-b 17% mod-inventory-b 55% mod-inventory-b 10% mod-data-import-b 47% mod-quick-marc-b 6.60% mod-circulation-item-b 44% mod-pubsub-b 5.50% mod-source-record-storage-b 41% mod-di-converter-storage-b 2.10% mod-source-record-manager-b 40% mod-feesfines-b 2.00% mod-entities-links-b 37% mod-configuration-b 2.00% okapi-b 32% mod-source-record-manager-b 1.80% mod-feesfines-b 31% pub-okapi 1.60% mod-quick-marc-b 30% mod-source-record-storage-b 1.30% mod-di-converter-storage-b 29% okapi-b 1.20% mod-patron-blocks-b 29% mod-data-import-b 1.00% mod-configuration-b 28% nginx-okapi 1.00% mod-users-bl-b 26% mod-remote-storage-b 0.80% mod-pubsub-b 24% mod-authtoken-b 0.60% mod-authtoken-b 19% mod-circulation-storage-b 0.50% mod-circulation-storage-b 18% mod-users-bl-b 0.50% mod-remote-storage-b 16% mod-circulation-b 0.30% mod-inventory-storage-b 13% mod-patron-blocks-b 0.20% nginx-okapi 4.00%

Service CPU Utilization

Here we can see that mod-entities-links used 18% CPU in spikes.

Service Memory Utilization

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

DB CPU Utilization

DB CPU was 12%.

DB Connections

Max number of DB connections was 1355.

DB load

Top SQL-queries

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?

2

select this_.fl_id as fl_id1_53_0_, this_.fl_family as fl_famil2_53_0_, this_.fl_name as fl_name3_53_0_, this_.fl_owner as fl_owner4_53_0_ from mod_serials_management__system.federation_lock this_
where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_

3

select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,
aa1_0.sft_headings,aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1
offset $2 rows fetch first $3 rows only

4

SELECT jsonb FROM cs00000int_mod_permissions.permissions

5

SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = ? AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number()
OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, nullif(a.attidentity, ?) as attidentity,nullif(a.attgenerated, ?) as attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid)
AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) J

Resource utilization for Test №2

CPU RAM mod-entities-links-b 52% mod-inventory-b 55% mod-inventory-b 10% mod-data-import-b 47% mod-quick-marc-b 6.90% mod-circulation-item-b 44% mod-pubsub-b 5.50% mod-source-record-storage-b 41% nginx-okapi 5.30% mod-source-record-manager-b 40% pub-okapi 4.80% mod-entities-links-b 38% mod-configuration-b 3.10% okapi-b 32% okapi-b 2.10% mod-feesfines-b 31% mod-feesfines-b 2.10% mod-quick-marc-b 30% mod-di-converter-storage-b 2.00% mod-di-converter-storage-b 29% mod-source-record-manager-b 1.70% mod-patron-blocks-b 29% mod-source-record-storage-b 1.30% mod-configuration-b 28% mod-data-import-b 1.10% mod-users-bl-b 26% mod-users-bl-b 1.00% mod-pubsub-b 24% mod-authtoken-b 0.80% mod-authtoken-b 19% mod-remote-storage-b 0.70% mod-circulation-storage-b 18% mod-circulation-storage-b 0.60% mod-remote-storage-b 17% mod-circulation-b 0.30% mod-inventory-storage-b 13% mod-patron-blocks-b 0.20% nginx-okapi 4.00%

Service CPU Utilization

Here we can see that mod-entities-links used 53% CPU in spikes.

Service Memory Utilization

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

DB CPU Utilization

DB CPU was 23%.

DB Connections

Max number of DB connections was 1350.

DB load

Top SQL-queries

 

#

TOP SQL statements

#

TOP SQL statements

1

select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,aa1_0.sft_headings,
aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1 offset $2 rows fetch first $3 rows only

2

select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?

3

SELECT cs00000int_0004_mod_circulation_storage.count_estimate(?)

4

select this_.fl_id as fl_id1_44_0_, this_.fl_family as fl_famil2_44_0_, this_.fl_name as fl_name3_44_0_, this_.fl_owner as fl_owner4_44_0_ from mod_agreements__system.federation_lock this_ where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_

Resource utilization for Test №3

CPU RAM mod-entities-links-b 38% mod-data-import-b 49% mod-inventory-b 14% mod-circulation-item-b 44% mod-quick-marc-b 8.60% mod-source-record-storage-b 42% mod-pubsub-b 5.50% mod-source-record-manager-b 41% nginx-okapi 3.80% mod-entities-links-b 38% pub-okapi 3.30% mod-inventory-b 37% mod-di-converter-storage-b 2.10% okapi-b 32% mod-feesfines-b 2.10% mod-di-converter-storage-b 32% mod-configuration-b 1.80% mod-feesfines-b 31% mod-source-record-manager-b 1.70% mod-quick-marc-b 31% okapi-b 1.50% mod-patron-blocks-b 29% mod-source-record-storage-b 1.30% mod-configuration-b 28% mod-data-import-b 1.20% mod-users-bl-b 26% mod-remote-storage-b 0.80% mod-inventory-storage-b 25% mod-authtoken-b 0.60% mod-pubsub-b 24% mod-circulation-storage-b 0.50% mod-authtoken-b 19% mod-users-bl-b 0.50% mod-circulation-storage-b 18.00% mod-circulation-b 0.30% mod-remote-storage-b 17.00% mod-patron-blocks-b 0.30% nginx-okapi 4%

Service CPU Utilization

Here we can see that mod-entities-links used 39% CPU in spikes.

Service Memory Utilization

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

DB CPU Utilization

DB CPU was 43%.

DB Connections

Max number of DB connections was 1355.

DB load

Top SQL-queries

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,
aa1_0.sft_headings,aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1
offset $2 rows fetch first $3 rows only

2

WITH cte AS (SELECT count(*) AS total_count FROM cs00000int_0001_mod_source_record_manager.job_execution WHERE subordination_type <> ? AND ? AND status IN (?, ?, ?) AND job_profile_id NOT IN (?, ?)
AND file_name NOT IN (?) AND subordination_type NOT IN (?) AND NOT job_profile_hidden AND NOT is_deleted) SELECT j.*, cte.*, p.total_records_count total,
p.succeeded_records_count + p.error_records_count currently_processed, (select jsonb_agg(x) composite_data from (select status, count(?) cnt, sum(p

3

select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?

4

WITH cte AS (SELECT count(*) AS total_count FROM cs00000int_0001_mod_source_record_manager.job_execution WHERE subordination_type <> ? AND ? AND status <> ? AND ui_status IN (?, ?, ?)

AND subordination_type NOT IN (?) AND NOT job_profile_hidden AND NOT is_deleted) SELECT j.*, cte.*, p.total_records_count total, p.succeeded_records_count + p.error_records_count currently_processed,

(select jsonb_agg(x) composite_data from (select status, count(?) cnt, sum(p1.total_records_count) total_records_co

5

select this_.fl_id as fl_id1_53_0_, this_.fl_family as fl_famil2_53_0_, this_.fl_name as fl_name3_53_0_, this_.fl_owner as fl_owner4_53_0_ from mod_serials_management__system.federation_lock this_
where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_

Appendix

Infrastructure

PTF - environment Quesnelia (qcon)

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

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

  • OpenSearch

    • domain: fse

    • Number of nodes: 6

    • 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

 

Module Task Def. Revision Module Version Task Count Mem Hard Limit Mem Soft limit CPU units Xmx MetaspaceSize MaxMetaspaceSize qcon-pvt Thu May 23 10:47:25 UTC 2024 mod-remote-storage 1 mod-remote-storage:3.2.0 2 4920 4472 1024 3960 512 512 mod-finance-storage 1 mod-finance-storage:8.6.0 2 1024 896 1024 700 88 128 mod-ncip 1 mod-ncip:1.14.4 2 1024 896 128 768 88 128 mod-agreements 1 mod-agreements:7.0.0 2 1592 1488 128 0 0 0 mod-ebsconet 1 mod-ebsconet:2.2.0 2 1248 1024 128 700 128 256 mod-consortia 1 mod-consortia:1.1.0 2 3072 2048 128 2048 512 1024 mod-organizations 1 mod-organizations:1.9.0 2 1024 896 128 700 88 128 mod-serials-management 1 mod-serials-management:1.0.0 2 2480 2312 128 1792 384 512 mod-settings 1 mod-settings:1.0.3 2 1024 896 200 768 88 128 mod-search 9 mod-search:3.3.0-SNAPSHOT.224 2 2592 2480 2048 1440 512 1024 edge-dematic 1 edge-dematic:2.2.0 1 1024 896 128 768 88 128 mod-data-import 1 mod-data-import:3.1.0 1 2048 1844 256 1292 384 512 mod-tags 1 mod-tags:2.2.0 2 1024 896 128 768 88 128 mod-authtoken 3 mod-authtoken:2.15.1 2 1440 1152 512 922 88 128 edge-courses 1 edge-courses:1.4.0 2 1024 896 128 768 88 128 mod-inventory-update 1 mod-inventory-update:3.3.0 2 1024 896 128 768 88 128 mod-notify 1 mod-notify:3.2.0 2 1024 896 128 768 88 128 mod-configuration 1 mod-configuration:5.10.0 2 1024 896 128 768 88 128 mod-orders-storage 1 mod-orders-storage:13.7.0 2 1024 896 512 700 88 128 edge-caiasoft 1 edge-caiasoft:2.2.0 2 1024 896 128 768 88 128 mod-login-saml 1 mod-login-saml:2.8.0 2 1024 896 128 768 88 128 mod-erm-usage-harvester 1 mod-erm-usage-harvester:4.5.0 2 1024 896 128 768 88 128 mod-password-validator 1 mod-password-validator:3.2.0 2 1440 1298 128 768 384 512 mod-licenses 1 mod-licenses:6.0.0 2 2480 2312 128 1792 384 512 mod-gobi 1 mod-gobi:2.8.0 2 1024 896 128 700 88 128 mod-bulk-operations 1 mod-bulk-operations:2.0.0 2 3072 2600 1024 1536 384 512 mod-fqm-manager 1 mod-fqm-manager:2.0.1 2 3000 2600 128 2048 384 512 edge-dcb 1 edge-dcb:1.1.0 2 1024 896 128 768 88 128 mod-graphql 1 mod-graphql:1.12.1 2 1024 896 128 768 88 128 mod-finance 1 mod-finance:4.9.0 2 1024 896 128 700 88 128 mod-erm-usage 1 mod-erm-usage:4.7.0 2 1024 896 128 768 88 128 mod-batch-print 1 mod-batch-print:1.1.0 2 1024 896 128 768 88 128 mod-copycat 1 mod-copycat:1.6.0 2 1024 512 128 768 88 128 mod-lists 1 mod-lists:2.0.0 2 3000 2600 128 2048 384 512 mod-entities-links 1 mod-entities-links:3.0.0 2 2592 2480 400 1440 0 1024 mod-permissions 2 mod-permissions:6.5.0 2 1684 1544 512 1024 384 512 pub-edge 1 pub-edge:2023.06.14 2 1024 896 128 768 0 0 mod-orders 1 mod-orders:12.8.0 2 2048 1440 1024 1024 384 512 edge-patron 1 edge-patron:5.1.0 2 1024 896 256 768 88 128 edge-ncip 1 edge-ncip:1.9.2 2 1024 896 128 768 88 128 mod-users-bl 1 mod-users-bl:7.7.0 2 1440 1152 512 922 88 128 mod-invoice 1 mod-invoice:5.8.0 2 1440 1152 512 922 88 128 mod-inventory-storage 2 mod-inventory-storage:27.2.0-SNAPSHOT.738 2 4096 3690 2048 3076 384 512 mod-user-import 1 mod-user-import:3.8.0 2 1024 896 128 768 88 128 mod-sender 1 mod-sender:1.12.0 2 1024 896 128 768 88 128 edge-oai-pmh 1 edge-oai-pmh:2.9.0 2 1512 1360 1024 1440 384 512 mod-data-export-worker 1 mod-data-export-worker:3.2.1 2 3072 2048 1024 2048 384 512 mod-rtac 1 mod-rtac:3.6.0 2 1024 896 128 768 88 128 mod-circulation-storage 1 mod-circulation-storage:17.2.0 2 2880 2592 1536 1814 384 512 mod-source-record-storage 1 mod-source-record-storage:5.8.0 2 5600 5000 2048 3500 384 512 mod-calendar 1 mod-calendar:3.1.0 2 1024 896 128 768 88 128 mod-event-config 1 mod-event-config:2.7.0 2 1024 896 128 768 88 128 mod-courses 1 mod-courses:1.4.10 2 1024 896 128 768 88 128 mod-circulation-item 1 mod-circulation-item:1.0.0 2 1024 896 128 0 0 0 mod-inventory 3 mod-inventory:20.2.0 2 2880 2592 1024 1814 384 512 mod-email 1 mod-email:1.17.0 2 1024 896 128 768 88 128 mod-circulation 2 mod-circulation:24.2.1 2 2880 2592 1536 1814 384 512 mod-pubsub 1 mod-pubsub:2.13.0 2 1536 1440 1024 922 384 512 mod-di-converter-storage 2 mod-di-converter-storage:2.2.2 2 1024 896 128 768 88 128 edge-orders 1 edge-orders:3.0.0 2 1024 896 128 768 88 128 edge-rtac 1 edge-rtac:2.7.1 2 1024 896 128 768 88 128 mod-users 1 mod-users:19.3.1 2 1024 896 128 768 88 128 mod-template-engine 1 mod-template-engine:1.20.0 2 1024 896 128 768 88 128 mod-patron-blocks 1 mod-patron-blocks:1.10.0 2 1024 896 1024 768 88 128 mod-audit 1 mod-audit:2.9.0 2 1024 896 128 768 88 128 edge-fqm 1 edge-fqm:2.0.0 2 1024 896 128 768 88 128 mod-source-record-manager 1 mod-source-record-manager:3.8.0 2 5600 5000 2048 3500 384 512 nginx-edge 1 nginx-edge:2023.06.14 2 1024 896 128 0 0 0 mod-quick-marc 1 mod-quick-marc:5.1.0 1 2288 2176 128 1664 384 512 nginx-okapi 1 nginx-okapi:2023.06.14 2 1024 896 128 0 0 0 okapi-b 1 okapi:5.3.0 3 1684 1440 1024 922 384 512 mod-feesfines 1 mod-feesfines:19.1.0 2 1024 896 128 768 88 128 mod-invoice-storage 1 mod-invoice-storage:5.8.0 2 1872 1536 1024 1024 384 512 mod-service-interaction 1 mod-service-interaction:4.0.1 2 2048 1844 256 1290 384 512 mod-dcb 1 mod-dcb:1.1.0 2 1024 896 128 768 88 128 mod-patron 1 mod-patron:6.1.0 2 1024 896 128 768 88 128 mod-data-export 1 mod-data-export:5.0.0 1 2048 1524 1024 0 0 0 mod-oai-pmh 1 mod-oai-pmh:3.13.0 2 4096 3690 2048 3076 384 512 edge-connexion 1 edge-connexion:1.2.0 2 1024 896 128 768 88 128 mod-notes 1 mod-notes:5.2.0 2 1024 896 128 952 384 512 mod-kb-ebsco-java 1 mod-kb-ebsco-java:4.0.0 2 1024 896 128 768 88 128 mod-login 1 mod-login:7.11.0 2 1440 1298 1024 768 384 512 mod-organizations-storage 1 mod-organizations-storage:4.7.0 2 1024 896 128 700 88 128 mod-data-export-spring 1 mod-data-export-spring:3.2.0 1 2048 1844 256 1536 384 512 pub-okapi 1 pub-okapi:2023.06.14 2 1024 896 128 768 0 0 mod-eusage-reports 1 mod-eusage-reports:2.1.1 2 1024 896 128 768 88 128

 

Methodology/Approach

Export deleted MARC authority records were started from CarrierIO (QCON_ECS_Export_Deleted_Records) on a qcon Quesnelia [ECS] environment..

Test runs:

  • Test 1: Tested export 100k deleted MARC authority records,  started on Central Office tenant(cs00000int) with one user.

  • Test 2: Tested export 300k deleted MARC authority records,  started on Central Office tenant(cs00000int) with one user.

  • Test 3: Tested export 646k deleted MARC authority records,  started on Central Office tenant(cs00000int) with one user.

  • After tests all deleted MARC authority records were removed from Database and added again, then repeat tests with 100k and 300k MARC authority records on Central Office tenant(cs00000int) with one user.

At the time of the test run, Grafana was not available. As a result, response times for Export deleted MARC authority records were taken from a .jtl files. These results were visualized in JMeter using a Listener (Response Times Over Time).