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.

PERF-890 - Getting issue details... STATUS

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 MDEXP-780 - Getting issue details... STATUS
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 MODINVSTOR-1234 - Getting issue details... STATUS
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
1	Default instances export job profile	19664506	3:03:59	COMPLETED
1	Custom Mapping Profiles - Example 1	10000	0:11:14	COMPLETED
2	Default authority export job profile	6186517	3:29:03	COMPLETED
2	Custom Mapping Profiles - Example 1	10000	0:01:49	COMPLETED
3	Default holdings export job profile	20669235	1:46:56	COMPLETED
3	Custom Mapping Profiles - Example 1	10000	0:01:22	COMPLETED
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

Resource utilization table

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


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
select ie1_0.id,ie1_0.jsonb from v_instance ie1_0 where ie1_0.id in ($1...)
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
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
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

#	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

Resource utilization table

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


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
select ie1_0.id,ie1_0.jsonb from v_instance ie1_0 where ie1_0.id in ($1...)
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
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
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

#	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

Resource utilization table

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


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)
select iwhe1_0.id,iwhe1_0.hrid from v_instance_hrid iwhe1_0 where iwhe1_0.id in ($1,$2...)
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
select hre1_0.id,hre1_0.instance_id,hre1_0.jsonb from v_holdings_record hre1_0 where hre1_0.instance_id=$1
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

#	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

Quesnelia modules memory and CPU parameters

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;

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

Browser not supported