Skip to content

Reschedule background migration to copy container_registry_enabled

Reuben Pereira requested to merge 18792-reschedule-background-migration1 into master

What does this MR do?

This MR reschedules the background migration added in !58360 (merged). The previous time it was scheduled, the track_jobs parameter was set to true. However, the background migration did not update rows in the background_migration_jobs table as it completed jobs.

This MR reschedules the migration and modifies the background migration to update the background_migration_jobs table as it completes jobs.

Example query executed by background migration

UPDATE project_features SET container_registry_access_level = (CASE p.container_registry_enabled WHEN true THEN 20 WHEN false THEN 0 ELSE 0 END )
FROM projects p WHERE project_id = p.id AND project_id BETWEEN 13151901 and 13152200;

Execution plan:

 ModifyTable on public.project_features  (cost=1.12..1010.53 rows=206 width=92) (actual time=906.519..906.522 rows=0 loops=1)
   Buffers: shared hit=5568 read=456 dirtied=227
   I/O Timings: read=856.648
   ->  Nested Loop  (cost=1.12..1010.53 rows=206 width=92) (actual time=0.160..677.342 rows=241 loops=1)
         Buffers: shared hit=1111 read=316 dirtied=7
         I/O Timings: read=655.008
         ->  Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.56..273.05 rows=206 width=82) (actual time=0.096..116.958 rows=241 loops=1)
               Index Cond: ((project_features.project_id >= 13151901) AND (project_features.project_id <= 13152200))
               Buffers: shared hit=144 read=77 dirtied=3
               I/O Timings: read=112.970
         ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects p  (cost=0.56..3.58 rows=1 width=11) (actual time=2.315..2.315 rows=1 loops=241)
               Index Cond: (p.id = project_features.project_id)
               Buffers: shared hit=967 read=239 dirtied=4
               I/O Timings: read=542.038

Background migration estimates

projects table has 18,189,661 rows

All rows to be migrated.

21,000 rows per sidekiq job

18,189,661 / 21,000 = 867 jobs

300 rows per batch in job

21,000 / 300 = 70 batches per job

18,189,661 / 300 = 60633 total batches

Estimated times per batch:

907ms for update statement with 300 items (from https://console.postgres.ai/shared/0b28c145-2f28-40f1-95b9-fa46b0ee25b1)

Execution time per sidekiq job:
907 * 70 = 63.49 seconds

Sidekiq jobs are scheduled 2 minutes apart.

867 * 120 = 1734 minutes = ~28.9 hours

Migration output

== 20210415155043 MoveContainerRegistryEnabledToProjectFeatures3: migrating ===
-- Scheduled 1 MoveContainerRegistryEnabledToProjectFeature jobs with a maximum of 21000 records per batch and an interval of 120 seconds.

The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-04-15 16:59:33 UTC."
== 20210415155043 MoveContainerRegistryEnabledToProjectFeatures3: migrated (0.1052s)

Revert migration output:

== 20210401131948 MoveContainerRegistryEnabledToProjectFeatures2: reverting ===
== 20210401131948 MoveContainerRegistryEnabledToProjectFeatures2: reverted (0.0000s)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • 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

Related to #18792 (closed)

Edited by Mayra Cabrera

Merge request reports