Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-792

GET item-storage/items?query=barcode=="" is very slow - takes 58 seconds

    XMLWordPrintable

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

    • Standard Bug Write-Up Format
    • 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

            Activity

              People

                Unassigned Unassigned
                varunjavalkar Varun Javalkar
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases