Uploaded image for project: 'mod-source-record-storage'
  1. mod-source-record-storage
  2. MODSOURCE-298

New Trigger, process_marc_records_lb_delete_trigger, Is Painfully Slow



    • 3
    • Spitfire


      See comments from Jenn below

      The trigger process_marc_records_lb_delete_trigger was added to the table marc_records_lb in mod-source-record-storage v5.1.0-SNAPSHOT makes it extremely slow to delete records in the records_lb table. (records_lb is related to marc_records_lb by ways of foreign key.) Deleting 25K records from records_lb takes over 12 minutes with this trigger enabled, but after disabling this trigger, deleting 75K records from records_lb only took 6.1 seconds. This trigger deletes records from marc_indexers and marc_indexers_leader row by row by matching up the IDs, which makes it painfully slow. Could these two tables be hooked up to marc_records_lb by foreign keys and take advantage of CASCADE Delete to have the records deleted without calling the delete statement explicitly on each row?

      Steps to Reproduce:
      Delete any number of records (25K or even 1K) in marc_records_lb of mod-source-record-storage v5.1.0-SNAPSHOT with a time constraint, ex:

      delete FROM fs08000000_mod_source_record_storage.records_lb where created_date > '2021-05-16 22:00:00.00+00'

      It will take minutes to delete

      Expected Results:
      Delete operation would take seconds
      Actual Results:
      Delete operation takes more than 10 minutes!

      Interested parties:
      abreaux OleksiiKuzminov ruslan_lavrov


      Implementation steps

      1. This story requires the preparation in a database to be implemented first MODSOURCE-304  
      2. The approach is to implement scheduled deletion from marc_indexers & marc_indexers_leaders. The scheduler will monitor marc_indexers & marc_indexers_leaders and clean up the records marked deleted = 'true'. The time settings for the scheduler for deletion should be clarified with the PO

      TestRail: Results


          Issue Links



                Igor_Gorchakov Igor Gorchakov
                mtraneis Martin Tran
                0 Vote for this issue
                7 Start watching this issue



                  TestRail: Runs

                    TestRail: Cases