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

Slow queries when migrating from Daisy to Edelweiss

    XMLWordPrintable

Details

    • Core: F - Sprint 83, Core: F - Sprint 84, Core: F - Sprint 85
    • 5
    • Prokopovych

    Description

      When attempting to migrate a tenant from daisy to edelweiss we experienced very slow queries (some individual queries took 2+ hours to complete) and as a result we could not successfully complete the migration.

      We extended load balancer timeouts to the maximum possible 4000secs. Issuing a POST install request times out

      POST <okapi>/_/proxy/tenants/<tenant>/install
      

      The tenant had a large number of records in Inventory - with the following number of records:
      holdings – 7,678,725
      instance – 7,641,915
      item — 9,031,146

      As reference – attached is a log of the slow queries that we experienced – with the timings of these queries

      The top 12 queries with poor performance are as follows:

      UPDATE fs00001006_mod_inventory_storage.item AS it SET	jsonb = JSONB_SET(it.jsonb, 			'{effectiveLocationId}', 			COALESCE(it.jsonb->'temporaryLocationId', it.jsonb->'permanentLocationId', 			hr.jsonb->'temporaryLocationId', hr.jsonb->'permanentLocationId') ) FROM fs00001006_mod_inventory_storage.holdings_record AS hr WHERE hr.id = it.holdingsrecordid;
      UPDATE 9031146
      Time: 8283804.996 ms
      UPDATE fs00001006_mod_inventory_storage.item AS it SET jsonb = jsonb_set(   it.jsonb,   '{effectiveCallNumberComponents}',   jsonb_build_object(     'callNumber', COALESCE(it.jsonb->'itemLevelCallNumber', hr.jsonb->'callNumber'),     'prefix', COALESCE(it.jsonb->'itemLevelCallNumberPrefix', hr.jsonb->'callNumberPrefix'),     'suffix', COALESCE(it.jsonb->'itemLevelCallNumberSuffix', hr.jsonb->'callNumberSuffix')   ) ) FROM fs00001006_mod_inventory_storage.holdings_record AS hr WHERE hr.id = it.holdingsrecordid;
      UPDATE 9031146
      Time: 8183348.877 ms
      UPDATE fs00001006_mod_inventory_storage.item SET effectiveLocationId = (jsonb->>'effectiveLocationId')::uuid;
      UPDATE 9031146
      Time: 5198585.185 ms
      CREATE INDEX IF NOT EXISTS instance_keyword_idx_ft ON fs00001006_mod_inventory_storage.instance USING GIN         ( to_tsvector('simple', f_unaccent(concat_space_sql(instance.jsonb->>'title' , concat_array_object_values(instance.jsonb->'contributors','name') , concat_array_object_values(instance.jsonb->'identifiers','value')))) );
      Time: 974018.135 ms
      CREATE INDEX IF NOT EXISTS instance_identifiers_idx_ft ON fs00001006_mod_inventory_storage.instance USING GIN         ( to_tsvector('simple', f_unaccent(jsonb->>'identifiers')) );
      Time: 725451.992 ms
      CREATE INDEX IF NOT EXISTS instance_contributors_idx_gin ON fs00001006_mod_inventory_storage.instance USING GIN         ((lower(f_unaccent(jsonb->>'contributors'))) gin_trgm_ops)     ;
      Time: 722843.693 ms
      CREATE INDEX IF NOT EXISTS instance_identifiers_idx_gin ON fs00001006_mod_inventory_storage.instance USING GIN         ((lower(f_unaccent(jsonb->>'identifiers'))) gin_trgm_ops)     ;
      Time: 718281.157 ms
      CREATE INDEX IF NOT EXISTS item_barcode_idx_ft ON fs00001006_mod_inventory_storage.item USING GIN         ( to_tsvector('simple', f_unaccent(jsonb->>'barcode')) );
      Time: 621271.185 ms
      CREATE INDEX IF NOT EXISTS item_barcode_idx ON fs00001006_mod_inventory_storage.item     (left(lower(f_unaccent(jsonb->>'barcode')),600))      ;
      Time: 618795.231 ms
      CREATE INDEX IF NOT EXISTS item_status_name_idx_gin ON fs00001006_mod_inventory_storage.item USING GIN         ((lower(f_unaccent(jsonb->'status'->>'name'))) gin_trgm_ops)     ;
      Time: 594673.711 ms
      CREATE INDEX IF NOT EXISTS instance_contributors_idx_ft ON fs00001006_mod_inventory_storage.instance USING GIN         ( to_tsvector('simple', f_unaccent(jsonb->>'contributors')) );
      Time: 567407.793 ms
      CREATE INDEX IF NOT EXISTS instance_title_idx_gin ON fs00001006_mod_inventory_storage.instance USING GIN         ((lower(f_unaccent(jsonb->>'title'))) gin_trgm_ops)     ;
      Time: 416479.812 ms
      
      

      For reference the db instance was – db.r5.2xlarge

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                kday Kevin Day
                cgodfrey Carole Godfrey
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases