Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-14681

Improve database upgrade speed for migration 3208

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 8.2
    • Fix Version/s: 8.9
    • Component/s: Database
    • Edition:
      Community
    • Production Notes:
      None

      Description

      SonarQube Migration 8.2 step #3208 "Remove old Security Review Rating measures" is slow because not using indexes to delete the metrics.

      The solution is to split this migration into two parts, with distinct responsibility:

      • one to delete the metrics from live_measures
      • one to delete the metrics from project_measures

       

      Live measure deletion improvement

      For live_measures, we rework the query to benefit from the existing composite index on component_uuid + metric_id

      Old query:

      select c.uuid from components c where c.scope in ('PRJ')
      for each result:
      delete from live_measures where project_uuid = ? and metric_id = ?

      New query:

      select lm.uuid from live_measures lm inner join components c on lm.component_uuid = c.uuid and lm.metric_id = ?
      for each result:
      delete from live_measures where uuid = ?

       

      Project Measure deletion improvement

      For project_measures, we are adding a dedicated technical index for this migration on metric_uuid, moving the purge step to 8.4 (metric_id is migrated to metric_uuid in SQ 8.4, so with an index on metric_uuid instead of metric_id, we can keep it for use later on)

        Attachments

          Activity

            People

            Assignee:
            pierre.guillot Pierre Guillot
            Reporter:
            pierre.guillot Pierre Guillot
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Due:
              Created:
              Updated:
              Resolved: