Table of Contents

outline	true

Overview

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

Jira Legacy

server	System Jira
columnIds	issuekey,summary,issuetype,created,updated,duedate,assignee,reporter,priority,status,resolution
columns	key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution
serverId	01505d01-b853-3c2e-90f1-ee9b165564fc
key	PERF-844

Summary

Data Export tests finished successfully on qcon environment using the profiles Default instances export job profile and srs - holdings and items.
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

...

Comparison

This table contains durations comparison between Poppy and Quesnelia releases.

...

Table of Contents

outline	true

Overview

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

Jira Legacy

server	System Jira
columnIds	issuekey,summary,issuetype,created,updated,duedate,assignee,reporter,priority,status,resolution
columns	key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution
serverId	01505d01-b853-3c2e-90f1-ee9b165564fc
key	PERF-844

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 593% CPU.
For Test №5 Data Export started on College tenant(cs00000int_0001), Central tenant(cs00000int) and Professional tenant(cs00000int_0002) concurrently using the Default instances export job profile, we observed that the CPU usage of the mod-data-export module was initially at 44% before the test began then it spiked to 109% during the test and remained elevated without returning to the initial state.

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

hh:mm:ss / percent

DE MARC Bib (Default instances export job profile)

1k.csv

00

0:00:

08

02

COMPLETED

0:00:

02

05

COMPLETED

-00:00:06
-75%

100k.csv

00:15:36COMPLETED

0:02:39

COMPLETED

-00

0:

12

04:

57
-83.02%

24	COMPLETED
500k.csv

00:57:25FAIL

0:05:21

COMPLETED

-00

0:

52

06:

04
-90.68%

17	COMPLETED
DE MARC Bib (srs - holdings and items)	1k.csv

00

0:00:

27

05

COMPLETED

00

0:00:

29

05	COMPLETED
100k.csv	0:

00

08:

05

15

COMPLETED

-00

0:

00

05:

24
-82.76%100k

58	COMPLETED
500k.csv

00

0:

47

09:

51

22

COMPLETED

00

0:

47

08:

23

28

COMPLETED

0:08:15COMPLETED-00:39:08
-82.59%

This table contains durations for Test №5 Data Export for 3 tenants concurrently.

Tenant

CSV File

Result

Status

Tenant College

(cs00000int_0001)

500k.csv

04

0:

00

10:

26

24

COMPLETED

04:11:09FAIL

Tenant Professional

(cs00000int_0002)

500k.csv

0:

09

06:

22

47

COMPLETED

-04:01:47
-96.27%

Resource utilization for Test #1 and Test #2

...

title	Resource utilization table

...

Service CPU Utilization

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

Image Removed

Service Memory Utilization

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

Image Removed

DB CPU Utilization

DB CPU spike was 32%.

Image Removed

DB Connections

DB connections was 1470.

Image Removed

DB load

Image Removed

Top SQL-queries

Image Removed

#TOP 5 SQL statements1

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

3select 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

Central Tenant

(cs00000int)

500k.csv

0:07:56

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

Expand

title	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.

Image Added

Service Memory Utilization

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

Image Added

DB CPU Utilization

DB CPU spike was 32%.

Image Added

DB Connections

DB connections was 1470.

Image Added

DB load

Image Added

Top SQL-queries

Image Added

#	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`

...

Top SQL-queries

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

#	TOP 5 SQL statements
1	5	`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`
`,$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`

Resource utilization for Test #5

Expand

title	Resource utilization table

CPU		RAM
mod-data-export-b	592%	mod-data-export-b	108%
mod-inventory-b	10%	mod-inventory-b	78%
mod-source-record-storage-b	1.80%	mod-source-record-storage-b	40%
mod-authtoken-b	1.70%	mod-source-record-manager-b	39%
mod-source-record-manager-b	1.50%	okapi-b	32%
okapi-b	1.50%	mod-users-bl-b	24%
mod-inventory-storage-b	0.60%	mod-authtoken-b	18%
mod-users-bl-b	0.60%	mod-inventory-storage-b	13%
nginx-okapi	0.40%	nginx-okapi	4%

Service CPU Utilization

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

Image Added

Service Memory Utilization

We observed that the CPU usage of the mod-data-export module was initially at 44% before the test began. It spiked to 109% during the test and remained elevated without returning to the initial state.

Image Added

DB CPU Utilization

DB CPU was 50%.

Image Added

DB Connections

DB connections was 1368.

Image Added

DB load

Image Added

Top SQL-queries

Image Added

Appendix

Infrastructure

PTF - environment Quesnelia (qcon)

...

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.
Test 5: Manually tested 500k records file Data Export started on College tenant(cs00000int_0001), Central tenant(cs00000int) and Professional tenant(cs00000int_0002) concurrently using Default instances export job profile.

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

...

Version	Old Version 22	New Version Current
Changes made by	Stanislav Nehrii	Stanislav Nehrii
Saved on	Jun 01, 2024	Jun 03, 2024

Versions Compared

Key

Overview

Summary

Test Results

Comparison

Overview

Summary

Test Results

Resource utilization for Test #1 and Test #2

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

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

Top SQL-queries

Resource utilization for Test #5

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

Appendix

Infrastructure

Page Comparison

Versions Compared

Key

Overview

Summary

Test Results

Comparison

Overview

Summary

Test Results

Resource utilization for Test #1 and Test #2

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

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

Top SQL-queries

Resource utilization for Test #5

Service CPU Utilization

Service Memory Utilization

DB CPU Utilization

DB Connections

DB load

Top SQL-queries

Appendix

Infrastructure