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:

  1. Add the flag, the resolver filter and specs. The select_all spec from the previous merge request now checks the percentile statement, because Ci::Partition.recent_ids also reads through select_all.
  2. Describe the partition limit in the queuedDuration field description and the GraphQL reference. The text is accurate with the flag on and off.
  3. Add the removal patch for the DeleteOldFeatureFlags keep.
  4. 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 1

In a development database with one partition, Rails renders the list as = 100.

References

Screenshots or screen recordings

Not applicable. There is no UI change.

How to set up and validate locally

  1. Enable the flag in the Rails console:
    Feature.enable(:jobs_statistics_recent_partitions)
  2. Sign in as an admin on an instance with a licensed runner_performance_insights feature.
  3. Open /-/graphql-explorer and run:
    { runners { jobsStatistics { queuedDuration { p50 p95 } } } }
  4. Check the log for the p_ci_builds statements and confirm they filter on partition_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.

Merge request reports

Loading
Loading