Create a database cleanup for ci_test_cases and ci_test_case_failures
Overview
These tables will grow indefinitely and slow down page performance for the the MR and/or test summary widget of not pruned occasionally.
Currently the table is new, after 24 hours on GitLab dot com it grew to ~10,000 rows. Given that pace, we likely have a few months to act, but we should get this squared away.
Proposal
- Create a cleanup job to delete data from projects that have been deleted (set to delete and past the delay you can save it in)
- Create a cleanup job that deletes data more than 28 days old (around 1 month) which is twice what we display today so gives us some ability to extend the window for users.
- Looking into the DB schema, I assume that we're planning to use the
ci_test_case_failures.failed_at
column to determine the cleanup period (28 days). If we add an index to this column, then we can clean records up in batches efficiently. - Perhaps it's worth looking at the
Ci::DeleteObjectsWorker
. It uses theLimitedCapacity::Worker
to go through batches of rows and delete them. Example for batch delete (pseudo code):
# delete 1K items until we have nothing left to delete
loop do
result = execute("DELETE FROM ci_test_case_failures WHERE id IN (SELECT id FROM ci_test_case_failures WHERE failed_at > '28days ago' LIMIT 1000)")
break if result.count == 0 # no more items to delete
end
(From https://gitlab.com/gitlab-org/gitlab/-/issues/268249#note_484539212)
Questions
- Are there other table cleanup jobs we can base this off of?
- When should we get the database team involved?
Edited by Ricky Wiens