ClickHouse를 이용한 병합 요청 분석

병합 요청 분석 기능은 프로젝트에서 병합된 병합 요청에 대한 통계를 보여주며, 레코드 수준의 메타데이터도 노출합니다.

집계 내용은 다음과 같습니다:

  • 병합까지의 평균 시간: 생성 시간과 병합 시간 사이의 기간입니다.

  • 월별 집계: 병합된 병합 요청의 12개월 차트입니다.

차트 아래에서는 사용자가 페이지당 12개월의 병합 요청 목록을 확인할 수 있습니다.

다음 기준으로 필터링할 수 있습니다:

  • 작성자
  • 담당자
  • 레이블
  • 마일스톤
  • 소스 브랜치
  • 대상 브랜치

현재 성능 문제

  • 집계 쿼리는 전문화된 인덱스가 필요하며, 이는 추가적인 디스크 공간을 소모합니다(인덱스 전용 스캔).

  • 전체 12개월 쿼리는 느립니다(쿼리 타임아웃). 대신 프론트엔드는 월별 데이터 요청(12개의 데이터베이스 쿼리)을 합니다.

  • 전문화된 인덱스가 있음에도 불구하고, 그룹 수준에서 기능을 제공하는 것은 병합 요청의 대량으로 인해 실행 가능하지 않습니다.

예시 쿼리

지정된 월에 병합된 병합 요청의 수를 가져옵니다:

SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
         (SELECT 1
          FROM "banned_users"
          WHERE (merge_requests.author_id = banned_users.user_id)))
  AND "merge_request_metrics"."target_project_id" = 278964
  AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
  AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'

merge_request_metrics 테이블은 첫 페이지 로드 시간을 개선하기 위해 비정규화되었습니다( target_project_id 추가). 쿼리 자체는 작은 날짜 범위에서 잘 작동하지만, 날짜 범위가 증가할수록 타임아웃이 발생할 수 있습니다.

추가 필터가 추가되면, 쿼리는 다음과 같이 더 복잡해지며 merge_requests 테이블도 필터링해야 합니다:

SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
         (SELECT 1
          FROM "banned_users"
          WHERE (merge_requests.author_id = banned_users.user_id)))
  AND "merge_requests"."author_id" IN
    (SELECT "users"."id"
     FROM "users"
     WHERE (LOWER("users"."username") IN (LOWER('ahegyi'))))
  AND "merge_request_metrics"."target_project_id" = 278964
  AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
  AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'

병합까지의 평균 시간을 계산하기 위해, 병합 요청 생성 시간과 병합 시간 사이의 총 시간을 쿼리합니다.

SELECT EXTRACT(epoch
               FROM SUM(AGE(merge_request_metrics.merged_at, merge_request_metrics.created_at)))
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
         (SELECT 1
          FROM "banned_users"
          WHERE (merge_requests.author_id = banned_users.user_id)))
  AND "merge_requests"."author_id" IN
    (SELECT "users"."id"
     FROM "users"
     WHERE (LOWER("users"."username") IN (LOWER('ahegyi'))))
  AND "merge_request_metrics"."target_project_id" = 278964
  AND "merge_request_metrics"."merged_at" >= '2022-08-01 00:00:00'
  AND "merge_request_metrics"."merged_at" <= '2022-09-01 00:00:00'
  AND "merge_request_metrics"."merged_at" > "merge_request_metrics"."created_at"
LIMIT 1

ClickHouse에 병합 요청 데이터 저장

병합 요청 데이터를 ClickHouse에 저장하고 쿼리하는 다른 여러 사용 사례가 존재합니다.

이 문서에서는 이 특정 기능에 집중합니다.

핵심 데이터는 merge_request_metrics와 merge_requests 데이터베이스 테이블에 존재합니다. 일부 필터는 추가 테이블을 결합해야 합니다:

  • banned_users: 금지된 사용자가 생성한 병합 요청을 필터링합니다.
  • labels: 병합 요청에는 하나 이상의 할당된 레이블이 있을 수 있습니다.
  • assignees: 병합 요청에는 하나 이상의 담당자가 있을 수 있습니다.
  • merged_at: merged_at 열은 merge_request_metrics 테이블에 위치합니다.

merge_requests 테이블은 직접 필터링할 수 있는 데이터를 포함합니다:

  • Author: author_id 열을 통해.
  • Milestone: milestone_id 열을 통해.
  • Source branch.
  • Target branch.
  • Project: project_id 열을 통해.

ClickHouse 데이터 최신 상태 유지

불행히도 merge_requests 테이블을 복제하거나 동기화하는 것만으로는 충분하지 않습니다. ClickHouse 데이터베이스에 비정규화된 merge_requests 행을 삽입하기 위해 관련 테이블에 대한 별도의 쿼리가 필요합니다.

