Details
-
Story
-
Status: Closed (View Workflow)
-
P3
-
Resolution: Done
-
None
-
-
Core: F - Sprint 92, Core: F - Sprint 93
-
3
-
Prokopovych
-
Q2 2020 Hot Fix #1
Description
Overview
This was observed in bugfest. Logs were having a lot of noise like below:
WARNING: Doing FT search without index for holdings_record.jsonb->>'discoverySuppress', CQL >>> SQL: holdingsRecords.discoverySuppress = true >>> to_tsvector('simple', f_unaccent(holdings_record.jsonb->>'discoverySuppress')) @@ replace((to_tsquery('simple', f_unaccent('''true''')))::text, '&', '<->')::tsquery
WARNING: Doing FT search without index for instance.jsonb->>'staffSuppress', CQL >>> SQL: staffSuppress = true >>> to_tsvector('simple', f_unaccent(instance.jsonb->>'staffSuppress')) @@ replace((to_tsquery('simple', f_unaccent('''true''')))::text, '&', '<->')::tsquery
WARNING: Doing LIKE search without index for location.jsonb->>'primaryServicePoint', CQL >>> SQL: primaryServicePoint == 7068e104-aa14-4f30-a8bf-71f71cc15e07 >>> lower(f_unaccent(location.jsonb->>'primaryServicePoint')) LIKE lower(f_unaccent('7068e104-aa14-4f30-a8bf-71f71cc15e07'))
WARNING: Doing FT search without index for item.jsonb->'status'->>'name', CQL >>> SQL: item.status.name = Available >>> to_tsvector('simple', f_unaccent(item.jsonb->'status'->>'name')) @@ replace((to_tsquery('simple', f_unaccent('''Available''')))::text, '&', '<->')::tsquery
WARNING: Doing LIKE search without index for item.jsonb->>'purchaseOrderLineIdentifier', CQL >>> SQL: purchaseOrderLineIdentifier == 76c0ddee-cd91-4a0e-9295-fec513e47252 >>> lower(f_unaccent(item.jsonb->>'purchaseOrderLineIdentifier')) LIKE lower(f_unaccent('76c0ddee-cd91-4a0e-9295-fec513e47252'))
WARNING: Doing FT search without index for holdings_record.jsonb->>'hrid', CQL >>> SQL: holdingsRecords.hrid = ho1757203 >>> to_tsvector('simple', f_unaccent(holdings_record.jsonb->>'hrid')) @@ replace((to_tsquery('simple', f_unaccent('''ho1757203''')))::text, '&', '<->')::tsquery
WARNING: Doing LIKE search without index for service_point.jsonb->>'pickupLocation', CQL >>> SQL: pickupLocation == true >>> lower(f_unaccent(service_point.jsonb->>'pickupLocation')) LIKE lower(f_unaccent('true'))
The logs for mod-inventory-storage are huge, therefore they can't be attached here. Please contact Zmiivska for logs if need be.
Acceptance Criteria
- the indexes are added
- the warnings are disappeared
Description from UIIN-1062 - Bugfest: Inventory. Instance, holdings, item segment. Query search - poor response time (about a minute):
Overview: Query search in all three segments are very slow (poor performance) in Bugfest environment, compared to uChicago's environment (Edelweiss). The same search do also sometimes result in: 1000 records found - which is the new 999,999,999 records found.
Steps to reproduce:
- Login to the bugfest environment, go to inventory
- Select the Instance segment, and search option Query search
- When searching: publication = "MIT Press" - Search (https://bugfest.folio.ebsco.com/inventory?qindex=querySearch&query=publication%20%3D%20%22MIT%20Press%22&sort=Title)
Expected result:
I get my search result up quickly - in about > 1 sec (https://wiki.folio.org/pages/viewpage.action?pageId=25729156)
Actual result:
First time I tried it took about 60 sec. And I got: 7.080 records found.
Second time I tried it took less than 10 sec. And I got (only): 1000 records found
Some more Query search tests:
| Segment | Query search | Time log Stop watch |
Results found |
|---|---|---|---|
| Instance | subjects = "history" | 00:05 sec | 35,813 records found |
| Instance | series = "cooperative information systems" | 00:43 sec | 7 records found |
| Holdings | holdingsRecords.callNumber = "HD1697.R9F79" | 00:33 sec | 1 record found |
| Holdings | holdingsRecords.holdingsStatements = "v.57:no.1-3" | 00:16 sec | 24 records found |
| Item | item.enumeration = "v.71" | 00:08 sec | 44,604 records found |
| Item | item.formerIds = "hzn64519" | 00:37 sec | 1 record found |
Other observations:
Query search fails when search using parentheses, e.g. in the holdingsRecords.holdingsStatements = ".57:no.1-3(2014:Jul-2015:Jan) - then following error message pop up:
https://bugfest.folio.ebsco.com/inventory/view/5917b896-7ca1-4f69-8b60-f4a9a9d2ddf0?qindex=querySearch&query=holdingsRecords.holdingsStatements%20%20%3D%20%22.57%3Ano.1-3%282014%3AJul-2015%3AJan%29&segment=holdings
I'll investigate this some more, and file another bug ticket for this.
TestRail: Results
Attachments
Issue Links
- has to be done before
-
MODINVSTOR-537
Release mod-inventory-storage 19.2.5
-
- Closed
-
- is blocked by
-
PERF-4
Investigate Bugfest Q1 FF Performance Issues
-
- Closed
-
-
PERF-20
Bugfest: performance issues investigation (4/2/20, 4/3/20, 4/6/20)
-
- Closed
-
- relates to
-
UIIN-1062 Bugfest: Inventory. Instance, holdings, item segment. Query search - poor response time (about a minute)
-
- Closed
-
-
FOLIO-2573 Paused due to POC of Elastic Search. All Search Options Should be Supported by a Database Index
-
- Closed
-