PTF- MARC search

Overview

This document contains the results of performance testing for searching SRS records based on the LDR05 value on the OCP3 environment for POST /source-storage/stream/marc-record-identifiers request with the following parameters https://folio-org.atlassian.net/browse/PERF-703 :

Summary

  • The average time to respond to HTTP requests used in Test 1 and Test 2 for returning 66 records is about 6,4 seconds. The average time to respond to requests used in Test 3 for returning 20,000 records is about 6,9 seconds. The average time to respond to requests used in Test 4 for filtering using data range is about 6,7 seconds;

  • The high response time from the server to the HTTP request is caused by the duration of the SQL query to the database, which is about 6 seconds for all types of queries, ticket for optimization is created;

  • The memory utilization didn`t increase during Test 1- Test 4;

  • The maximal CPU utilization increases up to 3.5% on mod-source-record-storage;

  • RDS CPU utilization was about 80% during Test 1- Test 4, number on DB connection ~ 400;

Recommendations & Jiras

Test Runs 

Test 1. Request method POST, path source-storage/stream/marc-record-identifiers , body data

{"leaderSearchExpression": "p_05 = 'd'", } this request returns 66 records.

Test 2. Request method POST, path source-storage/stream/marc-record-identifiers , body data

{"leaderSearchExpression": "p_05 = 'd'", "deleted": true, "suppressFromDiscovery": true,} this request returns 66 records, generated by DELETE /source-storage/records/${id}.

Test 3. Request method POST, path source-storage/stream/marc-record-identifiers , body data

{"leaderSearchExpression": "p_05 = 'd'", "deleted": true, "suppressFromDiscovery": true,} this request returns 20,000 records, generated by DELETE /source-storage/records/${id}.

Test 4. Request method POST, path source-storage/stream/marc-record-identifiers , body data

{"leaderSearchExpression": "p_05 = 'd'", "deleted": true, "suppressFromDiscovery": true, "fieldsSearchExpression": "005.date in '20141106-20231108'"} this request returns 574 records.

Results

The table contains the main testing metrics. The duration of all of the tests was about 30 minutes. All test finished successfully.

NAME

TOTAL REQ, COUNT

MIN (sec)

MEDIAN (sec)

PCT95 (sec)

SQL Query Execution Time (sec)

Test 1

450

6,1

6,4

6,9

6,2

Test 2

450

6

6,4

6,9

6,2

Test 3

450

6,5

6,9

7,4

6,6

Test 4

450

6,3

6,7

7,2

6,58

 

Resource Utilizations

Services CPU Utilizations 

Test 1 Started at 12:15 and finished at about 12:45, the main services that were involved in request processing: mod-authtoken, mod-source-record-storage, okapi-b, nginx-okapi. Maximum CPU utilization was about 2.5%

Test 2 Started at 11:30 and finished at about 12:00, Test 3 Started at 13:50 and finished at about 14:20,
Test 4 Started at 15:50 and finished at about 15:20

Maximum CPU utilization was about 3.5% for mod-source-record-storage

image-20240308-162659.png

Services Memory Utilizations

Test 1. During testing, the memory usage does not increase

Test 2 - Test 3 - Test 4. During testing, the memory usage does not increase

Test 2 - Test 3 - Test 4. The memory usage for main CPU Utilization services.

 

RDS CPU Utilization 

During Test 1 RDS CPU usage increased up to 75%;

The maximum RDS CPU usage during Test 2, Test 3, and Test 4 was up to 80% ;

RDS Database Connection

During Test 1- Test 4 the average number of database connections was about 400.

Database Load

As we can see from the graphs Database load sliced by Waits(Green columns) and sliced by SQL(light green), the most consuming is the SQL query given in the Full SQL query section. The average duration of these SQL queries is about 6,5 seconds for 4 tests. The detailed analysis of this query is in the additional section.

Database load during Test 1 .

select "alias_94280021"."external_id", "alias_100265999".count from (select distinct "records_lb"."external_id" from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and "records_lb"."state" = 'ACTUAL' and "records_lb"."suppress_discovery" = false and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_94280021" right outer join (select count(distinct "records_lb"."external_id") from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and "records_lb"."state" = 'ACTUAL' and "records_lb"."suppress_discovery" = false and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_100265999" on true

 

Database load during Test 2

 

select "alias_121109780"."external_id", "alias_44493210".count from (select distinct "records_lb"."external_id" from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_121109780" right outer join (select count(distinct "records_lb"."external_id") from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_44493210" on true

 

Database load during Test 3

select "alias_121109780"."external_id", "alias_44493210".count from (select distinct "records_lb"."external_id" from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_121109780" right outer join (select count(distinct "records_lb"."external_id") from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" where ((p_05 = 'd') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_44493210" on true

 

Database load during Test 4

select "alias_5398203"."external_id", "alias_100273909".count from (select distinct "records_lb"."external_id" from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" join "marc_indexers_005" "i005" on "records_lb"."id" = "i005"."marc_id" join "marc_records_tracking" "mrt_005" on ("i005"."marc_id" = "mrt_005"."marc_id" and "i005"."version" = "mrt_005"."version") where ((p_05 = 'd') and (to_date(substring("i005"."value", 1, 8), 'yyyymmdd') between '20141106' and '20231108') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_5398203" right outer join (select count(distinct "records_lb"."external_id") from "records_lb" join "marc_indexers_leader" on "records_lb"."id" = "marc_indexers_leader"."marc_id" join "marc_indexers_005" "i005" on "records_lb"."id" = "i005"."marc_id" join "marc_records_tracking" "mrt_005" on ("i005"."marc_id" = "mrt_005"."marc_id" and "i005"."version" = "mrt_005"."version") where ((p_05 = 'd') and (to_date(substring("i005"."value", 1, 8), 'yyyymmdd') between '20141106' and '20231108') and ("records_lb"."state" = 'ACTUAL' or "records_lb"."state" = 'DELETED' or ("records_lb"."state" = 'ACTUAL' and "records_lb"."leader_record_status" in ('d', 's', 'x'))) and "records_lb"."suppress_discovery" = true and "records_lb"."record_type" = 'MARC_BIB' and "records_lb"."external_id" is not null)) "alias_100273909" on true

 

Additional information


Long SQL query analysis

select "alias_94280021"."external_id", "alias_100265999".count
from (select distinct fs09000000_mod_source_record_storage.records_lb.external_id
from fs09000000_mod_source_record_storage.records_lb join fs09000000_mod_source_record_storage.marc_indexers_leader on fs09000000_mod_source_record_storage.records_lb.id = fs09000000_mod_source_record_storage.marc_indexers_leader.marc_id
where ((p_05 = 'd')
and fs09000000_mod_source_record_storage.records_lb.state = 'ACTUAL'
and fs09000000_mod_source_record_storage.records_lb.suppress_discovery = false
and fs09000000_mod_source_record_storage.records_lb.record_type = 'MARC_BIB'
and fs09000000_mod_source_record_storage.records_lb.external_id is not null)) "alias_94280021"
right outer join (select count(distinct fs09000000_mod_source_record_storage.records_lb.external_id)
from fs09000000_mod_source_record_storage.records_lb join fs09000000_mod_source_record_storage.marc_indexers_leader on fs09000000_mod_source_record_storage.records_lb.id = fs09000000_mod_source_record_storage.marc_indexers_leader.marc_id
where ((p_05 = 'd')
and state = 'ACTUAL'
and suppress_discovery = false
and record_type = 'MARC_BIB'
and external_id is not null)) "alias_100265999" on true

 

 

Line 5 shows that the duration of the p_05 filtering is ~ 4 seconds because it goes through all 29 million records in the table.

 

Appendix

Infrastructure

PTF -environment ocp3

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

  • 1 instance writer of db.r6.xlarge database instances, [db.r6g.xlarge, vCPU 4 RAM 32 GB]

Number of database records

Data for testing were prepared in scope https://folio-org.atlassian.net/browse/PERF-726
fs09000000_mod_inventory_storage.instance =22779941

fs09000000_mod_source_record_storage.records_lb = 29369636

fs09000000_mod_source_record_storage.marc_indexers = 2587420890

 

  • MSK tenant [ kafka configurations]
    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

mod-source-record-storage: Kafka consolidated topic = enabled; Partitions = 50.

 

 

Modules memory and CPU parameters

Module

Task Def. Revision

Module Version

Task Count

Mem Hard Limit

Mem Soft limit

CPU units

Xmx

MetaspaceSize

MaxMetaspaceSize

R/W split enabled

pcp1-pvt

mod-authtoken