/
PTF - Performance testing of moving Parsed Records Flattening From Database To Module Space (Quesnelia) [non-ECS]

PTF - Performance testing of moving Parsed Records Flattening From Database To Module Space (Quesnelia) [non-ECS]

Jul 03, 2024

    Overview

    • This document contains comparing results of testing  Data Import for MARC Bibliographic records with create and update jobs on the Quesnelia [non-ECS] release on qcp1 environment with two different versions of  mod-source-record-storage 5.8.0and5.9.0-SNAPSHOT.387 (moved json parsing function from DB to module). 

    https://folio-org.atlassian.net/browse/PERF-924 

    Summary

    • Data Import tests finished successfully on qcp1 environment using the PTF - Create 2 and PTF - Updates Success - 6 profiles with 10k, 100k and 200k file records.

    • DI duration growth correlates to the number of records imported.

    • Comparing between  mod-source-record-storage 5.8.0 and mod-source-record-storage 5.9.0-SNAPSHOT.387

      • Data Import durations have performance degradation of around 35% for all Data-imports jobs.

      • Server and Database metrics at the same level for both versions of mod-source-record-storage module.

    • No memory leaks are observed.

    Test Runs and Results

    This table contains durations for Data Import. 

    Profile

    Tenant

    MARC File

    Test #

    DI Duration
    mod-source-record-storage
    5.8.0
    (hh:mm:ss)

    Test #

    DI Duration
    mod-source-record-storage
    5.9.0-SNAPSHOT.387
    (hh:mm:ss)

    Results

    Profile

    Tenant

    MARC File

    Test #

    DI Duration
    mod-source-record-storage
    5.8.0
    (hh:mm:ss)

    Test #

    DI Duration
    mod-source-record-storage
    5.9.0-SNAPSHOT.387
    (hh:mm:ss)

    Results

    DI MARC Bib Create (PTF - Create 2)

    fs09000000

    25K.mrc

    1

    0:11:14

    7

    0:14:30

    Completed

    fs09000000

    100K.mrc

    2

    0:46:02

    8

    1:03:37

    Completed

    fs09000000

    200K.mrc

    3

    1:30:05

    9

    2:10:52

    Completed

    DI MARC Bib Update (PTF - Updates Success - 6)

    fs09000000

    25K.mrc

    4

    0:37:11

    10

    0:26:37

    Completed

    fs09000000

    100K.mrc

    5

    1:21:57

    11

    1:51:49

    Completed

    fs09000000

    200K.mrc

    6

    2:43:16

    12

    3:38:51

    Completed

     

    Comparison

    This table contains durations comparison between  mod-source-record-storage 5.8.0 and mod-source-record-storage 5.9.0-SNAPSHOT.387.

    Profile

    MARC File

    DI Duration
    mod-source-record-storage
    5.8.0
    (hh:mm:ss)

    DI Duration
    mod-source-record-storage
    5.9.0-SNAPSHOT.387
    (hh:mm:ss)

    Delta
    (hh:mm:ss / %)

    Profile

    MARC File

    DI Duration
    mod-source-record-storage
    5.8.0
    (hh:mm:ss)

    DI Duration
    mod-source-record-storage
    5.9.0-SNAPSHOT.387
    (hh:mm:ss)

    Delta
    (hh:mm:ss / %)

    DI MARC Bib Create (PTF - Create 2)

    25K.mrc

    0:11:14

    0:14:30

    0:03:1
    +29%

    100K.mrc

    0:46:02

    1:03:37

    0:17:35
    +38%

    200K.mrc

    1:30:05

    2:10:52

    0:40:47
    +45%

    DI MARC Bib Update (PTF - Updates Success - 6)

    25K.mrc

    0:37:11

    0:26:37

    0:10:34
    −28%

    100K.mrc

    1:21:57

    1:51:49

    0:29:52
    +36%

    200K.mrc

    2:43:16

    3:38:51

    0:55:35
    +34%

     

     

     

    Resource utilization for Test Set №1

    CPU RAM mod-inventory-b 124% mod-inventory-b 91% mod-quick-marc-b 92% mod-source-record-manager-b 71% mod-di-converter-storage-b 78% mod-circulation-item-b 58% nginx-okapi 50% mod-source-record-storage-b 56% mod-data-import-b 34% mod-di-converter-storage-b 53% mod-source-record-storage-b 27% okapi-b 44% okapi-b 24% mod-data-import-b 44% mod-source-record-manager-b 20% mod-patron-blocks-b 44% mod-inventory-storage-b 18% mod-feesfines-b 41% mod-pubsub-b 7% mod-configuration-b 41% mod-remote-storage-b 3% mod-pubsub-b 34% mod-configuration-b 2% mod-quick-marc-b 33% mod-circulation-item-b 2% mod-users-bl-b 32% mod-authtoken-b 2% mod-authtoken-b 29% mod-feesfines-b 2% mod-circulation-storage-b 24% mod-circulation-storage-b 2% mod-inventory-storage-b 23% mod-users-bl-b 0.60% mod-remote-storage-b 21% mod-patron-blocks-b 0.30% nginx-okapi 5%

    Service CPU Utilization

    Here we can see that mod-data-import used 250% CPU in spike and mod-inventory used 115% CPU.

    Service Memory Utilization

    Here we can see that all modules show a stable trend except mod-source-record-manager.

    DB CPU Utilization

    DB CPU was 90%.

    CPU (User) usage by broker

    Disk usage by broker

     

    DB Connections

    Max number of DB connections was 918.

    DB load

    Top SQL-queries

     

    #

    TOP 5 SQL statements

    #

    TOP 5 SQL statements

    1

    insert into "marc_records_lb" ("id", "content") values (cast($1 as uuid), cast($2 as jsonb)) on conflict ("id") do update set "content" = cast($3 as jsonb)

    2

    INSERT INTO fs09000000_mod_source_record_manager.events_processed (handler_id, event_id) VALUES ($1, $2)

    3

    INSERT INTO fs09000000_mod_source_record_manager.journal_records (id, job_execution_id, source_id, source_record_order, entity_type, entity_id, entity_hrid, action_type, action_status, error,
    action_date, title, instance_id, holdings_id, order_id, permanent_location_id, tenant_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)

    4

    INSERT INTO fs09000000_mod_inventory_storage.instance (id, jsonb) VALUES ($1, $2) RETURNING jsonb

    5

    INSERT INTO fs09000000_mod_inventory_storage.item (id, jsonb) VALUES ($1, $2) RETURNING jsonb

    Resource utilization for Test Set №2

    CPU RAM mod-inventory-b 111% mod-inventory-b 92% mod-quick-marc-b 88% mod-source-record-manager-b 72% mod-di-converter-storage-b 60% mod-circulation-item-b 60% nginx-okapi 41% okapi-b 56% mod-source-record-storage-b 21% mod-di-converter-storage-b 54% okapi-b 19% mod-source-record-storage-b 48% mod-inventory-storage-b 15% mod-data-import-b 46% mod-source-record-manager-b 14% mod-patron-blocks-b 44% mod-pubsub-b 7% mod-feesfines-b 42% mod-remote-storage-b 3.20% mod-configuration-b 42% mod-feesfines-b 2.30% mod-pubsub-b 36% mod-configuration-b 2.20% mod-users-bl-b 35% mod-circulation-item-b 2.20% mod-quick-marc-b 34% mod-circulation-storage-b 1.60% mod-authtoken-b 31% mod-data-import-b 1.50% mod-inventory-storage-b 26% mod-authtoken-b 1.30% mod-circulation-storage-b 24% mod-users-bl-b 0.50% mod-remote-storage-b 21% mod-patron-blocks-b 0.30% nginx-okapi 5%

    Service CPU Utilization

    Here we can see that mod-data-import used 240% CPU in spike and mod-inventory used 111% CPU.

    Service Memory Utilization

    Here we can see that all modules show a stable trend except mod-source-record-manager.

    CPU (User) usage by broker

    Disk usage by broker

    DB CPU Utilization

    DB CPU was 95%.

    DB Connections

    Max number of DB connections was 942.

    DB load

    Top SQL-queries

     

    #

    TOP 5 SQL statements

    #

    TOP 5 SQL statements

    1

    INSERT INTO fs09000000_mod_source_record_manager.events_processed (handler_id, event_id) VALUES ($1, $2)

    2

    INSERT INTO fs09000000_mod_source_record_manager.journal_records (id, job_execution_id, source_id, source_record_order, entity_type, entity_id, entity_hrid, action_type, action_status, error, action_date,
    title, instance_id, holdings_id, order_id, permanent_location_id, tenant_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)

    3

    WITH deleted_rows AS ( delete from marc_indexers mi where exists( select 1 from marc_records_tracking mrt where mrt.is_dirty = true and mrt.marc_id = mi.marc_id
    and mrt.version > mi.version ) returning mi.marc_id), deleted_rows2 AS ( delete from marc_indexers mi where exists( select 1 from records_lb
    where records_lb.id = mi.marc_id and records_lb.state = 'OLD' ) returning mi.marc_id) INSERT INTO marc_indexers_deleted_ids
    SELECT DISTINCT marc_id FROM deleted_rows UNION SELECT marc_id FROM deleted_rows2

    4

    INSERT INTO fs09000000_mod_inventory_storage.item (id, jsonb) VALUES ($1, $2) RETURNING jsonb

    5

    INSERT INTO fs09000000_mod_inventory_storage.instance (id, jsonb) VALUES ($1, $2) RETURNING jsonb

    Appendix

    Infrastructure

    PTF -environment qcp1

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

    • 1 database  instance, writer

      Name

      Memory GIB

      vCPUs

      max_connections

      db.r6g.xlarge

      32 GiB

      4 vCPUs

      2731

    • Number of records in DB:

      •  fs09000000

        • instances - 25901331

        • items - 27074913

        • holdings - 25871735

    • Open Search ptf-test

      • Data nodes

        • Instance type - r6g.2xlarge.search

        • Number of nodes - 4

        • Version: OpenSearch_2_7_R20240502

      • Dedicated master nodes

        • Instance type - r6g.large.search

        • Number of nodes - 3

    • MSK tenant

      • 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

     

    Module Version qcp1-pvt Revision Task Count Mem Hard Limit Mem Soft Limit CPU Xmx MetaspaceSize MaxMetaspaceSize mod-users-bl:7.7.0 5 2 1440 1152 512 922 88 128 mod-configuration:5.10.0 5 2 1024 896 128 768 88 128 mod-authtoken:2.15.1 6 2 1440 1152 512 922 88 128 mod-data-import:3.1.0 8 1 2048 1844 256 1292 384 512 mod-remote-storage:3.2.0 5 2 4920 4472 1024 3960 512 512 mod-inventory-storage:27.1.0 5 2 4096 3690 2048 3076 384 512 pub-okapi:2023.06.14 3 2 1024 896 128 768 Not found Not found mod-feesfines:19.1.0 5 2 1024 896 128 768 88 128 okapi:5.3.0 5 3 1684 1440 1024 922 384 512 nginx-okapi:2023.06.14 3 2 1024 896 128 Not found Not found Not found mod-quick-marc:5.1.0 5 1 2288 2176 128 1664 384 512 mod-source-record-manager:3.9.0-SNAPSHOT.330 6 2 5600 5000 2048 3500 384 512 mod-patron-blocks:1.10.0 5 2 1024 896 1024 768 88 128 mod-pubsub:2.13.0 5 2 1536 1440 1024 922 384 512 mod-circulation:24.2.0 5 2 2880 2592 1536 1814 384 512 mod-di-converter-storage:2.2.0 5 2 1024 896 128 768 88 128 mod-inventory:20.2.0 5 2 2880 2592 1024 1814 384 512 mod-source-record-storage:5.8.0 5 2 5600 5000 2048 3500 384 512 mod-circulation-storage:17.2.0 5 2 2880 2592 1536 1814 384 512

     

    Methodology/Approach

    DI tests scenario (DI MARC Bib Create\Update) were started from UI.

    Test set №1:

    • Test 1: Manually tested 25k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    • Test 2: Manually tested 100k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    • Test 3: Manually tested 200k records files DI MARC Bib Create started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    • Test 4: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    • Test 5: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    • Test 6: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.8.0.

    Test set №2:

    • Test 7: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    • Test 8: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    • Test 9: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    • Test 10: Manually tested 25k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    • Test 11: Manually tested 100k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    • Test 12: Manually tested 200k records files DI MARC Bib Update started on one tenant (fs09000000) with version of module mod-source-record-storage:5.9.0-SNAPSHOT.387.

    To get status and time range for import jobs the query used: 

    SQL Query
    select file_name, job_Profile_name, started_date,completed_date, completed_date - started_date as duration ,status from fs09000000_mod_source_record_manager.job_execution order by started_date desc limit 2000;