Add runner filter for generic owner field (!150347) · Merge requests · GitLab.org / GitLab

Martin Duffy requested to merge gitlab-community/gitlab:filter-runner-by-owner into master Apr 22, 2024

Please check this box if this contribution uses AI-generated content as outlined in the GitLab DCO & CLA

What does this MR do and why?

This MR has back-end changes to add a runners finder with a generic owner field intended to search for the owning Project/Group, or for Administrator Instance runners.

See the relevant design here: #378558. The screenshots in this design give an idea of how this back-end functionality is intended to be used.

I took this approach so that a filter text passed to the back-end can be added to the existing GraphQL calls on the front-end to filter by these different fields.

How to set up and validate locally

Make sure you have at least one GitLab runner registered and running.

From the Rails console, find the full path of a project and a group that contain runners:

> Ci::Runner.project_type.first.owner.full_path
=> "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1/rf-project-1-1-1-1"
> Ci::Runner.group_type.first.owner.full_path
=> "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1"

Go to http://gdk.test:3000/-/graphql-explorer

Use the following query, replacing with the values you obtained in step 1:

{
  projectRunners: runners(
    ownerFullPath: "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1/rf-project-1-1-1-1"
  ) {
    nodes {
      id
      description
      ownerProject {
        fullPath
      }
    }
  }
  groupRunners: runners(
    ownerFullPath: "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1"
  ) {
    nodes {
      id
      description
      groups {
        nodes {
          fullPath
        }
      }
    }
  }
}

The result should contain only runners from that project and from that group.

The ownerWildcard: ADMINISTRATORS filter returns all runners created by an Administrator:

{
  runners(ownerWildcard: ADMINISTRATORS) {
    nodes {
      id
      description
      createdBy {
        username
      }
    }
  }
}

Database query plans

Getting a list of runners by project path

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/33968/commands/104441

SELECT "routes".*
FROM "routes"
WHERE "routes"."source_type" = 'Project'
  AND "routes"."path" = 'gitlab-org/gitlab'
LIMIT 1

 Limit  (cost=0.56..3.58 rows=1 width=93) (actual time=7.201..7.203 rows=1 loops=1)
   Buffers: shared read=5
   I/O Timings: read=7.127 write=0.000
   ->  Index Scan using index_routes_on_path on public.routes  (cost=0.56..3.58 rows=1 width=93) (actual time=7.199..7.199 rows=1 loops=1)
         Index Cond: ((routes.path)::text = 'gitlab-org/gitlab'::text)
         Filter: ((routes.source_type)::text = 'Project'::text)
         Rows Removed by Filter: 0
         Buffers: shared read=5
         I/O Timings: read=7.127 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/33967/commands/104442

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 278964
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101

 Limit  (cost=21.36..21.37 rows=4 width=246) (actual time=9.598..9.599 rows=2 loops=1)
   Buffers: shared hit=9 read=10
   I/O Timings: read=9.486 write=0.000
   ->  Sort  (cost=21.36..21.37 rows=4 width=246) (actual time=9.596..9.597 rows=2 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=9 read=10
         I/O Timings: read=9.486 write=0.000
         ->  Nested Loop  (cost=0.86..21.32 rows=4 width=246) (actual time=5.370..9.568 rows=2 loops=1)
               Buffers: shared hit=3 read=10
               I/O Timings: read=9.486 write=0.000
               ->  Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects  (cost=0.43..7.53 rows=4 width=4) (actual time=4.369..5.845 rows=2 loops=1)
                     Index Cond: (ci_runner_projects.project_id = 278964)
                     Buffers: shared read=5
                     I/O Timings: read=5.810 write=0.000
               ->  Index Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..3.45 rows=1 width=246) (actual time=1.858..1.858 rows=1 loops=2)
                     Index Cond: (ci_runners.id = ci_runner_projects.runner_id)
                     Buffers: shared hit=3 read=5
                     I/O Timings: read=3.676 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

Getting a list of runners by group path

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/33968/commands/104443

SELECT "routes".*
FROM "routes"
WHERE "routes"."source_type" = 'Namespace'
  AND "routes"."path" = 'gitlab-org'
LIMIT 1

 Limit  (cost=0.56..3.58 rows=1 width=93) (actual time=2.075..2.076 rows=1 loops=1)
   Buffers: shared hit=3 read=2
   I/O Timings: read=2.014 write=0.000
   ->  Index Scan using index_routes_on_path on public.routes  (cost=0.56..3.58 rows=1 width=93) (actual time=2.074..2.074 rows=1 loops=1)
         Index Cond: ((routes.path)::text = 'gitlab-org'::text)
         Filter: ((routes.source_type)::text = 'Namespace'::text)
         Rows Removed by Filter: 0
         Buffers: shared hit=3 read=2
         I/O Timings: read=2.014 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB'

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/33968/commands/104445

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" = 9970

 Nested Loop  (cost=0.00..0.01 rows=1 width=1761) (actual time=0.004..0.004 rows=0 loops=1)
   I/O Timings: read=0.000 write=0.000
   ->  Seq Scan on public.ci_runners  (cost=0.00..0.00 rows=1 width=1761) (actual time=0.003..0.003 rows=0 loops=1)
         I/O Timings: read=0.000 write=0.000
   ->  Seq Scan on public.ci_runner_namespaces  (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Filter: (ci_runner_namespaces.namespace_id = 9970)
         Rows Removed by Filter: 0
         I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB'

Getting a list of runners owned by Administrators

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/33967/commands/104440

SELECT DISTINCT "ci_runners".*
FROM "ci_runners"
WHERE "ci_runners"."runner_type" = 1
  AND "ci_runners"."creator_id" IN (...)
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101

 Limit  (cost=183.77..183.84 rows=1 width=246) (actual time=5.496..5.545 rows=50 loops=1)
   Buffers: shared hit=36 read=113 dirtied=12
   WAL: records=12 fpi=12 bytes=77132
   I/O Timings: read=4.686 write=0.000
   ->  Unique  (cost=183.77..183.84 rows=1 width=246) (actual time=5.495..5.540 rows=50 loops=1)
         Buffers: shared hit=36 read=113 dirtied=12
         WAL: records=12 fpi=12 bytes=77132
         I/O Timings: read=4.686 write=0.000
         ->  Sort  (cost=183.77..183.78 rows=1 width=246) (actual time=5.494..5.497 rows=50 loops=1)
               Sort Key: ci_runners.created_at DESC, ci_runners.id DESC, ci_runners.token, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.maximum_timeout, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.maintainer_note, ci_runners.token_expires_at, ci_runners.allowed_plans, ci_runners.registration_type, ci_runners.creator_id, ci_runners.creation_state, ci_runners.allowed_plan_ids, ci_runners.sharding_key_id
               Sort Method: quicksort  Memory: 40kB
               Buffers: shared hit=36 read=113 dirtied=12
               WAL: records=12 fpi=12 bytes=77132
               I/O Timings: read=4.686 write=0.000
               ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=1.19..183.76 rows=1 width=246) (actual time=3.556..5.403 rows=50 loops=1)
                     Index Cond: (ci_runners.runner_type = 1)
                     Filter: (ci_runners.creator_id = ANY ('{...}'::bigint[]))
                     Rows Removed by Filter: 62
                     Buffers: shared hit=5 read=113 dirtied=12
                     WAL: records=12 fpi=12 bytes=77132
                     I/O Timings: read=4.686 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

Edited Nov 26, 2024 by Pedro Pombeiro

Add runner filter for generic owner field

What does this MR do and why?

How to set up and validate locally

Database query plans

Merge request reports