Add GraphQL API for ci queueing history using ClickHouse

Vladimir Shushlinrequested to merge
vshushlin/ci-queueing-delay-graphql into master

What does this MR do and why?

Adds graphql API for [Runner Fleet dashboard] API for percentiles of... (#421199 - closed) which will be used in Visualize percentiles of estimated job wait tim... (#411966 - closed)

Based on Add service calculating ci job queuing time per... (!130211 - merged) and should not be merged before it

Related: discussion the API design: !128752 (comment 1523079095)

How to set up and validate locally

  1. Follow steps from https://docs.gitlab.com/ee/development/database/clickhouse/clickhouse_within_gitlab.html up until ClickHouseTestRunner.new.ensure_schema

  2. Connect to clickhouse and generate the data (replace 300000000 with number of builds you want to generate):

    INSERT INTO ci_finished_builds
    (id, project_id, pipeline_id, status, finished_at, created_at, started_at, queued_at,
     runner_id, runner_manager_system_xid, runner_run_untagged, runner_type,
     runner_manager_version, runner_manager_revision, runner_manager_platform, runner_manager_architecture)
SELECT
    number AS id,
    toUInt64(1 * pow(1 - (rand() / 4294967295.0), -1/1.0)) AS project_id,
    toUInt64(1 * pow(1 - (rand() / 4294967295.0), -1/1.0)) AS pipeline_id,
    arrayElement(['success', 'success', 'success', 'success', 'success', 'failed', 'failed', 'cancelled'], 1 + (rand() % 8)) AS status,
    (now() - toIntervalSecond(rand() % (86400 * 30))) AS finished_at,
    (finished_at - toIntervalSecond(-600 * ln(1 - rand() / 4294967295.0))) AS started_at,
    (started_at - toIntervalSecond(-10 * ln(1 - rand() / 4294967295.0))) AS queued_at,
    (queued_at - toIntervalSecond(-10 * ln(1 - rand() / 4294967295.0))) AS created_at,
    rand() % 10000 AS runner_id,
    toString(rand() % 1000) AS runner_manager_system_xid,
    rand() % 2 AS runner_run_untagged,
    rand() % 3 + 1 AS runner_type,
    '' AS runner_manager_version,
    '' AS runner_manager_revision,
    '' AS runner_manager_platform,
    '' AS runner_manager_architecture
FROM numbers(300000);

  3. login as admin user

  4. go to http://172.16.123.1:3000/-/graphql-explorer

  5. run the following query (replace time parameters to match last 3 hours):

    {
  ciQueueingHistory(runnerType: INSTANCE_TYPE, fromTime: "2023-09-07 13:46:38 UTC", toTime: "2023-09-07 15:46:38 UTC") {
    timeSeries {
      time
      p99
      p95
      p90
      p50
      p25     
    }
  }
}

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Vladimir Shushlin

Merge request reports