Overview

This document contains the results of testing Data Export (MARC BIB) on the Quesnelia [ECS] release on qcon environment.

PERF-844 - Getting issue details... STATUS

Summary

Data Export tests finished successfully on qcon environment using the profiles Default instances export job profile and srs - holdings and items job profile.
Data Export test were run on College and Central tenants, but results for comparing between environment releases were taken from College tenant.
Comparing with previous testing results Poppy and Quesnelia releases
- Data Export processed all files including file with 500k records without errors for Quesnelia releases.
- Data Export durations improved - 80% in Average for Quesnelia releases.
During testing, we noticed spikes in the mod-data-export up to 452% CPU.
No memory leak is suspected for Data Export modules.

Test Results

This table contains durations for Data Export with 2 job profiles.

Profile	CSV File	Tenant College (cs00000int_0001)		Central Tenant (cs00000int)
Profile	CSV File	Result	Status	Result	Status
DE MARC Bib (Default instances export job profile)	1k.csv	0:00:02	COMPLETED	0:00:05	COMPLETED
	100k.csv	0:02:39	COMPLETED	0:04:24	COMPLETED
	500k.csv	0:05:21	COMPLETED	0:06:17	COMPLETED
DE MARC Bib (srs - holdings and items)	1k.csv	0:00:05	COMPLETED	0:00:05	COMPLETED
	100k.csv	0:08:15	COMPLETED	0:05:58	COMPLETED
	500k.csv	0:09:22	COMPLETED	0:08:28	COMPLETED

Comparison

This table contains durations comparison between Poppy and Quesnelia releases.

Profile	CSV File	DE Duration/Status Orchid		DE Duration/Status Poppy 1 set		DE Duration/Status Quesnelia Tenant College (cs00000int_0001)		DE Duration, DELTA Poppy/Quesnelia
Profile	CSV File	Result	Status	Result	Status	Result	Status	hh:mm:ss / percent
DE MARC Bib (Default instances export job profile)	1k.csv			00:00:08	COMPLETED	0:00:02	COMPLETED	-00:00:06 -75%
	100k.csv			00:15:36	COMPLETED	0:02:39	COMPLETED	-00:12:57 -83.02%
	500k.csv			00:57:25	FAIL	0:05:21	COMPLETED	-00:52:04 -90.68%
DE MARC Bib (srs - holdings and items)	1k.csv	00:00:27	COMPLETED	00:00:29	COMPLETED	0:00:05	COMPLETED	-00:00:24 -82.76%
	100k.csv	00:47:51	COMPLETED	00:47:23	COMPLETED	0:08:15	COMPLETED	-00:39:08 -82.59%
	500k.csv	04:00:26	COMPLETED	04:11:09	FAIL	0:09:22	COMPLETED	-04:01:47 -96.27%

Resource utilization for Test #1 and Test #2

Resource utilization table

CPU		RAM
mod-data-export-b	452%	mod-data-export-b	75%
mod-inventory-b	13%	mod-source-record-manager-b	53%
mod-source-record-storage-b	2.40%	mod-inventory-b	48%
mod-source-record-manager-b	1.80%	okapi-b	32%
okapi-b	1.10%	mod-source-record-storage-b	30%
mod-authtoken-b	0.90%	mod-authtoken-b	20%
mod-users-bl-b	0.50%	mod-users-bl-b	19%
nginx-okapi	0.40%	mod-inventory-storage-b	16%
mod-inventory-storage-b	0.40%	nginx-okapi	5%

Service CPU Utilization

Here we can see that mod-data-export used 452% CPU in spike.

Service Memory Utilization

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

DB CPU Utilization

DB CPU spike was 32%.

DB Connections

DB connections was 1470.

DB load

Top SQL-queries

#	TOP 5 SQL statements
1	`INSERT INTO job_executions_export_ids (job_execution_id, instance_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`
2	`INSERT INTO job_executions_export_ids (job_execution_id, instance_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`
3	`select mre1_0.id,mre1_0.content,mre1_0.external_id,mre1_0.leader_record_status,mre1_0.record_type,mre1_0.state,mre1_0.suppress_discovery from v_marc_records_lb mre1_0 where mre1_0.external_id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$`
4	`select iwhe1_0.id,iwhe1_0.hrid from v_instance_hrid iwhe1_0 where iwhe1_0.id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$1`
5	`select hre1_0.id,hre1_0.instance_id,hre1_0.jsonb from v_holdings_record hre1_0 where hre1_0.instance_id=$1`

Resource utilization for Test #3 and Test #4

Resource utilization table

