PTF - Performance testing of Export All Endpoint (Quesnelia) [non-ECS]

PTF - Performance testing of Export All Endpoint (Quesnelia) [non-ECS]

Overview

  • This document contains results of testing Data Export All by the endpoint (POST data-export/export-all) with Default instances, authority, export holdings job profiles and with the custom profile Example 1 on the Quesnelia [non-ECS] release on qcp1 environment. 

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

Summary

  • Data Export tests finished successfully on qcp1 environment using the Default instances, authority, holdings export job profile and Custom Mapping Profiles - Example 1.

  • During the tests data-export-all with Custom Mapping Profiles - Example 1 we observe Errors converting json to marc for instances with 9 and more holdings, task for investigation was created https://folio-org.atlassian.net/browse/MDEXP-780

  • Two very slow queries when checking deleted records in tables mod_inventory_storage.audit_instance and mod_inventory_storage.audit_holdings_record for Data-Export-All, task for investigation was created https://folio-org.atlassian.net/browse/MODINVSTOR-1234

  • No memory leaks are observed.

  • The data export duration for default profiles and the duration for the concurrent test of default profiles plus the export of 10,000 instances with a custom profile are the same. This is because Data-Export-All uses one data-export module task, so if we run concurrent tests, each job will be executed on a separate data-export module task, because data-export module has two tasks.

Test Runs and Results

This table contains durations for Data Export. 

Test #

Job Profile

Count of exported records

Data Export Duration
(hh:mm:ss)

Results

Test #

Job Profile

Count of exported records

Data Export Duration
(hh:mm:ss)

Results

1

Default instances export job profile

19664506

3:03:59

COMPLETED

Custom Mapping Profiles - Example 1

10000

0:11:14

2

Default authority export job profile

6186517

3:29:03

COMPLETED

Custom Mapping Profiles - Example 1

10000

0:01:49

3

Default holdings export job profile

20669235

1:46:56

COMPLETED

Custom Mapping Profiles - Example 1

10000

0:01:22

4

Default instances export job profile

19789508

2:34:09

COMPLETED

5

Default holdings export job profile

20794226

1:46:16

COMPLETED

6

Default authority export job profile

6186517

3:28:51

COMPLETED

7

Custom Mapping Profiles - Example 1

19789503

5:51:08

COMPLETED_WITH_ERRORS

 

 

Resource utilization for Test №1, №2 and №3

Service Name

CPU

Service Name

RAM

mod-data-export-b 143% mod-data-export-b 106% mod-inventory-b 12% mod-source-record-manager-b 53% mod-source-record-manager-b 1.90% mod-source-record-storage-b 51% mod-source-record-storage-b 1.40% okapi-b 47% okapi-b 0.80% mod-users-bl-b 43% mod-authtoken-b 0.60% mod-inventory-b 26% mod-users-bl-b 0.50% mod-authtoken-b 26% mod-inventory-storage-b 0.30% mod-inventory-storage-b 17% nginx-okapi 0.10% pub-okapi 4.70% pub-okapi 0.00% nginx-okapi 4.60%

Service CPU Utilization

Here we can see that mod-data-export used 150% CPU.

Service Memory Utilization

Here we can see that data-export module used 95% memory.

 

Kafka metrics

 

 

DB CPU Utilization

DB CPU was 90%.

DB Connections

Max number of DB connections was 750

DB load

Top SQL-queries

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

SELECT id, content, external_id, record_type, state, leader_record_status, suppress_discovery FROM v_authority_all WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC offset $3 rows fetch next $4 rows only

2

select ie1_0.id,ie1_0.jsonb from v_instance ie1_0 where ie1_0.id in ($1...)

3

SELECT * FROM v_marc_instance_all_non_deleted_non_suppressed WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC offset $3 rows fetch next $4 rows only

4

SELECT * FROM v_folio_instance_all_non_deleted_non_suppressed WHERE id BETWEEN $1 AND $2 ORDER BY id ASC fetch first $3 rows only

5

SELECT * FROM v_marc_instance_all_non_deleted_non_suppressed WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC fetch first $3 rows only

Resource utilization for Test №4, №5 and №6

Service Name CPU Service Name RAM

mod-data-export-b

177%

mod-data-export-b

103%

mod-inventory-b 12% mod-source-record-manager-b 52% mod-source-record-manager-b 1.80% mod-inventory-b 48% mod-source-record-storage-b 1.40% okapi-b 38% okapi-b 1.00% mod-source-record-storage-b 31% mod-authtoken-b 0.80% mod-users-bl-b 31% mod-users-bl-b 0.50% mod-authtoken-b 26% mod-inventory-storage-b 0.30% mod-inventory-storage-b 17% nginx-okapi 0.20% nginx-okapi 4.70% pub-okapi 0.10% pub-okapi 4.40%

