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 PERF-1034 - Getting issue details... STATUS

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 MODFQMMGR-667 - Getting issue details... STATUS 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 MSEARCH-961 - Getting issue details... STATUS

Jiras

MSEARCH-961 - Getting issue details... STATUS

MODEXPW-560 - Getting issue details... STATUS

MODFQMMGR-667 - Getting issue details... STATUS

Test description

Test Results

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

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)

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 

call delete_old_marc_indexers_versions($1)

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'')')

SELECT jsonb FROM fs09000000_mod_permissions.permissions

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

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

• 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

Methodology/Approach

Test set

Data preparation: prepare . csv files with instance UUIDs for each of the test step 
The script to prepare files is here.
Populate the database with FOLIO records if needed with this script.

1.  Manually tested all of the scenarios started on one tenant only:
   1. Trigger bulk edit by submitting a list of identifiers for 100, 1000, 10 000, and 100 000 records or run a query
   2. Wait until Bulk edit finds all matched records and record the startTime and endTime of the process from the response.
   3. Edit instances and start the second part of the bulk edit to commit the changes.
   4. Record the start time of committing changes from Headers
2. Database preparation script after the test run required to restore Suppress from discovery and Staff suppress parameters.

Additional screenshots