Skip to content

Add cron worker for cleaning up unit test tables

Erick Bajao requested to merge eb-clean-up-unit-test-tables into master

What does this MR do?

Solves #297008 (closed)

This adds a new cron worker that will run once daily and will do the following:

  1. Delete ci_unit_test_failures records that have failed_at older than 14 days.
  2. Then, delete ci_unit_tests records that don't have any remaining associated ci_unit_test_failures.

Queries and related DB info

The unit test tables have been around for more than a month now. As of May 11th, these are their current sizes:

  • ci_unit_test_failures: 490_501 rows
    • If we were to run the clean up job today, around 374_251 will be deleted.
  • ci_unit_tests: 106_158 rows
    • After the ci_unit_test_failures have been cleaned up, around 76_299 will be deleted.

Here are the queries tested on #database-lab. Please note that these queries are executed in a loop.

This is the query from using Ci::UnitTestFailure.deletable:

SELECT "ci_unit_test_failures"."id" FROM "ci_unit_test_failures"
WHERE (failed_at < '2021-04-27 11:42:42.228774') LIMIT 100

Query plan: https://explain-depesz.postgres.ai/s/5g

This is the query from using Ci::UnitTest.deletable:

SELECT "ci_unit_tests"."id" FROM "ci_unit_tests"
WHERE (
  NOT EXISTS (
    SELECT 1 FROM "ci_unit_test_failures"
    WHERE (
      ci_unit_test_failures.unit_test_id = ci_unit_tests.id
    )
  )
) LIMIT 100

Query plan: https://explain-depesz.postgres.ai/s/bv

I also tested the actual delete queries, for example:

DELETE FROM "ci_unit_test_failures"
WHERE "ci_unit_test_failures"."id" IN (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100
)

Query plan: https://explain-depesz.postgres.ai/s/tF

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Erick Bajao

Merge request reports