Summary

The purpose of this page to help identify the reasons for large size of below tables in Bamboo and how to reduce the data in it. This page will not talk about any manual queries to delete the data from these tables. Below are the tables which we'll cover.

• TEST_CASE
• TEST_CASE_RESULT
• TEST_CLASS
• TEST_CLASS_RESULT
• TEST_ERROR

Environment

The solution and the DB queries mentioned on this page are applicable for all supported version of Bamboo and has been tested in PostgreSQL and might require changes for other DB types.

Diagnosis

The first step is to identify the large tables which are occupying the space in DB file system by running the below query

SELECT SCHEMANAME
       AS
       table_schema,
       RELNAME
       AS table_name,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(RELID))
       AS total_size,
       PG_SIZE_PRETTY(PG_RELATION_SIZE(RELID))
       AS data_size,
       PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(RELID) - PG_RELATION_SIZE(RELID))
       AS
       external_size
FROM   PG_CATALOG.PG_STATIO_USER_TABLES
ORDER  BY PG_TOTAL_RELATION_SIZE(RELID) DESC,
          PG_RELATION_SIZE(RELID) DESC
LIMIT  20; 

The result would look something like below, here we can see tables like test_case, test_error, test_case_result occupying good amount of space on the DB. 

The below diagram explains about the relationship between these tables

Cause

The data is populated in these tables when a build is run which contains test_cases to be executed. One of the probable reason why these table will grow is size is because of large number of test cases produced as part of builds results of a plan. As part of Bamboo Global expiry build results are deleted, but the test cases of these build results are not immediately deleted.

These test cases become orphaned and later these are removed as part of test cases scheduled cleanup jobs which runs on 10th and 25th of every month in it's scheduled setting, refer below

From Bamboo 9.6 the above schedule has been changed to run every Sunday instead of 10th and 25th of every month. 

Solution

In the below section, we'll list few solution and you can choose the one which works out for you the best.

Solution 1

As explained above the scheduled cleanup jobs which clean up the tables mentioned above runs on every 10th and 25th of the month, if these tables needs to be immediately cleaned up these we can modify the default CRON expression to make these run more frequently. 

This can be done by override the Bamboo bamboo.orphaned.test.case.cron System property, add is as a system argument in JVM_SUPPORT_RECOMMENDED_ARGS in setEnv.sh/setEnv.bat file

JVM_SUPPORT_RECOMMENDED_ARGS="-Dbamboo.orphaned.test.case.cron=NEW-CRON-EXPRESSION

You can do this by following the steps in the documentation Configuring your system properties ( This will require a Bamboo restart )

${JVM_SUPPORT_RECOMMENDED_ARGS:="-Dbamboo.orphaned.test.case.cron='0/5 * * ? * * *'"}

One sample CRON expression above means this Job would run every 5 mins, please note this process is little resource intensive, hence it does not run very frequently by default, you should monitor your system performance if you are frequently running these scheduled jobs.

Solution 2

If the plans related to build results where these test cases are produced are deleted, these test cases are cleaned up immediately, using the below query we can identify the plans where there are large number of test cases and take decision accordingly.

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

Solution 3

Relook at the Bamboo Global expiry and Bamboo Plan expiry settings and modify the rules to make it more strict, so that Bamboo is able to cleanup more build results which in turn will cleanup the test cases associated with them.

Solution 4

You can call the below REST API which would call the scheduled test case cleanup Job immediately.

curl -u admin_user:admin_password -p -H "Content-Type: application/json" -X POST -d '{"name": "removeOrphanedTestCaseJob", "groupName": "removeOrphanedTestCaseGroup"}' -k 'http://bamboo962:8085/rest/admin/latest/scheduler/jobs/trigger'

Please replace admin_user, admin_password and http://bamboo962:8085 with appropriate values.