Versions Compared

Old Version 16

changes.mady.by.user Mykhailo Petryshyn

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.

...

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

4500

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

...

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

...

Database Load

Database load during Test 1

...

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

...


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

...

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]

...

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=480

    • default.replication.factor=3

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

Modules memory and CPU parameters

...