Service CPU Utilization

Here we can see that mod-data-export used 194% CPU.

Service Memory Utilization

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

 

Kafka metrics

 

 

DB CPU Utilization

DB CPU was 92%.

DB Connections

Max number of DB connections was 762.

DB load

Top SQL-queries

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

SELECT id, content, external_id, record_type, state, leader_record_status, suppress_discovery FROM v_authority_all WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC offset $3 rows fetch next $4 rows only

2

select ie1_0.id,ie1_0.jsonb from v_instance ie1_0 where ie1_0.id in ($1...)

3

SELECT * FROM v_marc_instance_all_non_deleted_non_suppressed WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC offset $3 rows fetch next $4 rows only

4

SELECT * FROM v_folio_instance_all_non_deleted_non_suppressed WHERE id BETWEEN $1 AND $2 ORDER BY id ASC fetch first $3 rows only

5

SELECT * FROM v_marc_instance_all_non_deleted_non_suppressed WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC fetch first $3 rows only

Resource utilization for Test №7

Service Name CPU Service Name RAM

mod-data-export-b

145%

mod-data-export-b

92%

mod-inventory-b 12.80% mod-source-record-manager-b 47% mod-source-record-manager-b 1.70% okapi-b 46% mod-authtoken-b 1.40% mod-source-record-storage-b 39% mod-source-record-storage-b 1.20% mod-inventory-b 36% okapi-b 1.00% mod-users-bl-b 34% mod-users-bl-b 0.40% mod-authtoken-b 25% mod-inventory-storage-b 0.40% mod-inventory-storage-b 15% nginx-okapi 0.30% nginx-okapi 4.50% pub-okapi 0.10% pub-okapi 4.40%

Service CPU Utilization

Here we can see that mod-data-export used 145% CPU.

Service Memory Utilization

Here we can see that mod-data-export used 93% memory .

 

Kafka metrics

 

 

 

DB CPU Utilization

DB CPU was 82%.

DB Connections

Max number of DB connections was 730.

DB load

Top SQL-queries

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

select ie1_0.id,ie1_0.holdings_record_id,ie1_0.jsonb from v_item ie1_0 where ie1_0.holdings_record_id in ($1)

2

select iwhe1_0.id,iwhe1_0.hrid from v_instance_hrid iwhe1_0 where iwhe1_0.id in ($1,$2...)

3

SELECT * FROM v_marc_instance_all_non_deleted_non_suppressed WHERE external_id BETWEEN $1 AND $2 ORDER BY id ASC offset $3 rows fetch next $4 rows only

4

select hre1_0.id,hre1_0.instance_id,hre1_0.jsonb from v_holdings_record hre1_0 where hre1_0.instance_id=$1

5

SELECT * FROM v_folio_instance_all_non_deleted_non_suppressed WHERE id BETWEEN $1 AND $2 ORDER BY id ASC fetch first $3 rows only

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 - 27289981

      • items - 28463562

      • holdings - 27535678

      • authorities - 6193573

  • 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

 

