Limit jobsStatistics to recent CI partitions behind a flag
What does this MR do and why?
CiRunnerConnection.jobsStatistics reads p_ci_builds three times: the first job of each runner, the full row of the current job, and the next job of the runner that was just used. None of the reads filters on partition_id, so PostgreSQL probes every partition for each of up to 5,000 runners. The cost grows with every new partition. On the largest group (about 202k group runners), one request costs about 0.84s of CI primary time.
This MR adds partition_id to the runner lookup and to the row finder, using Ci::Partition.recent_ids, as Resolvers::Ci::RunnerJobCountResolver already does. The list is a set of literals, so the planner can prune partitions in all three reads.
The change is behind the jobs_statistics_recent_partitions feature flag, which is off by default.
The percentiles then come from jobs on the current partition and the two most recent active ones. With the default ci_partitions_in_seconds_limit of one month, that is about the last two to three months of jobs.
Commits:
- Add the flag, the resolver filter and specs. The
select_allspec from the previous merge request now checks the percentile statement, becauseCi::Partition.recent_idsalso reads throughselect_all. - Describe the partition limit in the
queuedDurationfield description and the GraphQL reference. The text is accurate with the flag on and off. - Add the removal patch for the
DeleteOldFeatureFlagskeep. - Regenerate the GraphQL introspection schemas, which carry the field description.
Query
With the flag on, each of the three p_ci_builds reads gets one more condition:
... FROM "p_ci_builds" WHERE "p_ci_builds"."runner_id" = "array_cte"."id" AND "p_ci_builds"."partition_id" IN (<recent ids>) ORDER BY "p_ci_builds"."id" DESC LIMIT 1
... FROM "p_ci_builds" WHERE "p_ci_builds"."id" = recursive_keyset_cte.p_ci_builds_id_array[position] AND "p_ci_builds"."partition_id" IN (<recent ids>) LIMIT 1
... FROM "p_ci_builds" WHERE "p_ci_builds"."runner_id" = ... AND "p_ci_builds"."id" < ... AND "p_ci_builds"."partition_id" IN (<recent ids>) ORDER BY "p_ci_builds"."id" DESC LIMIT 1In a development database with one partition, Rails renders the list as = 100.
References
- https://gitlab.com/gitlab-org/gitlab/-/work_items/638240
- Parent: https://gitlab.com/gitlab-org/gitlab/-/work_items/638233
- Previous change: !268475 (merged)
Screenshots or screen recordings
Not applicable. There is no UI change.
How to set up and validate locally
- Enable the flag in the Rails console:
Feature.enable(:jobs_statistics_recent_partitions) - Sign in as an admin on an instance with a licensed
runner_performance_insightsfeature. - Open
/-/graphql-explorerand run:{ runners { jobsStatistics { queuedDuration { p50 p95 } } } } - Check the log for the
p_ci_buildsstatements and confirm they filter onpartition_id.
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.