How to reduce the size of test_case, test_case_result, test_class, test_class_result,test_error tables in Bamboo
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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-EXPRESSIONYou can do this by following the steps in the documentation Configuring your system properties ( This will require a Bamboo restart )
Example CRON Expression
${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(*) DESCSolution 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.
Was this helpful?