Problem

When opening certain build results summary pages, Bamboo's UI is slow to respond or unresponsive, and Bamboo's memory usage spikes

Diagnosis

Diagnostic Steps

• A large number of test cases per build plan, in the tens or hundreds of thousands, can cause this issue to occur

• To count the number of test cases per build plan, run the following query in your database: 

  SELECT b.full_key AS plan_key, COUNT(*) AS test_cases
             FROM BUILD b
  LEFT OUTER JOIN TEST_CLASS cl ON b.build_id = cl.plan_id 
  LEFT OUTER JOIN TEST_CASE ON cl.TEST_CLASS_ID = TEST_CASE.TEST_CLASS_ID
         GROUP BY b.build_id, b.buildkey, b.full_key
         ORDER BY COUNT(*) DESC

Cause

When viewing a build results summary, all test cases for that plan are loaded into memory. If too many records exist, memory resourcing issues can occur and performance issues may result. More information can be found in the following bug report: BAM-17202 - A lot of memory is used when open details of plan with huge amount of tests.

Resolution

Orphaned test case records can be safely deleted from your database. The steps to do this are described below.

Steps for MySQL

1.  Shut down Bamboo and backup the database, e.g. using the mysqldump tool

   You MUST backup your database before continuing!

2. From the command line, run:

   mysqlcheck -u $YOUR_DB_USER -p --analyze $YOUR_DB_NAME

3. Open a mysql session and run the following queries:

   SET AUTOCOMMIT=1;
   
   CREATE TEMPORARY TABLE MIN_BUILD_NUMBER (
       build_id bigint(20) NOT NULL, 
       min_build_number int(11) DEFAULT NULL, 
       PRIMARY KEY (build_id)
   ) AS SELECT b.build_id, min(build_number) AS min_build_number 
          FROM BUILDRESULTSUMMARY brs, BUILD b 
         WHERE b.build_type = 'JOB' AND b.full_key = brs.build_key GROUP BY b.build_id;
   
   
   DELETE FROM TEST_ERROR 
    WHERE result_id IN (SELECT test_case_result_id 
                          FROM TEST_CASE_RESULT tcr, TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                         WHERE tcr.test_case_id=tca.test_case_id 
                           AND tcl.plan_id=b.build_id 
                           AND tcl.test_class_id=tca.test_class_id 
                           AND mbn.build_id=b.build_id 
                           AND tca.last_build_num<mbn.min_build_number
                       );
   
   DELETE FROM TEST_CASE_RESULT 
    WHERE test_case_id in (SELECT test_case_id 
                             FROM TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                            WHERE tcl.plan_id=b.build_id 
                              AND tcl.test_class_id=tca.test_class_id 
                              AND mbn.build_id=b.build_id 
                              AND tca.last_build_num<mbn.min_build_number
                          );

4. In the same session, run the following query repeatedly until it no longer deletes rows (each run removes 100k rows):

   DELETE FROM TEST_CASE 
    WHERE test_case_id IN (SELECT test_case_id 
                             FROM (SELECT test_case_id 
                                     FROM TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                                    WHERE tcl.plan_id=b.build_id 
                                      AND tcl.test_class_id=tca.test_class_id 
                                      AND mbn.build_id=b.build_id 
                                      AND tca.last_build_num<mbn.min_build_number limit 100000
                                  ) AS tcis 
                          );

5. Finally, exit the mysql session and run the following from the command line:

   mysqlcheck -u $YOUR_DB_USER -p --optimize $YOUR_DB_NAME

6. Once this is complete, restart Bamboo

Steps for PostgreSQL

1.  Shut down Bamboo and backup the database, e.g. using the pg_dump tool

   You MUST backup your database before continuing!

2. Open a psql session and run the following queries:

   CREATE TEMPORARY TABLE MIN_BUILD_NUMBER AS 
       SELECT b.build_id, min(build_number) min_build_number 
         FROM BUILDRESULTSUMMARY brs, BUILD b 
        WHERE b.build_type = 'JOB' 
          AND b.full_key = brs.build_key GROUP BY b.build_id;
   
   
   DELETE FROM TEST_ERROR 
    WHERE result_id IN (SELECT test_case_result_id 
                          FROM TEST_CASE_RESULT tcr, TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                         WHERE tcr.test_case_id=tca.test_case_id 
                           AND tcl.plan_id=b.build_id 
                           AND tcl.test_class_id=tca.test_class_id 
                           AND mbn.build_id=b.build_id 
                           AND tca.last_build_num<mbn.min_build_number
                       );
   
   
   DELETE FROM TEST_CASE_RESULT 
    WHERE test_case_id in (SELECT test_case_id 
                             FROM TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                            WHERE tcl.plan_id=b.build_id 
                              AND tcl.test_class_id=tca.test_class_id 
                              AND mbn.build_id=b.build_id 
                              AND tca.last_build_num<mbn.min_build_number
                          );

3. In the same session, run the following query repeatedly until it no longer deletes rows (each run removes 100k rows):

   DELETE FROM TEST_CASE 
    WHERE test_case_id IN (SELECT test_case_id 
                             FROM (SELECT test_case_id 
                                     FROM TEST_CASE tca, TEST_CLASS tcl, BUILD b, MIN_BUILD_NUMBER mbn 
                                    WHERE tcl.plan_id=b.build_id 
                                      AND tcl.test_class_id=tca.test_class_id 
                                      AND mbn.build_id=b.build_id 
                                      AND tca.last_build_num<mbn.min_build_number limit 100000
                                  ) AS tcis 
                          );

4. Once this is complete, restart Bamboo