Details
-
Bug
-
Status: Closed (View Workflow)
-
TBD
-
Resolution: Cannot Reproduce
-
None
-
None
-
-
Prokopovych
Description
Background:
Instance "Query Search" is making system unresponsive due to increase in RDS CPU Utilization
Steps to reproduce:
1. Go to https://bugfest-honeysuckle.folio.ebsco.com
2. Go to Inventory app
3. Select "Query Search" from the dropdown
4. Search for a non-common word - like "notes=Druse"
5. Notice that the API request never returns with a response
The following API Query is made:
https://okapi-bugfest-honeysuckle.folio.ebsco.com/inventory/instances?limit=100&query=%28notes%3DDruse%29%20sortby%20title
and this corresponding DB query that never completes:
WITH headrecords AS ( SELECT jsonb, (lower(f_unaccent(jsonb->>'title')) ) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE (get_tsvector(f_unaccent(instance.jsonb->>'notes')) @@ tsquery_phrase(f_unaccent('Druse'))) AND left(lower(f_unaccent(jsonb->>'title')) ,600) < ( SELECT left(lower(f_unaccent(jsonb->>'title')) ,600) FROM fs09000000_mod_inventory_storage.instance ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) OFFSET 10000 LIMIT 1 ) ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) LIMIT 100 OFFSET 0 ), allrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE (get_tsvector(f_unaccent(instance.jsonb->>'notes')) @@ tsquery_phrase(f_unaccent('Druse'))) AND (SELECT COUNT(*) FROM headrecords) < 100 ), totalCount AS (SELECT fs09000000_mod_inventory_storage.count_estimate(' SELECT lower(f_unaccent(jsonb->>''title'')) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE get_tsvector(f_unaccent(instance.jsonb->>''notes'')) @@ tsquery_phrase(f_unaccent(''Druse''))') AS count) SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION (SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM allrecords ORDER BY data_column LIMIT 100 OFFSET
There is 30% spike in RDS CPU Utilization for 1 query - so 3-4 queries conducted in parallel can technically make the app unresponsive and bring the system down. Graphs of ReadIOPS and CPU Utilization for 1 query attached below:
ReadIOPS
CPU Utilization:
Expected Results:
Query completes and results are returned in a timely fashion
Actual Results:
Query never completes getting the system hung
TestRail: Results
Attachments
Issue Links
- is blocked by
-
UXPROD-2806 Create Elasticsearch indexes for Inventory search
-
- Closed
-