변경 감지는 구현하기 복잡합니다. 몇 가지 단축할 수 있는 점은 다음과 같습니다:

  • 이 기능은 GitLab Premium 및 GitLab Ultimate 고객에게 제공됩니다. 모든 데이터를 동기화할 필요는 없으며, 라이센스가 있는 그룹의 merge_requests 레코드만 동기화하면 됩니다.
  • 데이터 변경(종종)은 MergeRequest 서비스에 의해 발생하며, 이때 updated_at 타임스탬프 열이 대체로 일관성이 있습니다. 어떤 종류의 점진적 동기화 프로세스를 구현할 수 있습니다.
  • 우리는 병합된 병합 요청만 쿼리하면 됩니다. 병합 후, 레코드는 거의 변경되지 않습니다.

데이터베이스 테이블 구조

데이터베이스 테이블 구조는 비정규화를 사용하여 모든 필수 열을 하나의 데이터베이스 테이블에서 사용할 수 있게 합니다. 이렇게 하면 JOINs를 사용할 필요가 없습니다.

CREATE TABLE merge_requests
(
    `id` UInt64,
    `project_id` UInt64 DEFAULT 0 NOT NULL,
    `author_id` UInt64 DEFAULT 0 NOT NULL,
    `milestone_id` UInt64 DEFAULT 0 NOT NULL,
    `label_ids` Array(UInt64) DEFAULT [] NOT NULL,
    `assignee_ids` Array(UInt64) DEFAULT [] NOT NULL,
    `source_branch` String DEFAULT '' NOT NULL,
    `target_branch` String DEFAULT '' NOT NULL,
    `merged_at` DateTime64(6, 'UTC') NOT NULL,
    `created_at` DateTime64(6, 'UTC') DEFAULT now() NOT NULL,
    `updated_at` DateTime64(6, 'UTC') DEFAULT now() NOT NULL
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (project_id, merged_at, id);

활동 데이터 예제(activity data example)와 유사하게, 우리는 ReplacingMergeTree 엔진을 사용합니다. 병합 요청 레코드의 여러 열이 변경될 수 있으므로 테이블을 최신 상태로 유지하는 것이 중요합니다.

데이터베이스 테이블은 project_id, merged_at, id 열로 정렬됩니다. 이 정렬은 우리의 사용 사례에 맞게 테이블 데이터를 최적화합니다: 프로젝트의 merged_at 열을 쿼리하는 것입니다.

카운트 쿼리 다시 쓰기

먼저 테이블에 대한 데이터를 생성해 보겠습니다.

INSERT INTO merge_requests (id, project_id, author_id, milestone_id, label_ids, merged_at, created_at)
SELECT id, project_id, author_id, milestone_id, label_ids, merged_at, created_at
FROM generateRandom('id UInt64, project_id UInt8, author_id UInt8, milestone_id UInt8, label_ids Array(UInt8), merged_at DateTime64(6, \'UTC\'), created_at DateTime64(6, \'UTC\')')
LIMIT 1000000;
note
일부 정수 데이터 유형은 UInt8로 캐스트되었으므로 서로 다른 행에서 동일한 값을 가질 확률이 높습니다.

원래 카운트 쿼리는 한 달 동안의 데이터만 집계했습니다. ClickHouse에서는 전체 연도의 데이터 집계를 시도할 수 있습니다.

PostgreSQL 기반의 카운트 쿼리:

SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
         (SELECT 1
          FROM "banned_users"
          WHERE (merge_requests.author_id = banned_users.user_id)))
  AND "merge_request_metrics"."target_project_id" = 278964
  AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
  AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'

ClickHouse 쿼리:

SELECT
  toYear(merged_at) AS year,
  toMonth(merged_at) AS month,
  COUNT(*)
FROM merge_requests
WHERE
  project_id = 200
  AND merged_at BETWEEN '2022-01-01 00:00:00'
  AND '2023-01-01 00:00:00'
GROUP BY year, month

쿼리는 생성된 데이터에 비해 현저히 적은 수의 행을 처리했습니다. ORDER BY 절(기본 키)은 쿼리 실행에 도움이 되고 있습니다:

11 rows in set. Elapsed: 0.010 sec.
Processed 8.19 thousand rows, 131.07 KB (783.45 thousand rows/s., 12.54 MB/s.)

병합 시간 평균 쿼리 재작성

