Details
-
Bug
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Duplicate
-
None
-
None
-
Release: Juniper-hotfix-1.5
mod-inventory-storage:21.0.3
Postgresql RDS instance type: 4 vCPU 32 GB RAM
instance - 7.2 million
item - 8.93 million
holdings_record - 7.7 million
-
Folijet Support
-
R3 2021 Bug Fix
Description
Overview:
GET item-storage/items?query=barcode=="" is used multiple times in Data Import workflow and we have seen significant slowness because of this query. Why is Data Import making a query for empty barcode?
Steps to Reproduce:
- Log into https://pre-kiwi-cap1.int.aws.folio.org/ as folio user
- Open Data Import App
- Run Create job profile job with 100 records. See attached mrc file(kcp1-DE-100-7492.mrc) for reference.
- Job completes successfully but it takes 30+ minutes
While the job is running, the request is made to mod-inventory-storage, item-storage API as below which consumes 100% RDS Postgresql CPU
Request
curl --request GET 'https://okapi-pre-kiwi-cap1.int.aws.folio.org/item-storage/items?query=barcode==""' \ --header 'x-okapi-tenant: fs09000000' \ --header 'x-okapi-token: ${OKAPITOKEN}' \ --header 'Content-Type: application/json'
Response
Status code 200 57.21 seconds
Expected Results:
Should return response in less than 1 second
Actual Results:
taking 58 seconds
Additional Information:
In the database following query is run:
SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE lower(fs09000000_mod_inventory_storage.f_unaccent(item.jsonb->>'barcode')) LIKE lower(fs09000000_mod_inventory_storage.f_unaccent(''))
Explain Analyze of a simpler version of above query:
EXPLAIN ANALYZE SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE item.jsonb->>'barcode'=''; Query Plan: "Gather (cost=1000.00..7050169.75 rows=44489 width=782) (actual time=80977.741..80981.200 rows=0 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on item (cost=0.00..7044720.85 rows=18537 width=782) (actual time=80973.762..80973.762 rows=0 loops=3)" " Filter: ((jsonb ->> 'barcode'::text) = ''::text)" " Rows Removed by Filter: 2979217" "Planning Time: 0.121 ms" "Execution Time: 80981.225 ms"
Before running Explain Analyze, we vaccumed table
vacuum fs09000000_mod_inventory_storage.item
In mod-inventory-storage log, we see lot of calls to get empty barcode. See attached mod-inventory-storage mod-inventory-storage-item-storage.log
13:58:49 [] [] [] [] INFO CQLWrapper CQL >>> SQL: barcode=="" >>>WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('')) LIMIT 10 OFFSET 0
URL:
Interested parties:
All consumers of mod-inventory-storage
CC abreaux
TestRail: Results
Attachments
Issue Links
- defines
-
UXPROD-3191 NFR: R3 2021 Kiwi Data import performance work
-
- Closed
-
- relates to
-
MODINV-508 Block sending a requests with an empty barcode - KIWI
-
- Closed
-
-
-
- Closed
-
-
MODINVSTOR-523 Item barcode should be unique
-
- Closed
-