Overview
- This document contains the results of testing of export deleted MARC authority records via API on the Quesnelia [ECS] release on qcon environment.
PERF-897
-
Getting issue details...
STATUS
Summary
- Export deleted MARC authority records tests finished successfully on qcon environment using 100K, 300K and 646K records.
- Comparing Test Duration and Response time between tests with 100K, 300K and 646K records
- Test Duration grew up according to grew records count.
- Response time for 100k and 300k records almost the same, but it grew up almost in two times compared 100k and 646k records.
- During testing, we noticed that mod-entities-links module used 53% CPU in spikes for 300k, but for 646k records used 39% CPU.
- No memory leaks are observed.
- After the first part of the tests, the database was cleaned up by removing deleted MARC authority records. This cleanup negatively affected the duration and response time for the 100k records in the repeated test.
Test Runs
| Test № | Scenario | Test Conditions | Results |
|---|
| 1 | Export deleted MARC authority records via API
| 100k deleted records | Completed |
| 2 | 300k deleted records | Completed |
| 3 | 646k deleted records | Completed |
Test Results
This table contains durations for export deleted MARC authority records and response time (Average) for GET authority-storage/authorities endpoint.
| Profile | Test Conditions | Test Duration sec | Response time Average sec |
|---|
GET_authority-storage/authorities
|
Export deleted MARC authority records via API | 100k | 13.317 | 0.261 |
| 300k | 29.109 | 0.288 |
| 646k | 90.045 | 0.514 |
Export deleted MARC authority records via API (repeated tests) | 100k | 25.892 | 0.512 |
| 300k | 36.173 | 0.358 |
Resource utilization for Test №1
Resource utilization table
| CPU | RAM |
|---|
| mod-entities-links-b | 17% | mod-inventory-b | 55% |
| mod-inventory-b | 10% | mod-data-import-b | 47% |
| mod-quick-marc-b | 6.60% | mod-circulation-item-b | 44% |
| mod-pubsub-b | 5.50% | mod-source-record-storage-b | 41% |
| mod-di-converter-storage-b | 2.10% | mod-source-record-manager-b | 40% |
| mod-feesfines-b | 2.00% | mod-entities-links-b | 37% |
| mod-configuration-b | 2.00% | okapi-b | 32% |
| mod-source-record-manager-b | 1.80% | mod-feesfines-b | 31% |
| pub-okapi | 1.60% | mod-quick-marc-b | 30% |
| mod-source-record-storage-b | 1.30% | mod-di-converter-storage-b | 29% |
| okapi-b | 1.20% | mod-patron-blocks-b | 29% |
| mod-data-import-b | 1.00% | mod-configuration-b | 28% |
| nginx-okapi | 1.00% | mod-users-bl-b | 26% |
| mod-remote-storage-b | 0.80% | mod-pubsub-b | 24% |
| mod-authtoken-b | 0.60% | mod-authtoken-b | 19% |
| mod-circulation-storage-b | 0.50% | mod-circulation-storage-b | 18% |
| mod-users-bl-b | 0.50% | mod-remote-storage-b | 16% |
| mod-circulation-b | 0.30% | mod-inventory-storage-b | 13% |
| mod-patron-blocks-b | 0.20% | nginx-okapi | 4.00% |
Service CPU Utilization
Here we can see that mod-entities-links used 18% CPU in spikes.
Service Memory Utilization
Here we can see that all modules show a stable trend.
DB CPU Utilization
DB CPU was 12%.
DB Connections
Max number of DB connections was 1355.
DB load
Top SQL-queries
| # | TOP 5 SQL statements |
|---|
| 1 | select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?
|
| 2 | select this_.fl_id as fl_id1_53_0_, this_.fl_family as fl_famil2_53_0_, this_.fl_name as fl_name3_53_0_, this_.fl_owner as fl_owner4_53_0_ from mod_serials_management__system.federation_lock this_
where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_
|
| 3 | select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,
aa1_0.sft_headings,aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1
offset $2 rows fetch first $3 rows only
|
| 4 | SELECT jsonb FROM cs00000int_mod_permissions.permissions
|
| 5 | SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = ? AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number()
OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, nullif(a.attidentity, ?) as attidentity,nullif(a.attgenerated, ?) as attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid)
AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) J
|
Resource utilization for Test №2
Resource utilization table
| CPU | RAM |
|---|
| mod-entities-links-b | 52% | mod-inventory-b | 55% |
| mod-inventory-b | 10% | mod-data-import-b | 47% |
| mod-quick-marc-b | 6.90% | mod-circulation-item-b | 44% |
| mod-pubsub-b | 5.50% | mod-source-record-storage-b | 41% |
| nginx-okapi | 5.30% | mod-source-record-manager-b | 40% |
| pub-okapi | 4.80% | mod-entities-links-b | 38% |
| mod-configuration-b | 3.10% | okapi-b | 32% |
| okapi-b | 2.10% | mod-feesfines-b | 31% |
| mod-feesfines-b | 2.10% | mod-quick-marc-b | 30% |
| mod-di-converter-storage-b | 2.00% | mod-di-converter-storage-b | 29% |
| mod-source-record-manager-b | 1.70% | mod-patron-blocks-b | 29% |
| mod-source-record-storage-b | 1.30% | mod-configuration-b | 28% |
| mod-data-import-b | 1.10% | mod-users-bl-b | 26% |
| mod-users-bl-b | 1.00% | mod-pubsub-b | 24% |
| mod-authtoken-b | 0.80% | mod-authtoken-b | 19% |
| mod-remote-storage-b | 0.70% | mod-circulation-storage-b | 18% |
| mod-circulation-storage-b | 0.60% | mod-remote-storage-b | 17% |
| mod-circulation-b | 0.30% | mod-inventory-storage-b | 13% |
| mod-patron-blocks-b | 0.20% | nginx-okapi | 4.00% |
Service CPU Utilization
Here we can see that mod-entities-links used 53% CPU in spikes.
Service Memory Utilization
Here we can see that all modules show a stable trend.
DB CPU Utilization
DB CPU was 23%.
DB Connections
Max number of DB connections was 1350.
DB load
Top SQL-queries
| # | TOP SQL statements |
|---|
| 1 | select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,aa1_0.sft_headings,
aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1 offset $2 rows fetch first $3 rows only
|
| 2 | select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?
|
| 3 | SELECT cs00000int_0004_mod_circulation_storage.count_estimate(?)
|
| 4 | select this_.fl_id as fl_id1_44_0_, this_.fl_family as fl_famil2_44_0_, this_.fl_name as fl_name3_44_0_, this_.fl_owner as fl_owner4_44_0_ from mod_agreements__system.federation_lock this_ where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_
|
Resource utilization for Test №3
Resource utilization table
| CPU | RAM |
|---|
| mod-entities-links-b | 38% | mod-data-import-b | 49% |
| mod-inventory-b | 14% | mod-circulation-item-b | 44% |
| mod-quick-marc-b | 8.60% | mod-source-record-storage-b | 42% |
| mod-pubsub-b | 5.50% | mod-source-record-manager-b | 41% |
| nginx-okapi | 3.80% | mod-entities-links-b | 38% |
| pub-okapi | 3.30% | mod-inventory-b | 37% |
| mod-di-converter-storage-b | 2.10% | okapi-b | 32% |
| mod-feesfines-b | 2.10% | mod-di-converter-storage-b | 32% |
| mod-configuration-b | 1.80% | mod-feesfines-b | 31% |
| mod-source-record-manager-b | 1.70% | mod-quick-marc-b | 31% |
| okapi-b | 1.50% | mod-patron-blocks-b | 29% |
| mod-source-record-storage-b | 1.30% | mod-configuration-b | 28% |
| mod-data-import-b | 1.20% | mod-users-bl-b | 26% |
| mod-remote-storage-b | 0.80% | mod-inventory-storage-b | 25% |
| mod-authtoken-b | 0.60% | mod-pubsub-b | 24% |
| mod-circulation-storage-b | 0.50% | mod-authtoken-b | 19% |
| mod-users-bl-b | 0.50% | mod-circulation-storage-b | 18.00% |
| mod-circulation-b | 0.30% | mod-remote-storage-b | 17.00% |
| mod-patron-blocks-b | 0.30% | nginx-okapi | 4% |
Service CPU Utilization
Here we can see that mod-entities-links used 39% CPU in spikes.
Service Memory Utilization
Here we can see that all modules show a stable trend.
DB CPU Utilization
DB CPU was 43%.
DB Connections
Max number of DB connections was 1355.
DB load
Top SQL-queries
| # | TOP 5 SQL statements |
|---|
| 1 | select aa1_0.id,aa1_0.source_file_id,aa1_0.created_by_user_id,aa1_0.created_date,aa1_0.deleted,aa1_0.heading,aa1_0.heading_type,aa1_0.identifiers,aa1_0.natural_id,aa1_0.notes,aa1_0.saft_headings,
aa1_0.sft_headings,aa1_0.source,aa1_0.subject_heading_code,aa1_0.updated_by_user_id,aa1_0.updated_date,aa1_0._version from authority_archive aa1_0 where aa1_0.deleted=$1 and 1=1
offset $2 rows fetch first $3 rows only
|
| 2 | WITH cte AS (SELECT count(*) AS total_count FROM cs00000int_0001_mod_source_record_manager.job_execution WHERE subordination_type <> ? AND ? AND status IN (?, ?, ?) AND job_profile_id NOT IN (?, ?)
AND file_name NOT IN (?) AND subordination_type NOT IN (?) AND NOT job_profile_hidden AND NOT is_deleted) SELECT j.*, cte.*, p.total_records_count total,
p.succeeded_records_count + p.error_records_count currently_processed, (select jsonb_agg(x) composite_data from (select status, count(?) cnt, sum(p
|
| 3 | select count(aa1_0.id) from authority_archive aa1_0 where aa1_0.deleted=$1 and ?=?
|
| 4 | WITH cte AS (SELECT count(*) AS total_count FROM cs00000int_0001_mod_source_record_manager.job_execution WHERE subordination_type <> ? AND ? AND status <> ? AND ui_status IN (?, ?, ?)
AND subordination_type NOT IN (?) AND NOT job_profile_hidden AND NOT is_deleted) SELECT j.*, cte.*, p.total_records_count total, p.succeeded_records_count + p.error_records_count currently_processed,
(select jsonb_agg(x) composite_data from (select status, count(?) cnt, sum(p1.total_records_count) total_records_co
|
| 5 | select this_.fl_id as fl_id1_53_0_, this_.fl_family as fl_famil2_53_0_, this_.fl_name as fl_name3_53_0_, this_.fl_owner as fl_owner4_53_0_ from mod_serials_management__system.federation_lock this_
where this_.fl_name=$1 and this_.fl_family=$2 limit $3 for update of this_
|
Appendix
Infrastructure
PTF - environment Quesnelia (qcon)
Quesnelia modules memory and CPU parameters
Methodology/Approach
Export deleted MARC authority records were started from CarrierIO (QCON_ECS_Export_Deleted_Records) on a qcon Quesnelia [ECS] environment..
Test runs:
- Test 1: Tested export 100k deleted MARC authority records, started on Central Office tenant(cs00000int) with one user.
- Test 2: Tested export 300k deleted MARC authority records, started on Central Office tenant(cs00000int) with one user.
- Test 3: Tested export 646k deleted MARC authority records, started on Central Office tenant(cs00000int) with one user.
- After tests all deleted MARC authority records were removed from Database and added again, then repeat tests with 100k and 300k MARC authority records on Central Office tenant(cs00000int) with one user.
At the time of the test run, Grafana was not available. As a result, response times for Export deleted MARC authority records were taken from a .jtl files. These results were visualized in JMeter using a Listener (Response Times Over Time).