Cluster Resources - qcp1 (Mon Jul 08 15:51:20 EEST 2024) Module Task Definition Revision Module Version Task Count Mem Hard Limit Mem Soft Limit CPU Units Xmx Metaspace Size Max Metaspace Size mod-remote-storage 5 mod-remote-storage:3.2.0 2 4920 4472 1024 3960 512 512 mod-ncip 5 mod-ncip:1.14.4 2 1024 896 128 768 88 128 mod-finance-storage 5 mod-finance-storage:8.6.0 2 1024 896 1024 700 88 128 mod-agreements 5 mod-agreements:7.0.0 2 1592 1488 128 0 0 0 mod-ebsconet 5 mod-ebsconet:2.2.0 2 1248 1024 128 700 128 256 mod-organizations 5 mod-organizations:1.9.0 2 1024 896 128 768 88 128 mod-consortia 3 mod-consortia:1.1.0 2 5136 4776 1024 4416 384 512 edge-sip2 3 edge-sip2:3.2.0-SNAPSHOT.209 2 1024 896 128 768 88 128 mod-serials-management 5 mod-serials-management:1.0.0 2 2480 2312 128 1792 384 512 mod-settings 5 mod-settings:1.0.3 2 1024 896 200 768 88 128 mod-data-import 8 mod-data-import:3.1.0 1 2048 1844 256 1292 384 512 edge-dematic 5 edge-dematic:2.2.0 1 1024 896 128 768 88 128 mod-search 5 mod-search:3.2.0 2 2592 2480 2048 1440 512 1024 mod-inn-reach 3 mod-inn-reach:3.2.0-SNAPSHOT.86 2 3600 3240 1024 2880 512 1024 mod-tags 5 mod-tags:2.2.0 2 1024 896 128 768 88 128 edge-courses 5 edge-courses:1.4.0 2 1024 896 128 768 88 128 mod-authtoken 6 mod-authtoken:2.15.1 2 1440 1152 512 922 88 128 mod-inventory-update 5 mod-inventory-update:3.3.0 2 1024 896 128 768 88 128 mod-notify 5 mod-notify:3.2.0 2 1024 896 128 768 88 128 mod-configuration 5 mod-configuration:5.10.0 2 1024 896 128 768 88 128 mod-orders-storage 5 mod-orders-storage:13.7.0 2 1024 896 512 700 88 128 edge-caiasoft 5 edge-caiasoft:2.2.0 2 1024 896 128 768 88 128 mod-login-saml 5 mod-login-saml:2.8.0 2 1024 896 128 768 88 128 mod-erm-usage-harvester 5 mod-erm-usage-harvester:4.5.0 2 1024 896 128 768 88 128 mod-licenses 5 mod-licenses:6.0.0 2 2480 2312 512 1792 384 512 mod-gobi 5 mod-gobi:2.8.0 2 1024 896 128 768 88 128 mod-password-validator 5 mod-password-validator:3.2.0 2 1440 1298 128 768 384 512 mod-bulk-operations 5 mod-bulk-operations:2.0.0 2 3072 2600 1024 1536 384 512 mod-fqm-manager 5 mod-fqm-manager:2.0.1 2 1024 896 128 768 88 128 edge-dcb 5 edge-dcb:1.1.0 2 1024 896 128 768 88 128 mod-graphql 6 mod-graphql:1.12.1 2 1024 896 128 768 88 128 mod-finance 5 mod-finance:4.9.0 2 1024 896 128 768 88 128 mod-erm-usage 5 mod-erm-usage:4.7.0 2 2800 2550 1024 1800 384 512 mod-batch-print 6 mod-batch-print:1.1.0 2 1024 896 128 768 88 128 mod-copycat 5 mod-copycat:1.6.0 2 1024 896 128 768 88 128 mod-lists 5 mod-lists:2.0.0 2 1024 896 128 768 88 128 mod-entities-links 6 mod-entities-links:3.0.0 2 2592 2480 400 1440 0 1024 mod-permissions 10 mod-permissions:6.5.0 2 1684 1544 512 1024 384 512 pub-edge 3 pub-edge:2023.06.14 2 1024 896 128 768 0 0 mod-orders 5 mod-orders:12.8.0 2 2048 1740 1024 1024 384 512 edge-patron 5 edge-patron:5.1.0 2 1024 896 256 768 88 128 edge-ncip 5 edge-ncip:1.9.2 2 1024 896 128 768 88 128 edge-inn-reach 3 edge-inn-reach:3.1.1-SNAPSHOT.45 2 1024 896 128 768 88 128 mod-users-bl 5 mod-users-bl:7.7.0 2 1440 1152 512 922 88 128 mod-oa 4 mod-oa:2.1.0-SNAPSHOT.62 2 1024 896 128 768 88 256 mod-inventory-storage 5 mod-inventory-storage:27.1.0 2 4096 3690 2048 3076 384 512 mod-invoice 6 mod-invoice:5.8.0 2 1440 1152 512 922 88 128 mod-user-import 5 mod-user-import:3.8.0 2 1024 896 128 768 88 128 mod-sender 6 mod-sender:1.12.0 2 1024 896 128 768 88 128 edge-oai-pmh 5 edge-oai-pmh:2.9.0 2 1512 1360 1024 1440 384 512 mod-data-export-worker 5 mod-data-export-worker:3.2.1 2 3072 2800 1024 2048 384 512 mod-rtac 5 mod-rtac:3.6.0 2 1024 896 128 768 88 128 mod-circulation-storage 5 mod-circulation-storage:17.2.0 2 2880 2592 1536 1814 384 512 mod-calendar 5 mod-calendar:3.1.0 2 1024 896 128 768 88 128 mod-source-record-storage 7 mod-source-record-storage:5.8.0 2 5600 5000 2048 3500 384 512 mod-event-config 5 mod-event-config:2.7.0 2 1024 896 128 768 88 128 mod-courses 5 mod-courses:1.4.10 2 1024 896 128 768 88 128 mod-circulation-item 5 mod-circulation-item:1.0.0 2 1024 896 128 768 88 128 mod-inventory 6 mod-inventory:20.2.0 2 4096 3688 1024 1814 384 512 mod-email 5 mod-email:1.17.0 2 2800 2550 512 1800 384 512 mod-pubsub 5 mod-pubsub:2.13.0 2 1536 1440 1024 922 384 512 mod-circulation 5 mod-circulation:24.2.0 2 2880 2592 1536 1814 384 512 mod-di-converter-storage 5 mod-di-converter-storage:2.2.0 2 1024 896 128 768 88 128 edge-rtac 5 edge-rtac:2.7.1 2 1024 896 128 768 88 128 edge-orders 5 edge-orders:3.0.0 2 1024 896 128 768 88 128 mod-users 6 mod-users:19.3.1 2 1024 896 128 768 88 128 mod-template-engine 5 mod-template-engine:1.20.0 2 1024 896 128 768 88 128 mod-patron-blocks 5 mod-patron-blocks:1.10.0 2 1024 896 1024 768 88 128 mod-audit 5 mod-audit:2.9.0 2 1024 896 128 768 88 128 edge-fqm 5 edge-fqm:2.0.0 2 1024 896 128 768 88 128 mod-source-record-manager 6 mod-source-record-manager:3.9.0-SNAPSHOT.330 2 5600 5000 2048 3500 384 512 nginx-edge 3 nginx-edge:2023.06.14 2 1024 896 128 0 0 0 mod-quick-marc 5 mod-quick-marc:5.1.0 1 2288 2176 128 1664 384 512 nginx-okapi 3 nginx-okapi:2023.06.14 2 1024 896 128 0 0 0 okapi-b 5 okapi:5.3.0 3 1684 1440 1024 922 384 512 mod-feesfines 5 mod-feesfines:19.1.0 2 1024 896 128 768 88 128 mod-invoice-storage 5 mod-invoice-storage:5.8.0 2 1872 1536 1024 1024 384 512 mod-dcb 6 mod-dcb:1.1.0 2 1024 896 128 768 88 128 mod-service-interaction 5 mod-service-interaction:4.0.1 2 2048 1844 256 1290 384 512 mod-data-export 17 mod-data-export:5.0.4 1 2048 1844 2048 0 0 0 mod-patron 5 mod-patron:6.1.0 2 1024 896 128 768 88 128 mod-oai-pmh 5 mod-oai-pmh:3.13.0 2 4096 3690 2048 3076 384 512 edge-connexion 5 edge-connexion:1.2.0 2 1024 896 128 768 88 128 mod-kb-ebsco-java 5 mod-kb-ebsco-java:4.0.0 2 1024 896 128 768 88 128 mod-notes 5 mod-notes:5.2.0 2 1024 896 128 952 384 512 mod-data-export-spring 5 mod-data-export-spring:3.2.0 1 2048 1844 256 1536 384 512 mod-organizations-storage 5 mod-organizations-storage:4.7.0 2 1024 896 128 768 88 128 mod-login 5 mod-login:7.11.0 2 1440 1298 1024 768 384 512 pub-okapi 3 pub-okapi:2023.06.14 2 1024 896 128 768 0 0 mod-eusage-reports 5 mod-eusage-reports:2.1.1 2 1024 896 128 768 88 128

 

