...
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 |
|---|
|
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
|
...
MARC Search Query API MARC Search Query API
Long SQL query analysis
| Expand |
|---|
| title | SQL 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 |
|---|
| title | Explain 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]
...
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 |
|---|
| server | System Jira |
|---|
| serverId | 01505d01-b853-3c2e-90f1-ee9b165564fc |
|---|
| key | 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
...