CPU		RAM
mod-data-export-b	336%	mod-data-export-b	73%
mod-inventory-b	14%	mod-source-record-manager-b	53%
mod-source-record-storage-b	2.20%	mod-inventory-b	46%
mod-source-record-manager-b	1.70%	okapi-b	33%
okapi-b	0.90%	mod-source-record-storage-b	30%
mod-authtoken-b	0.80%	mod-users-bl-b	21%
mod-users-bl-b	0.50%	mod-authtoken-b	21%
mod-inventory-storage-b	0.30%	mod-inventory-storage-b	16%
nginx-okapi	0.20%	nginx-okapi	5%

Service CPU Utilization

Here we can see that mod-data-export used 336% CPU in spike.

Service Memory Utilization

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

DB CPU Utilization

DB CPU was 35%.

DB Connections

DB connections was 1377.

DB load

Top SQL-queries


select mre1_0.id,mre1_0.content,mre1_0.external_id,mre1_0.leader_record_status,mre1_0.record_type,mre1_0.state,mre1_0.suppress_discovery from v_marc_records_lb mre1_0 where mre1_0.external_id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$
INSERT INTO job_executions_export_ids (job_execution_id, instance_id) VALUES ($1, $2) ON CONFLICT DO NOTHING
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 hre1_0.id,hre1_0.instance_id,hre1_0.jsonb from v_holdings_record hre1_0 where hre1_0.instance_id=$1
select eie1_0.id,eie1_0.instance_id,eie1_0.job_execution_id from job_executions_export_ids eie1_0 where eie1_0.job_execution_id=$1 and eie1_0.instance_id>=$2 and eie1_0.instance_id<=$3 order by eie1_0.instance_id offset $4 rows fetch first $5 rows only

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
OpenSearch
- domain: fse
- 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

Quesnelia modules memory and CPU parameters

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

Methodology/Approach

Data Export tests scenario using the profiles Default instances export job profile and srs - holdings and items were started from UI on Quesnelia (qcon) ecs environment.

Test set

Test 1: Manually tested 1k, 100k and 500k records files Data Export started on College tenant(cs00000int_0001) only using Default instances export job profile.
Test 2: Manually tested 1k, 100k and 500k records files Data Export started on College tenant(cs00000int_0001) only using srs - holdings and items job profile.
Test 3: Manually tested 1k, 100k and 500k records files Data Export started on Central tenant(cs00000int) only using Default instances export job profile.
Test 4: Manually tested 1k, 100k and 500k records files Data Export started on Central tenant(cs00000int) only using srs - holdings and items job profile.

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

SQL Query

SELECT 
    jsonb->>'status' AS status,
    to_timestamp((jsonb->>'startedDate')::bigint / 1000) AS startedDate,
    to_timestamp((jsonb->>'completedDate')::bigint / 1000) AS completedDate,
    exported_file->>'fileName' AS fileName,
	jsonb->>'jobProfileName' AS jobProfileName,
    (jsonb->>'completedDate')::bigint - (jsonb->>'startedDate')::bigint AS duration_ms,
    to_char(
        (to_timestamp((jsonb->>'completedDate')::bigint / 1000) - to_timestamp((jsonb->>'startedDate')::bigint / 1000))::interval, 
        'HH24:MI:SS'
    ) AS duration_hhmmss
FROM 
    cs00000int_0001_mod_data_export.job_executions,
    jsonb_array_elements(jsonb->'exportedFiles') AS exported_file
WHERE 
-- 	(jsonb->>'hrId')::int IN (309, 310, 311, 312, 313, 314) -- Central tenant
    (jsonb->>'hrId')::int IN (266, 267, 268, 269, 270, 271)
ORDER BY 
    jsonb->>'startedDate' DESC
LIMIT 10;

#	TOP 5 SQL statements
1	`select mre1_0.id,mre1_0.content,mre1_0.external_id,mre1_0.leader_record_status,mre1_0.record_type,mre1_0.state,mre1_0.suppress_discovery from v_marc_records_lb mre1_0 where mre1_0.external_id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$`
2	`INSERT INTO job_executions_export_ids (job_execution_id, instance_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`
3	`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)`
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 eie1_0.id,eie1_0.instance_id,eie1_0.job_execution_id from job_executions_export_ids eie1_0 where eie1_0.job_execution_id=$1 and eie1_0.instance_id>=$2 and eie1_0.instance_id<=$3 order by eie1_0.instance_id offset $4 rows fetch first $5 rows only`

PTF - Data Export Test Report (Quesnelia) [ECS]

Overview

Summary

Test Results

Comparison

Resource utilization for Test #1 and Test #2

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

Resource utilization for Test #3 and Test #4

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

Appendix

Infrastructure

Methodology/Approach