Uploaded image for project: 'Bamboo Data Center'
  1. Bamboo Data Center
  2. BAM-21705

Running expiry against plans with a lot of results can be very inefficient

XMLWordPrintable

      Issue Summary

      The SQL queries used by Bamboo to identify plans to be expired based on the settings defined at the expiry functionality are very inefficient in instances that contain plans with a huge number of results

      Steps to Reproduce

      1. Have a plan in Bamboo with a huge number of build results (i.e. 300.000)
      2. Run the expiry

      Expected Results

      The build results covered by the rules defined at the expiry functionality will be marked to be deleted in a reasonable amount of time.

      Actual Results

      Some of the queries used by Bamboo to select the plans to be expired can take a lot of time to run, in some scenarios even a couple of days.

      In one example, a plan with 303845 results, the query that is used only to get the build number of the oldest build result to keep during build expiry (excluding specs builds):

      SELECT MIN(A.BUILD_NUMBER)
FROM BUILDRESULTSUMMARY A
WHERE A.BUILD_KEY = '<plan-key>'
  AND A.SPECS_RESULT = FALSE
  AND (
    SELECT COUNT(B.BUILD_NUMBER)
    FROM BUILDRESULTSUMMARY B
    WHERE B.SPECS_RESULT = FALSE
      AND B.BUILD_NUMBER >= A.BUILD_NUMBER
      AND B.BUILD_KEY = '<plan-key>'
  ) <= <build results to keep, eg. 1>;

      Took more than 2,5 days to get executed:

      mysql> SELECT min(a.BUILD_NUMBER)
    -> FROM BUILDRESULTSUMMARY a
    -> WHERE a.BUILD_KEY = 'PROJ-PLAN'
    ->   AND a.SPECS_RESULT = 0
    ->   AND (
    ->     SELECT count(b.BUILD_NUMBER)
    ->     FROM BUILDRESULTSUMMARY b
    ->     WHERE b.SPECS_RESULT = 0
    ->       AND b.BUILD_NUMBER >= a.BUILD_NUMBER
    ->       AND b.BUILD_KEY = 'PROJ-PLAN'
    ->   ) <= 1;
+---------------------+
| min(a.BUILD_NUMBER) |
+---------------------+
|              303845 |
+---------------------+
1 row in set (2 days 12 hours 46 min 10.81 sec)

      This situation can escalate if the instance holds more than one plan with a high number of build results.

      Workaround

      Currently, there is no workaround for this.

            [BAM-21705] Running expiry against plans with a lot of results can be very inefficient

            Alexey Chystoprudov made changes -
            Resolution New: Fixed [ 1 ]
            Status Original: Waiting for Release [ 12075 ] New: Closed [ 6 ]
            Marcin Gardias made changes -
            Status Original: In Review [ 10051 ] New: Waiting for Release [ 12075 ]
            Marcin Gardias made changes -
            Status Original: In Progress [ 3 ] New: In Review [ 10051 ]
            Marcin Gardias made changes -
            Remote Link New: This issue links to "+core+ Dogfooding › Test Git Branch Detection › bugfix-BAM-21705-expiry-query (tardigrade-bamboo)" [ 634407 ]
            Marcin Gardias made changes -
            Status Original: Short Term Backlog [ 12074 ] New: In Progress [ 3 ]
            Marcin Gardias made changes -
            Fix Version/s New: 8.1.6 [ 100494 ]
            Fix Version/s New: 8.2.2 [ 100690 ]
            Fix Version/s New: 8.0.7 [ 99693 ]
            Marcin Gardias made changes -
            Assignee New: Marcin Gardias [ mgardias ]
            Marcin Gardias made changes -
            Status Original: Long Term Backlog [ 12073 ] New: Short Term Backlog [ 12074 ]
            Marcin Gardias made changes -
            Status Original: Needs Triage [ 10030 ] New: Long Term Backlog [ 12073 ]
            SET Analytics Bot made changes -
            Support reference count Original: 1 New: 2

              mgardias Marcin Gardias
              gribeiro Gabriel Ribeiro
              Affected customers:
              1 This affects my team
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: