Table of Contents

Overview

Test goal is to assess performance of circulation check-in functionality for items with 10 TLR (title-level requests) each. Difference from the previous testing is added indexes to mod_circulation_storage.request and mod_circulation_storage.actual_cost_record in scope of

Previous test report: [Nolana] Check-IN + title-level requests

Ticket:

...

• Load tests showed that there is still significant degradation in performance of check-in for items with 10 TLRs each and without it. Also, response time increased after indexes were added. - see Response Time Comparison.
• Resource monitoring showed that:
  • CPU consumption by several services increased significantly - see  Service CPU Utilization.

  • RDS CPU Utilization increased from 15% to 72% - see RDS CPU Utilization

  • Database load increased  two times - see Database Load

• Query plan analysis for top SQL-queries showed that indexes were not used in queries processing.

...

As added indexes didn't change query plan of most CPU-consuming queries,

...

...

During verification tests two SQL queries moved to the beginning of Top SQL list:

SELECT fs09000000[tenant]_mod_circulation_storage.count_estimate(?)

SELECT jsonb,id FROM fs09000000[tenant]_mod_circulation_storage.request WHERE ((lower(f_unaccent(request.jsonb->>?)) LIKE lower(f_unaccent(?))) AND ((((CASE WHEN length(lower(?)) <= ? THEN left(lower(request.jsonb->>?),?) LIKE lower(?) ELSE left(lower(request.jsonb->>?),?) LIKE left(lower(?),?) AND lower(request.jsonb->>?) LIKE lower(?) END) OR (CASE WHEN length(lower(?)) <= ? THEN left(lower(request.jsonb->>?),?) LIKE lower(?) ELSE left(lower(request.jsonb->>?),?) LIKE left(lower(?),?) AND lower(reques

...

Environment didn't change since previous testing, but two indexes were added:  requestrequest_instanceid_idx on fs09000000on [tenant]_mod_circulation_storage.request and ctualand ctual_cost_record_expirationdate_idx on fs09000000on [tenant]_mod_circulation_storage.actual_cost_record.

...