Methodology/Approach

Test set:

  • Test 1: Manually tested 10k instances records files with the custom profile Example 1 and concurrently run Export All instances started by JMeter using endpoint(POST data-export/export-all) with profile Default instances export job profile. Data Export started on one tenant (fs09000000) with two tasks for data-export module.

  • Test 2: Manually tested 10k instances records files with the custom profile Example 1 and concurrently run Export All instances started by JMeter using endpoint(POST data-export/export-all) with profile Default holdings export job profile. Data Export started on one tenant (fs09000000) with two tasks for data-export module.

  • Test 3: Manually tested 10k instances records files with the custom profile Example 1 and concurrently run Export All instances started by JMeter using endpoint(POST data-export/export-all) with profile Default authority export job profile. Data Export started on one tenant (fs09000000) with two tasks for data-export module.

Test set:

  • Test 4: Export All instances started by JMeter using endpoint(POST data-export/export-all) with profile Default instances export job profile. Data Export started on one tenant (fs09000000) with one task for data-export module.

  • Test 5: Export All holdings started by JMeter using endpoint(POST data-export/export-all) with profile Default holdings export job profile. Data Export started on one tenant (fs09000000) with one task for data-export module.

  • Test 6: Export All authority started by JMeter using endpoint(POST data-export/export-all) with profile Default authority export job profile. Data Export started on one tenant (fs09000000) with one task for data-export module.

Test №7:

  • Export All instances started by JMeter using endpoint(POST data-export/export-all) with the Custom Profile Example 1. Data Export started on one tenant (fs09000000) with one task for data-export module.

 

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

SQL Query
select jsonb->'exportedFiles'->0->>'fileName' as fileName, job_profile_name,exported, started_date,completed_date, completed_date - started_date as duration ,status from fs09000000_mod_data_export.job_executions where started_date > '2024-07-8' order by started_date desc limit 1000;