이 쿼리는 평균 병합 시간을 다음과 같이 계산합니다: duration(created_at, merged_at) / merge_request_count. 계산은 두 단계로 나누어 수행됩니다:

  1. 월별 카운트와 월별 지속 시간을 요청합니다.
  2. 카운트를 합산하여 연간 카운트를 얻습니다.
  3. 지속 시간을 합산하여 연간 지속 시간을 얻습니다.
  4. 지속 시간을 카운트로 나눕니다.

ClickHouse에서는 한 쿼리로 평균 병합 시간을 계산할 수 있습니다:

SELECT
  SUM(
    dateDiff('second', merged_at, created_at) / 3600 / 24
  ) / COUNT(*) AS mean_time_to_merge -- mean_time_to_merge는 일 단위입니다
FROM merge_requests
WHERE
  project_id = 200
  AND merged_at BETWEEN '2022-01-01 00:00:00'
  AND '2023-01-01 00:00:00'

필터링

위의 데이터베이스 쿼리는 기본 쿼리로 사용될 수 있습니다. 추가 필터를 추가할 수 있습니다. 예를 들어, 레이블과 마일스톤으로 필터링:

SELECT
  toYear(merged_at) AS year,
  toMonth(merged_at) AS month,
  COUNT(*)
FROM merge_requests
WHERE
  project_id = 200
  AND milestone_id = 15
  AND has(label_ids, 118)
  AND -- 배열에 118 포함
  merged_at BETWEEN '2022-01-01 00:00:00'
  AND '2023-01-01 00:00:00'
GROUP BY year, month

특정 필터를 최적화하는 것은 일반적으로 데이터베이스 인덱스를 사용하여 수행됩니다. 이 특정 쿼리는 8000개의 행을 읽습니다:

1 row in set. Elapsed: 0.016 sec.
Processed 8.19 thousand rows, 589.99 KB (505.38 thousand rows/s., 36.40 MB/s.)

milestone_id에 인덱스를 추가합니다:

ALTER TABLE merge_requests
ADD
  INDEX milestone_id_index milestone_id TYPE minmax GRANULARITY 10;
ALTER TABLE
  merge_requests MATERIALIZE INDEX milestone_id_index;

생성된 데이터에서 인덱스를 추가해도 성능이 개선되지 않았습니다.

금지된 사용자 필터

GitLab에 최근 추가된 기능은 관리자가 금지한 작성자가 있는 병합 요청을 필터링합니다. 금지된 사용자는 banned_users 데이터베이스 테이블에서 인스턴스 수준에서 추적됩니다.

아이디어 1: 금지된 사용자 ID 열거하기

이것은 ClickHouse 데이터베이스 스키마에 구조적 변경이 필요하지 않습니다. 프로젝트에서 금지된 사용자를 쿼리하고 쿼리 시간에 해당 값을 필터링할 수 있습니다.

금지된 사용자 가져오기 (PostgreSQL):

SELECT user_id FROM banned_users

ClickHouse에서:

SELECT
  toYear(merged_at) AS year,
  toMonth(merged_at) AS month,
  COUNT(*)
FROM merge_requests
WHERE
  author_id NOT IN (1, 2, 3, 4) AND -- 금지된 사용자
  project_id = 200
  AND milestone_id = 15
  AND has(label_ids, 118) AND -- 배열에 118 포함
  merged_at BETWEEN '2022-01-01 00:00:00'
  AND '2023-01-01 00:00:00'
GROUP BY year, month

이 접근 방식의 문제는 금지된 사용자 수가 크게 증가할 수 있으며 이는 쿼리를 더 크고 느리게 만들 수 있다는 것입니다.

아이디어 2: banned_users 테이블 복제

banned_users 테이블이 수백만 행으로 성장하지 않을 것이라고 가정하면, 이 전체 테이블을 주기적으로 ClickHouse에 동기화하려고 시도할 수 있습니다. 이러한 방법으로 대체로 일관된 banned_users 테이블을 ClickHouse 데이터베이스 쿼리에서 사용할 수 있습니다:

SELECT
  toYear(merged_at) AS year,
  toMonth(merged_at) AS month,
  COUNT(*)
FROM merge_requests
WHERE
  author_id NOT IN (SELECT user_id FROM banned_users) AND
  project_id = 200 AND
  milestone_id = 15 AND
  has(label_ids, 118) AND -- 배열에 118 포함
  merged_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 00:00:00'
GROUP BY year, month

또한, banned_users 테이블을 저장하여 쿼리 성능을 더욱 개선할 수 있습니다. dictionary 형태로 저장할 수 있습니다.

아이디어 3: 기능 변경

분석 계산을 위해 이 특정 필터를 제거하는 것이 허용될 수 있습니다.

이 접근 방식은 금지된 사용자의 병합 요청을 포함하는 것이 통계를 크게 왜곡하지 않는다고 가정합니다.