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

Fix performance issue of DB measures migrations on Oracle and PostgreSQL

    Details

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

      Description

      Testing migration from 6.7 to 7.8 on a huge dataset, we have detected the following hotspots.

      Total duration of the migration: 22H

      • Migration #1907 PopulateLiveMeasures: 16.5H
      • Migration #1908 DeletePersonAndFileMeasures: 4H
      • Migration #2504 DeleteModuleAndFolderMeasures: 1H

      In addition to the performance issue, there is a functional problem with migration PopulateLiveMeasures: in case of failure during the migration (which is more likely to happen since the migration is very long), the migration will restart from scratch.

      We should:

      • try to optimize migrations to decrease duration
      • make PopulateLiveMeasures incremental to not restart from scratch in case of issue

      Ideas:

      1. Split DeletePersonAndFileMeasures in two and delete person measures before PopulateLiveMeasures to decrease size of the table and simplify the query (remove condition pm.person_id is null).
      2. Decrease the volume of data selected/transmitted by PopulateLiveMeasures by only returning measures ids in the SELECT, and using a SELECT in the INSERT step to copy measures data on DB side:
        INSERT INTO live_measures (uuid, component_uuid, project_uuid, metric_id, value, text_value, variation, measure_data, created_at, updated_at) SELECT ?, ?, ?, pm.metric_id, pm.value, pm.text_value, pm.variation_value_1, pm.measure_data, ?, ? FROM project_measures pm WHERE pm.id = ?
      3. Remove some indexes before doing the DELETE (and readd indexes only at the end)
      4. Merge #1908 and #2504

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sebastien.lesaint Sebastien Lesaint
                Reporter:
                julien.henry Julien Henry
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: