/
[Ramsons] [non-ECS] Bulk Edit of MARC and FOLIO Instance records

[Ramsons] [non-ECS] Bulk Edit of MARC and FOLIO Instance records

Mar 12, 2025

Overview

  • This document contains the results of the testing Bulk Edit of MARC and FOLIO Instance records by submitting list of identifiers for 100, 1000, 10 000, 100 000 records or triggering bulk edit by submitting the query

  •  Ramsons release on rcp1 environment on a non-ecs okapi environment (tenant fs09000000).

  • Jira https://folio-org.atlassian.net/browse/PERF-1034

Summary

  • Bulk edits could be performed with .csv files of 100, 1000, 10 000, and 100 000 records; however, updating 100k records is very slow and can take up to 11 hours.

  • Bulk edits could be performed by submitting the query but due to https://folio-org.atlassian.net/browse/MODFQMMGR-667 it could be unsuccessful.

  • Memory consumption for all modules shows a stable trend with periodical increasing and decreasing memory usage. No memory leaks are suspected.

  • Service CPU utilization corresponded to the load and did not exceed 2.1%.

  • Instance CPU Utilization did not exceed 13.5% 

  • DB CPU utilization was very high 99.7%.

  • DB connections - up to 1146 on average.

  • Database deadlocks were observed during the Bulk edit updating records step https://folio-org.atlassian.net/browse/MSEARCH-961

Jiras

https://folio-org.atlassian.net/browse/MSEARCH-961

https://folio-org.atlassian.net/browse/MODEXPW-560

https://folio-org.atlassian.net/browse/MODFQMMGR-667

Test description

Test name

source

Number of records

Action

query-1

query-2

Test name

source

Number of records

Action

query-1

query-2

Scenario 1

FOLIO

100, 1000, 10 000, 100 000

  1. Add  Action note: "Regular data cleanup."

  2. Mark the instance as Staff suppress 

from .csv file

from .csv file

Scenario 2

FOLIO

2007 

  1. Add  Action note: "Regular data cleanup."

  2. Mark the instance as Staff suppress 

(instance.source == "FOLIO") AND (instance.format_names contains any (<format names supported in test environment>)) [computer -- online resource]

English (354)
(instance.source == "folio") AND (instance.languages contains all (""))

Scenario 3

MARC

100, 1000, 10 000, 100 000

  1. Add 500 field with both identifiers empty and subfield “a” with the text “From the papers of the Smith family.” and subfield “5” set to “DLC”

from .csv file

from .csv file

Scenario 4

MARC

1148 

  1. Add 500 field with both identifiers empty and subfield “a” with the text “From the papers of the Smith family.” and subfield “5” set to “DLC”

(instance.source == "MARC") AND (instance.format_names contains any (<format names supported in test environment>)) [unmediated -- object]

Albanian (1055)
(instance.source == "marc") AND (instance.languages contains all (""))

Scenario 5

FOLIO &

MARC

100, 1000, 10 000, 100 000

  1. Suppress from discovery including option for holdings and items

from .csv file

from .csv file

Scenario 6

FOLIO &

MARC

562

  1. Suppress from discovery including option for holdings and items

(instance.format_names contains any (<format names supported in test environment>)) [audio -- other]

Georgian (1294)
(instance.languages contains all (""))

Test Results

Test #

Source

file size

Upload Duration (hh:mm:ss)

Update Duration  (hh:mm:ss)

Total time
(hh:mm:ss)

Results

Test #

Source

file size

Upload Duration (hh:mm:ss)

Update Duration  (hh:mm:ss)

Total time
(hh:mm:ss)

Results

Scenario 1

FOLIO

100

00:00:12

00:00:28

00:00:40

Completed

FOLIO

1000

00:01:22

00:03:05

00:04:27

Completed

FOLIO

10 000

00:13:35

00:31:30

00:45:05

Completed

FOLIO

100 000

02:21:51

05:56:30

08:18:21

Completed

Scenario 2

FOLIO

2166 (query-1)

388 (query-2)

00:06:05

00:01:11

00:09:43

00:02:15

00:15:48

00:03:26

query-1 Completed
query-2 Completed

Scenario 3

MARC

100

00:00:17

00:00:24

00:00:41

Completed

MARC

1000

00:02:14

00:01:50

00:04:04

Completed

MARC

10 000

00:22:21

00:32:52

00:55:13

Completed

MARC

100 000

04:13:48

03:45:38

07:59:26

Completed

Scenario 4

MARC

1055 (query-2)

-

-

-

Test query-1 not finished in 2 hours
Test query-2 not finished in 1 hour
https://folio-org.atlassian.net/browse/MODFQMMGR-667

Scenario 5

FOLIO & MARC

100

00:00:14

00:00:27

00:00:41

Completed

FOLIO & MARC

1000

00:01:18

00:04:07

00:05:25

Completed

FOLIO & MARC

10 000

00:17:13

00:53:30

01:10:43

Completed

FOLIO & MARC

100 000

02:54:46

08:09:48

11:04:34

Completed

 

FOLIO & MARC

1294 (query-2)

-

-

-

Test query-1 not finished in 2 hours

Test query-2 not finished in 1 hour
https://folio-org.atlassian.net/browse/MODFQMMGR-667

 

Resource utilization

Instance CPU Utilization

Resource utilization was very similar for all of the tests. I took the most representative.

Scenario 1

scenario 5

Service CPU Utilization

Here we can see that the modules used 1.7% CPU

Scenario 1

All modules graph: We can see spikes in mod-bulk-operations and mod-source-record-storage due to manual service restart but average CPU Utilization for these 2 modules did not exceed 1%.

Scenario 5

Service Memory Utilization

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

Scenario 1

No constantly growing trend is observed.

Scenario 5

DB CPU Utilization

Scenario 1

DB CPU on average was up to 99.7% while updating 100k records because updating is the process that uses data import and is very consuming in database CPU resources.

Scenario 5

DB Connections

Scenario 1 (almost the same for all of the tests)

DB connections was up to 1149.

Scenario 5 (almost the same for all of the tests)

DB load

 These database queries were observed for all of the scenarios.                                                                                            

Top SQL-queries

 

 

#

TOP 5 SQL statements

#

TOP 5 SQL statements

1

WITH deleted_ids as ( DELETE FROM fs09000000_mod_search.instance_contributor WHERE instance_id IN ($1::uuid) RETURNING contributor_id ) UPDATE fs09000000_mod_search.contributor SET last_updated_date = CURRENT_TIMESTAMP WHERE id IN (SELECT * FROM deleted_ids)

2

WITH cte AS (SELECT id, name, name_type_id, authority_id, last_updated_date FROM fs09000000_mod_search.contributor WHERE last_updated_date > $1 ORDER BY last_updated_date ) SELECT c.id, c.name, c.name_type_id, c.authority_id, c.last_updated_date, json_agg( CASE WHEN sub.instance_count

3

call delete_old_marc_indexers_versions($1)

4

SELECT fs09000000_mod_inventory_storage.count_estimate(?)

SELECT fs09000000_mod_inventory_storage.count_estimate('SELECT jsonb,id FROM fs09000000_mod_inventory_storage.service_point WHERE ((true) AND ( (get_tsvector(f_unaccent(service_point.jsonb->>''ecsRequestRouting'')) @@ tsquery_phrase(f_unaccent(''true''))) IS NOT TRUE)) AND (id=''3a7de149-f17d-4602-adfe-0b09bf8e884a'')')

5

SELECT jsonb FROM fs09000000_mod_permissions.permissions

6

INSERT INTO fs09000000_mod_search.instance (id, tenant_id, shared, is_bound_with, json) VALUES ($1::uuid, $2, $3, $4, $5::jsonb) ON CONFLICT (id) DO UPDATE SET shared = EXCLUDED.shared, tenant_id = EXCLUDED.tenant_id, is_bound_with = EXCLUDED.is_bound_with, json = EXCLUDED.json

Other RDS metrics and observations

During updating records from time to time database deadlocks were observed 
Locking transactions:

SQL text for digest ID: -206532979961713955 WITH deleted_ids as ( DELETE FROM fs09000000_mod_search.instance_contributor WHERE instance_id IN ($1::uuid,$2::uuid) RETURNING contributor_id ) UPDATE fs09000000_mod_search.contributor SET last_updated_date = CURRENT_TIMESTAMP WHERE id IN (SELECT * FROM deleted_ids) --------------------------------------with---------------------------------------------- INSERT INTO fs09000000_mod_search.contributor (id, name, name_type_id, authority_id) VALUES ($1, $2, $3, $4),($5, $6, $7, $8) ON CONFLICT (id) DO UPDATE SET last_updated_date = CURRENT_TIMESTAMP

Appendix

Infrastructure

PTF - environment Ramsons (rcp1)

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

  • 1 database instance, writer

  • MSK fse-test

    • 4 m5.2xlarge brokers in 2 zones

    • Apache Kafka version 3.7.x

    • EBS storage volume per broker 300 GiB

    • auto.create.topics.enable=true

    • log.retention.minutes=480

    • default.replication.factor=2

 

Cluster Resources - rcp1-pvt (Thu Feb 06 08:00:39 UTC 2025)

 

 

 

 

 

 

 

 

 

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

12

/folio/mod-remote-storage:3.3.5

2

4920

4472

0

3960

512

512

mod-ncip

9

/folio/mod-ncip:1.15.6

2

1024

896

0

768

88

128

mod-finance-storage

10

/folio/mod-finance-storage:8.7.3

2

1024

896

0

700

88

128

mod-agreements

12

/folio/mod-agreements:7.1.5

2

1592

1488

0

0

0

0

mod-ebsconet

11

/folio/mod-ebsconet:2.3.1

2

1248

1024