Skip to content

Backfill ci_runners_e59bb2812d table

What does this MR do and why?

This MR is the third part of a series of 3 MRs:

Number MR description
1 !165992 (merged) Implements the Gitlab::Database::PartitioningMigrationHelpers::TableManagementHelpers#partition_table_by_list method, which will allow tables such as ci_runners to be partitioned by an existing column (runner_type in that case).
2 !166308 (merged) Uses the new partition_table_by_list method to create the ci_runners_e59bb2812d partitioned table.
3 !166520 (merged) Backfills the new ci_runners_e59bb2812d table from ci_runners (ignores group/project runners that don't have a sharding_key_id).

It adds support for passing a sub-batch filter to enqueue_partitioning_data_migration, so that we can skip importing orphaned runners to the partitioned table.

Part of #442395

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

image

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

  1. Go to http://gdk.test:3000/gitlab-org and create a new blank project named Orphaned runners

  2. Visit http://gdk.test:3000/gitlab-org/orphaned-runners/-/runners/new to create a new project runner. Select Run untagged jobs and then Create runner

  3. Note the numeric ID of the created runner in the new URL (e.g. http://gdk.test:3000/gitlab-org/orphaned-runners/-/runners/12760/register).

  4. Click the View runners button.

  5. In the Assigned project runners list, click Remove runner for the runner you just created. This has the effect of removing the ci_runner_projects records that links the runner with the project:

    image

  6. The runner is not quite orphaned, since these days we already have logic to fill sharding_key_id on ci_runners when the runner is created. Let's clear that out to simulate a legacy runner that is missing a sharding_key_id. In the Postgres console, run:

    UPDATE ci_runners SET sharding_key_id = NULL WHERE id = 12760 -- Replace with the ID of your runner
  7. If you now run the following query to find out how many orphaned runners you have, you should have at least the one you just created:

    SELECT COUNT(*)
    FROM ci_runners
    WHERE sharding_key_id IS NULL
      AND runner_type <> 1
  8. Let's run the migration, and this orphaned runner should be skipped:

    bundle exec rails db:migrate
  9. Check http://gdk.test:3000/admin/background_migrations?database=ci to follow the state of the batched migration.

    image

  10. Compare the number of runners in the source table, and the ones in the partitioned table. The partitioned table should not contain the orphaned runners:

    query result
    SELECT COUNT(*) FROM ci_runners WHERE sharding_key_id IS NULL AND runner_type <> 1 image
    SELECT COUNT(*) FROM ci_runners_e59bb2812d WHERE sharding_key_id IS NULL AND runner_type <> 1 image
    SELECT COUNT(*) FROM ci_runners SELECT COUNT(*) FROM ci_runners_e59bb2812d image
Edited by Pedro Pombeiro

Merge request reports

Loading