Skip to content

Populate organization_id in runner tables

Pedro Pombeirorequested to merge
pedropombeiro/523694/2-populate-organization_id-in-runner-tables into master
MR Description
1. !193171 (merged) Add organization_id column to runner tables
2. !193202 (merged) 👈 Populate organization_id in runner tables
3. !193195 (merged) Backfill organization_id column in runner tables

What does this MR do and why?

This MR changes the app code to populate the new organization_id column in runner tables.This column will eventually replace the sharding_key_id column.

It is a break-up of !189651 (closed), which has already received a few reviews, but was getting too big.

References

Database query plans

Preparation:

UPDATE ci_runners SET sharding_key_id = 100000000 WHERE runner_type = 3 AND sharding_key_id = 28071850;
UPDATE ci_runner_machines SET sharding_key_id = 100000000 WHERE runner_type = 3 AND sharding_key_id = 28071850;

ALTER TABLE ci_runners ADD COLUMN organization_id bigint;
ALTER TABLE ci_runner_machines ADD COLUMN organization_id bigint;
ALTER TABLE ci_runner_taggings ADD COLUMN organization_id bigint;

CREATE INDEX index_ci_runners_on_organization_id_when_not_null ON ONLY ci_runners USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_10baeccf61 ON instance_type_ci_runners USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_ci_runner_taggings_on_organization_id_when_not_null ON ONLY ci_runner_taggings USING btree (organization_id);
CREATE INDEX index_2890b5102d ON ci_runner_taggings_group_type USING btree (organization_id);
CREATE INDEX index_ci_runner_machines_on_organization_id_when_not_null ON ONLY ci_runner_machines USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_4e69135dec ON group_type_ci_runner_machines USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_69a62c44bc ON project_type_ci_runner_machines USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_87a124ac92 ON project_type_ci_runners USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_9231a99090 ON instance_type_ci_runner_machines USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_927e85bcd2 ON ci_runner_taggings_instance_type USING btree (organization_id);
CREATE INDEX index_eef072ff6f ON group_type_ci_runners USING btree (organization_id) WHERE (organization_id IS NOT NULL);
CREATE INDEX index_f557150578 ON ci_runner_taggings_project_type USING btree (organization_id);
ALTER INDEX index_ci_runners_on_organization_id_when_not_null ATTACH PARTITION index_10baeccf61;
ALTER INDEX index_ci_runner_taggings_on_organization_id_when_not_null ATTACH PARTITION index_2890b5102d;
ALTER INDEX index_ci_runner_machines_on_organization_id_when_not_null ATTACH PARTITION index_4e69135dec;
ALTER INDEX index_ci_runner_machines_on_organization_id_when_not_null ATTACH PARTITION index_69a62c44bc;
ALTER INDEX index_ci_runners_on_organization_id_when_not_null ATTACH PARTITION index_87a124ac92;
ALTER INDEX index_ci_runner_machines_on_organization_id_when_not_null ATTACH PARTITION index_9231a99090;
ALTER INDEX index_ci_runner_taggings_on_organization_id_when_not_null ATTACH PARTITION index_927e85bcd2;
ALTER INDEX index_ci_runners_on_organization_id_when_not_null ATTACH PARTITION index_eef072ff6f;
ALTER INDEX index_ci_runner_taggings_on_organization_id_when_not_null ATTACH PARTITION index_f557150578;
UpdateProjectRunnersOwnerService

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/38986/commands/119953

UPDATE
  "ci_runners"
SET sharding_key_id = (
    SELECT "ci_runner_projects"."project_id"
    FROM "ci_runner_projects"
    WHERE "ci_runner_projects"."runner_id" = "ci_runners"."id"
    ORDER BY "ci_runner_projects"."id" ASC
    LIMIT 1), organization_id = 1
WHERE "ci_runners"."id" IN (<1000 runner ids>)
  AND (EXISTS (
      SELECT 1
      FROM "ci_runner_projects"
      WHERE "ci_runner_projects"."runner_id" = "ci_runners"."id"
      LIMIT 1))
 ModifyTable on public.ci_runners  (cost=1.44..15551.73 rows=0 width=0) (actual time=667.910..667.913 rows=0 loops=1)
   Buffers: shared hit=70580 read=1973 dirtied=1205 written=2
   WAL: records=16886 fpi=96 bytes=2520402
   I/O Timings: read=592.436 write=0.135
   ->  Nested Loop Semi Join  (cost=1.44..15551.73 rows=1059 width=32) (actual time=51.523..595.964 rows=1000 loops=1)
         Buffers: shared hit=15840 read=1325 dirtied=223
         WAL: records=371 fpi=16 bytes=148105
         I/O Timings: read=577.772 write=0.000
         ->  Merge Append  (cost=1.02..5891.77 rows=2118 width=18) (actual time=45.110..54.473 rows=1000 loops=1)
               Sort Key: ci_runners.id
               Buffers: shared hit=8864 read=295 dirtied=207
               WAL: records=355 fpi=0 bytes=21785
               I/O Timings: read=47.131 write=0.000
               ->  Index Scan using check_5c34a3c1db on public.instance_type_ci_runners ci_runners_1  (cost=0.14..214.31 rows=118 width=18) (actual time=0.941..0.941 rows=0 loops=1)
                     Index Cond: (ci_runners_1.id = ANY ('{<1000 runner ids>}'::bigint[]))
                     Buffers: shared hit=1002 read=1
                     I/O Timings: read=0.525 write=0.000
               ->  Index Scan using check_81b90172a6 on public.group_type_ci_runners ci_runners_2  (cost=0.42..2846.19 rows=1000 width=18) (actual time=43.817..43.817 rows=0 loops=1)
                     Index Cond: (ci_runners_2.id = ANY ('{<1000 runner ids>}'::bigint[]))
                     Buffers: shared hit=4941 read=215 dirtied=207
                     WAL: records=355 fpi=0 bytes=21785
                     I/O Timings: read=3.988 write=0.000
         ->  Index Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects  (cost=0.43..4.44 rows=2 width=10) (actual time=0.535..0.535 rows=1 loops=1000)
               Index Cond: (ci_runner_projects.runner_id = ci_runners.id)
               Buffers: shared hit=2973 read=1030 dirtied=16
               WAL: records=16 fpi=16 bytes=126320
               I/O Timings: read=530.641 write=0.000
         SubPlan 1
           ->  Limit  (cost=4.86..4.86 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1000)
                 Buffers: shared hit=4003
                 I/O Timings: read=0.000 write=0.000
                 ->  Sort  (cost=4.86..4.86 rows=2 width=8) (actual time=0.004..0.004 rows=1 loops=1000)
                       Sort Key: ci_runner_projects_1.id
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=4003
                       I/O Timings: read=0.000 write=0.000
                       ->  Index Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects ci_runner_projects_1  (cost=0.43..4.85 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=1000)
                             Index Cond: (ci_runner_projects_1.runner_id = ci_runners.id)
                             Buffers: shared hit=4000
                             I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', seq_page_cost = '4', jit = 'off', random_page_cost = '1.5', work_mem = '100MB'

MR acceptance checklist

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

Edited by Pedro Pombeiro

Merge request reports