Add support for ordering group runners by most active
requested to merge pedropombeiro/437849/1-add-most_active_desc-support-for-group-runners into master
What does this MR do and why?
- Changelog: added
This MR adds support for checking for the most active group runners. It does so by changing ee/app/finders/ee/ci/runners_finder.rb and the Ci::Runner model to allow sorting by most_active_desc for group runners when a group is specified.
Part of #452185 (closed), #437849 (closed)
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.
| Scenario | Screenshot |
|---|---|
Using DIRECT membership |
 |
| Not specifying membership |  |
Using DESCENDANTS membership |
 |
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Create a group runner with the following tags:
shell,gdk,mac, for instance on http://gdk.test:3000/groups/gitlab-org/-/runners -
Create a simple pipeline on a project inside that group:
default: tags: - shell - gdk - mac before_script: - echo "Before script section" - echo "For example you might run an update here or install a build dependency" - echo "Or perhaps you might print out some debugging details" after_script: - echo "After script section" - echo "For example you might do some cleanup here" build1: stage: build script: - echo "Do your build here" - sleep 30 test1: stage: test script: - echo "Do a test here" - echo "For example run a test suite" - sleep 30 test2: stage: test script: - echo "Do another parallel test here" - echo "For example run a lint test" - sleep 60 deploy1: stage: deploy script: - echo "Do your deploy here" environment: production -
Run the following query:
{ group(fullPath: "gitlab-org") { runners(first: 5, type: GROUP_TYPE, membership: DIRECT, sort: MOST_ACTIVE_DESC) { nodes { id jobs(statuses: RUNNING) { count } } } } }
Database query plans
Query most active group runners on group ID 9970 (gitlab-org)
https://postgres.ai/console/gitlab/gitlab-production-ci-deprecated/sessions/27821/commands/86657
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
INNER JOIN (
SELECT "ci_running_builds"."runner_id", ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY runner_id) AS rn
FROM "ci_running_builds"
WHERE "ci_running_builds"."runner_type" = 2
AND "ci_running_builds"."runner_owner_namespace_xid" = 9970) AS "limited_builds" ON "limited_builds"."runner_id" = "ci_runners"."id"
AND "limited_builds".rn <= 1000
WHERE "ci_runner_namespaces"."namespace_id" = 9970
AND "ci_runners"."runner_type" = 2
GROUP BY "ci_runners"."id"
ORDER BY COUNT(limited_builds.runner_id) DESC NULLS LAST, "ci_runners"."id" DESC
LIMIT 5 OFFSET 0 Limit (cost=103.23..103.23 rows=1 width=280) (actual time=1.049..1.053 rows=5 loops=1)
Buffers: shared hit=666
I/O Timings: read=0.000 write=0.000
-> Sort (cost=103.23..103.23 rows=1 width=280) (actual time=1.048..1.050 rows=5 loops=1)
Sort Key: (count(limited_builds.runner_id)) DESC NULLS LAST, ci_runners.id DESC
Sort Method: quicksort Memory: 29kB
Buffers: shared hit=666
I/O Timings: read=0.000 write=0.000
-> Aggregate (cost=103.20..103.22 rows=1 width=280) (actual time=0.953..0.995 rows=8 loops=1)
Group Key: ci_runners.id
Buffers: shared hit=660
I/O Timings: read=0.000 write=0.000
-> Sort (cost=103.20..103.20 rows=1 width=280) (actual time=0.943..0.951 rows=128 loops=1)
Sort Key: ci_runners.id DESC
Sort Method: quicksort Memory: 91kB
Buffers: shared hit=660
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=14.72..103.19 rows=1 width=280) (actual time=0.234..0.817 rows=128 loops=1)
Buffers: shared hit=660
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=14.29..99.74 rows=1 width=12) (actual time=0.200..0.466 rows=128 loops=1)
Hash Cond: (limited_builds.runner_id = ci_runner_namespaces.runner_id)
Buffers: shared hit=148
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on limited_builds (cost=0.29..85.57 rows=61 width=8) (actual time=0.121..0.361 rows=128 loops=1)
Filter: (limited_builds.rn <= 1000)
Rows Removed by Filter: 0
Buffers: shared hit=131
I/O Timings: read=0.000 write=0.000
-> WindowAgg (cost=0.29..83.30 rows=182 width=16) (actual time=0.120..0.345 rows=128 loops=1)
Buffers: shared hit=131
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using idx_ci_running_builds_on_runner_type_and_owner_xid_and_id on public.ci_running_builds (cost=0.29..80.11 rows=182 width=8) (actual time=0.060..0.273 rows=128 loops=1)
Index Cond: ((ci_running_builds.runner_type = 2) AND (ci_running_builds.runner_owner_namespace_xid = 9970))
Heap Fetches: 125
Buffers: shared hit=131
I/O Timings: read=0.000 write=0.000
-> Hash (cost=13.90..13.90 rows=8 width=4) (actual time=0.050..0.050 rows=14 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces (cost=0.43..13.90 rows=8 width=4) (actual time=0.023..0.045 rows=14 loops=1)
Index Cond: (ci_runner_namespaces.namespace_id = 9970)
Buffers: shared hit=17
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners (cost=0.43..3.45 rows=1 width=272) (actual time=0.002..0.002 rows=1 loops=128)
Index Cond: ((ci_runners.runner_type = 2) AND (ci_runners.id = ci_runner_namespaces.runner_id))
Buffers: shared hit=512
I/O Timings: read=0.000 write=0.000Edited by Pedro Pombeiro