Versions Compared

Old Version 11

changes.mady.by.user Martin Tran

Saved on

compared with

New Version Current

changes.mady.by.user Mykhailo Petryshyn

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Jira Legacy
serverSystem Jira
serverId01505d01-b853-3c2e-90f1-ee9b165564fc
keyPERF-703
:

Summary

  • The average time to respond to HTTP requests used in Test 1

...

{"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}.

...

  • 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'", "deleted": true, "suppressFromDiscovery": true, "fieldsSearchExpression": "005.date in '20141106-20231108'" } this request returns 574 records.

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

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

TTL TOTAL REQ, COUNTERRORS

MIN (sec)

MEDIAN (sec)

PCT95 (sec)

SQL Query Execution Time (sec)

Test 1

450

0

6,1

6,4

6,9

6,2

Test 2

450

0

6

6,4

6,9

6,2

Test 3

450

0

6,5

6,9

7,4

6,6

Test 4

4500

6,3

6,7

7,2

6,58

Resource Utilizations

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.

...

Services CPU Utilizations 

...

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

...

3.5% for mod-source-record-storage

...

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.

...

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 .

...

Expand
titleFull SQL query

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

...

Expand

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

Expand
titleSQL query that take about 6 second

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

Expand
titleExplain analysts results

"Nested Loop Left Join (cost=1020229.38..1020323.73 rows=3773 width=24) (actual time=5230.369..5231.050 rows=66 loops=1)" " -> Aggregate (cost=510007.34..510007.35 rows=1 width=8) (actual time=4192.762..4192.857 rows=1 loops=1)" " -> Gather (cost=1000.56..509997.90 rows=3773 width=16) (actual time=24.231..4192.687 rows=66 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Nested Loop (cost=0.56..508620.60 rows=1572 width=16) (actual time=151.580..4056.045 rows=22 loops=3)" " -> Parallel Seq Scan on marc_indexers_leader (cost=0.00..490961.97 rows=2074 width=16) (actual time=145.937..3841.444 rows=57 loops=3)" " Filter: ((p_05)::text = 'd'::text)" " Rows Removed by Filter: 9773039" " -> Index Scan using records_lb_pkey on records_lb (cost=0.56..8.51 rows=1 width=32) (actual time=3.739..3.739 rows=0 loops=172)" " Index Cond: (id = marc_indexers_leader.marc_id)" " Filter: ((NOT suppress_discovery) AND (external_id IS NOT NULL) AND (state = 'ACTUAL'::fs09000000_mod_source_record_storage.record_state) AND (record_type = 'MARC_BIB'::fs09000000_mod_source_record_storage.record_type))" " Rows Removed by Filter: 1" " -> Unique (cost=510222.05..510240.91 rows=3773 width=16) (actual time=1037.603..1038.181 rows=66 loops=1)" " -> Sort (cost=510222.05..510231.48 rows=3773 width=16) (actual time=1037.602..1038.168 rows=66 loops=1)" " Sort Key: records_lb_1.external_id" " Sort Method: quicksort Memory: 28kB" " -> Gather (cost=1000.56..509997.90 rows=3773 width=16) (actual time=1.604..1038.113 rows=66 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Nested Loop (cost=0.56..508620.60 rows=1572 width=16) (actual time=10.387..1033.099 rows=22 loops=3)" " -> Parallel Seq Scan on marc_indexers_leader marc_indexers_leader_1 (cost=0.00..490961.97 rows=2074 width=16) (actual time=10.348..1032.530 rows=57 loops=3)" " Filter: ((p_05)::text = 'd'::text)" " Rows Removed by Filter: 9773039" " -> Index Scan using records_lb_pkey on records_lb records_lb_1 (cost=0.56..8.51 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=172)" " Index Cond: (id = marc_indexers_leader_1.marc_id)" " Filter: ((NOT suppress_discovery) AND (external_id IS NOT NULL) AND (state = 'ACTUAL'::fs09000000_mod_source_record_storage.record_state) AND (record_type = 'MARC_BIB'::fs09000000_mod_source_record_storage.record_type))" " Rows Removed by Filter: 1" "Planning Time: 61.232 ms" "Execution Time: 5231.126 ms"

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]

...

NULL) AND (state = 'ACTUAL'::fs09000000_mod_source_record_storage.record_state) AND (record_type = 'MARC_BIB'::fs09000000_mod_source_record_storage.record_type))" " Rows Removed by Filter: 1" "Planning Time: 61.232 ms" "Execution Time: 5231.126 ms"

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

Jira Legacy
serverSystem Jira
serverId01505d01-b853-3c2e-90f1-ee9b165564fc
keyPERF-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=480default.replication.factor=3.retention.minutes=480

    • default.replication.factor=3

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

Modules memory and CPU parameters

...

Thread properties: 2 Virtual users with a Ramp-Up period of 1 second were running 225 loops.

Request parameters for:

Test 1.

Code Block
POST /source-storage/stream/marc-record-identifiers
{
"leaderSearchExpression": "p_05 = 'd'"
}

Test 2 and Test 3.

Code Block
POST /source-storage/stream/marc-record-identifiers
{
"leaderSearchExpression": "p_05 = 'd'"
"suppressFromDiscovery": true,
"deleted": true
}

Test 4

Code Block
POST /source-storage/stream/marc-record-identifiers
{
"leaderSearchExpression": "p_05 = 'd'",
"fieldsSearchExpression": "005.date in '20141106-20231108'",
"suppressFromDiscovery": true,
"deleted": true
}