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

The page "measures" can be very slow on H2 when a single project has been analyzed

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 6.3
    • Component/s: Database, Measures
    • Labels:

      Description

      On a fresh H2 database, the page "measures" is super slow (a few dozens seconds) when a single project is analyzed (regular size, for instance github.com/sonarsource/sonar-java).

      The reason is that H2 does correctly use the index "measures_analysis_metric" on columns ["ANALYSIS_UUID", "METRIC_ID"] of table project_measures when loading measures.

      -- simplified request (union dropped), but with same perf hotspot 
      SELECT pm.id
      FROM project_measures pm 
      INNER JOIN projects p
      INNER JOIN projects base
      INNER JOIN snapshots analysis
      WHERE
        base.project_uuid = p.project_uuid
        AND base.uuid ='AVll7U06RmK0ANNqfo2d'
        AND p.uuid_path LIKE '.AVll7U06RmK0ANNqfo2d.%' ESCAPE '/'
        AND p.project_uuid = analysis.component_uuid
        AND p.enabled = TRUE
        AND p.qualifier IN( 'FIL', 'UTS')
        AND analysis.islast = TRUE
        AND p.uuid = pm.component_uuid
        AND pm.analysis_uuid = analysis.uuid 
        AND pm.person_id IS NULL
        -- PROBLEM IS HERE
        AND pm.metric_id IN (3, 114, 126)
      

      Explain plan is not correct :

      SELECT
          PM.ID
      FROM PUBLIC.PROJECTS P
          /* PUBLIC.PROJECTS_QUALIFIER: QUALIFIER IN('FIL', 'UTS') */
          /* WHERE ((P.QUALIFIER IN('FIL', 'UTS'))
              AND (P.ENABLED = TRUE))
              AND (P.UUID_PATH LIKE '.AVll7U06RmK0ANNqfo2d.%' ESCAPE '/')
          */
          /* scanCount: 1481 */
      INNER JOIN PUBLIC.SNAPSHOTS ANALYSIS
          /* PUBLIC.SNAPSHOT_COMPONENT: COMPONENT_UUID = P.PROJECT_UUID */
          ON 1=1
          /* WHERE (ANALYSIS.ISLAST = TRUE)
              AND (P.PROJECT_UUID = ANALYSIS.COMPONENT_UUID)
          */
          /* scanCount: 2960 */
      INNER JOIN PUBLIC.PROJECT_MEASURES PM
          /* PUBLIC.MEASURES_ANALYSIS_METRIC: METRIC_ID IN(3, 114, 126)
              AND ANALYSIS_UUID = ANALYSIS.UUID
           */
          ON 1=1
          /* WHERE (P.UUID = PM.COMPONENT_UUID)
              AND (((PM.METRIC_ID IN(3, 114, 126))
              AND (PM.PERSON_ID IS NULL))
              AND (PM.ANALYSIS_UUID = ANALYSIS.UUID))
          */
          -- PROBLEM IS HERE
          /* scanCount: 39295480 */
      INNER JOIN PUBLIC.PROJECTS BASE
          /* PUBLIC.PROJECTS_UUID: UUID = 'AVll7U06RmK0ANNqfo2d' */
          ON 1=1
          /* scanCount: 1718 */
      WHERE (PM.PERSON_ID IS NULL)
          AND ((PM.METRIC_ID IN(3, 114, 126))
          AND ((PM.ANALYSIS_UUID = ANALYSIS.UUID)
          AND ((P.UUID = PM.COMPONENT_UUID)
          AND ((ANALYSIS.ISLAST = TRUE)
          AND ((P.QUALIFIER IN('FIL', 'UTS'))
          AND ((P.ENABLED = TRUE)
          AND ((P.PROJECT_UUID = ANALYSIS.COMPONENT_UUID)
          AND ((P.UUID_PATH LIKE '.AVll7U06RmK0ANNqfo2d.%' ESCAPE '/')
          AND ((BASE.UUID = 'AVll7U06RmK0ANNqfo2d')
          AND (BASE.PROJECT_UUID = P.PROJECT_UUID))))))))))
      /*
      total: 1507
      PROJECTS.PROJECTS_DATA read: 1488 (98%)
      PROJECTS.PROJECTS_QUALIFIER read: 19 (1%)
      */
      

      When more data are injected (analysis of new project), then explain plan is correct and execution is instantaneous:

      SELECT
          PM.ID
      FROM PUBLIC.PROJECT_MEASURES PM
          /* PUBLIC.MEASURES_PERSON: PERSON_ID IS NULL */
          /* WHERE (PM.METRIC_ID IN(3, 114, 126))
              AND (PM.PERSON_ID IS NULL)
          */
          /* scanCount: 54763 */
      INNER JOIN PUBLIC.PROJECTS P
          /* PUBLIC.PROJECTS_UUID: UUID = PM.COMPONENT_UUID */
          ON 1=1
          /* WHERE (P.UUID_PATH LIKE '.AVll7U06RmK0ANNqfo2d.%' ESCAPE '/')
              AND ((P.ENABLED = TRUE)
              AND ((P.QUALIFIER IN('FIL', 'UTS'))
              AND (P.UUID = PM.COMPONENT_UUID)))
          */
          /* scanCount: 4878 */
      INNER JOIN PUBLIC.SNAPSHOTS ANALYSIS
          /* PUBLIC.ANALYSES_UUID: UUID = PM.ANALYSIS_UUID */
          ON 1=1
          /* WHERE (P.PROJECT_UUID = ANALYSIS.COMPONENT_UUID)
              AND ((ANALYSIS.ISLAST = TRUE)
              AND (PM.ANALYSIS_UUID = ANALYSIS.UUID))
          */
          /* scanCount: 1718 */
      INNER JOIN PUBLIC.PROJECTS BASE
          /* PUBLIC.PROJECTS_UUID: UUID = 'AVll7U06RmK0ANNqfo2d' */
          ON 1=1
          /* scanCount: 1718 */
      WHERE (PM.PERSON_ID IS NULL)
          AND ((PM.METRIC_ID IN(3, 114, 126))
          AND ((PM.ANALYSIS_UUID = ANALYSIS.UUID)
          AND ((P.UUID = PM.COMPONENT_UUID)
          AND ((ANALYSIS.ISLAST = TRUE)
          AND ((P.QUALIFIER IN('FIL', 'UTS'))
          AND ((P.ENABLED = TRUE)
          AND ((P.PROJECT_UUID = ANALYSIS.COMPONENT_UUID)
          AND ((P.UUID_PATH LIKE '.AVll7U06RmK0ANNqfo2d.%' ESCAPE '/')
          AND ((BASE.UUID = 'AVll7U06RmK0ANNqfo2d')
          AND (BASE.PROJECT_UUID = P.PROJECT_UUID))))))))))
      /*
      total: 5261
      PROJECTS.PROJECTS_DATA read: 1728 (32%)
      PROJECTS.PROJECTS_UUID read: 48 (0%)
      PROJECT_MEASURES.MEASURES_PERSON read: 213 (4%)
      PROJECT_MEASURES.PROJECT_MEASURES_DATA read: 3235 (61%)
      overflow read: 37 (0%)
      */
      

        Attachments

          Activity

            People

            • Assignee:
              sebastien.lesaint Sebastien Lesaint
              Reporter:
              simon.brandhof Simon Brandhof
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Due:
                Created:
                Updated:
                Resolved: