Uploaded image for project: 'Opencast'
  1. MH-13347

deleteOrphanedProperties() is slow and causes deadlocks and timeouts

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed and reviewed
    • Affects versions: 6.1
    • Fix versions: 6.3
    • Components: Backend Software
    • Labels:
      None
    • Severity:
      Performance
    • Steps to reproduce:
      Hide
      When you delete an event, the asset manager method deleteOrphanedProperties() is called, which runs the query:

      DELETE p FROM oc_assets_properties p LEFT JOIN oc_assets_snapshot e ON p.mediapackage_id = e.mediapackage_id WHERE e.id IS NULL;

      This is slow because it does almost a full table scan on oc_assets_properties, which can have millions of rows.

      This doesn't seem necessary, as the properties should get deleted for a specific mediapackage when that MP is deleted.

      At best, it's a maintenance task that could get run once a day.
      Show
      When you delete an event, the asset manager method deleteOrphanedProperties() is called, which runs the query: DELETE p FROM oc_assets_properties p LEFT JOIN oc_assets_snapshot e ON p.mediapackage_id = e.mediapackage_id WHERE e.id IS NULL; This is slow because it does almost a full table scan on oc_assets_properties, which can have millions of rows. This doesn't seem necessary, as the properties should get deleted for a specific mediapackage when that MP is deleted. At best, it's a maintenance task that could get run once a day.

      TestRail: Results

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                smarquard Stephen Marquard
              • Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  TestRail: Cases