InfoGrab DocsInfoGrab Docs

ClickHouse를 활용한 머지 리퀘스트 애널리틱스

요약

머지 리퀘스트 애널리틱스 기능은 프로젝트에서 머지된 머지 리퀘스트에 대한 통계를 보여주며, 레코드 수준의 메타데이터도 제공합니다. 평균 머지 소요 시간: 생성 시각과 머지 시각 사이의 기간. 월별 집계: 12개월간 머지된 머지 리퀘스트의 차트.

머지 리퀘스트 애널리틱스 기능은 프로젝트에서 머지된 머지 리퀘스트에 대한 통계를 보여주며, 레코드 수준의 메타데이터도 제공합니다. 집계 항목에는 다음이 포함됩니다:

  • 평균 머지 소요 시간: 생성 시각과 머지 시각 사이의 기간.

  • 월별 집계: 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를 추가하는 방식으로 역정규화(de-normalized)되었습니다. 이 쿼리 자체는 짧은 날짜 범위에서는 잘 동작하지만, 날짜 범위가 늘어날수록 타임아웃이 발생할 수 있습니다.

추가 필터가 적용되면, 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_id 칼럼을 통해 필터링.

  • 마일스톤: milestone_id 칼럼을 통해 필터링.

  • 소스 브랜치.

  • 타깃 브랜치.

  • 프로젝트: project_id 칼럼을 통해 필터링.

ClickHouse 데이터를 최신 상태로 유지#

merge_requests 테이블을 단순히 복제하거나 동기화하는 것만으로는 충분하지 않습니다. 역정규화된 merge_requests 행 한 건을 ClickHouse 데이터베이스에 삽입하려면 연관 테이블에 대한 별도 쿼리가 필요합니다.

변경 감지(change detection)를 구현하는 것은 간단하지 않습니다. 몇 가지 절충안이 있습니다:

  • 이 기능은 GitLab Premium 및 GitLab Ultimate 고객을 위해 제공됩니다. 모든 데이터를 동기화할 필요 없이, 라이선스가 있는 그룹에 속한 merge_requests 레코드만 선택적으로 동기화할 수 있습니다.

  • 데이터 변경은 (주로) MergeRequest 서비스를 통해 발생하며, updated_at 타임스탬프 칼럼의 업데이트가 대체로 일관되게 이루어집니다. 일종의 점진적 동기화 프로세스를 구현할 수 있습니다.

  • 머지된 머지 리퀘스트만 조회하면 됩니다. 머지된 이후에는 레코드가 거의 변경되지 않습니다.

데이터베이스 테이블 구조#

데이터베이스 테이블 구조는 역정규화를 사용하여 필요한 모든 칼럼을 하나의 데이터베이스 테이블에서 사용할 수 있도록 합니다. 이렇게 하면 JOIN이 필요하지 않습니다.

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 now64(6, 'UTC') NOT NULL,
    `updated_at` DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC') NOT NULL
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (project_id, merged_at, id);

활동 데이터 예시와 마찬가지로, 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;

일부 정수형 데이터 타입은 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. 계산은 두 단계로 이루어집니다:

  • 월별 카운트 및 월별 기간 값을 요청합니다.

  • 카운트를 합산하여 연간 카운트를 구합니다.

  • 기간을 합산하여 연간 기간을 구합니다.

  • 기간을 카운트로 나눕니다.

ClickHouse에서는 평균 머지 소요 시간을 하나의 쿼리로 계산할 수 있습니다:

SELECT
  SUM(
    dateDiff('second', merged_at, created_at) / 3600 / 24
  ) / COUNT(*) AS mean_time_to_merge -- mean_time_to_merge is in days
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 -- array includes 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 -- banned users
  project_id = 200
  AND milestone_id = 15
  AND has(label_ids, 118) AND -- array includes 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에 동기화하는 방법을 시도할 수 있습니다. 이 방법을 사용하면 ClickHouse 데이터베이스 쿼리에서 대체로 일관된 banned_users 테이블을 활용할 수 있습니다:

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 -- array includes 118
  merged_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 00:00:00'
GROUP BY year, month

또는, 쿼리 성능을 더욱 개선하기 위해 banned_users 테이블을 딕셔너리로 저장할 수도 있습니다.

아이디어 3: 기능 변경#

분석적 계산에서는 이 특정 필터를 제거하는 것이 허용될 수 있습니다. 이 방법은 차단된 사용자의 머지 리퀘스트를 포함하더라도 통계에 큰 영향을 주지 않는다고 가정합니다.

ClickHouse를 활용한 머지 리퀘스트 애널리틱스

GitLab v19.1
원문 보기
요약

머지 리퀘스트 애널리틱스 기능은 프로젝트에서 머지된 머지 리퀘스트에 대한 통계를 보여주며, 레코드 수준의 메타데이터도 제공합니다. 평균 머지 소요 시간: 생성 시각과 머지 시각 사이의 기간. 월별 집계: 12개월간 머지된 머지 리퀘스트의 차트.

머지 리퀘스트 애널리틱스 기능은 프로젝트에서 머지된 머지 리퀘스트에 대한 통계를 보여주며, 레코드 수준의 메타데이터도 제공합니다. 집계 항목에는 다음이 포함됩니다:

  • 평균 머지 소요 시간: 생성 시각과 머지 시각 사이의 기간.

  • 월별 집계: 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를 추가하는 방식으로 역정규화(de-normalized)되었습니다. 이 쿼리 자체는 짧은 날짜 범위에서는 잘 동작하지만, 날짜 범위가 늘어날수록 타임아웃이 발생할 수 있습니다.

추가 필터가 적용되면, 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_id 칼럼을 통해 필터링.

  • 마일스톤: milestone_id 칼럼을 통해 필터링.

  • 소스 브랜치.

  • 타깃 브랜치.

  • 프로젝트: project_id 칼럼을 통해 필터링.

ClickHouse 데이터를 최신 상태로 유지#

merge_requests 테이블을 단순히 복제하거나 동기화하는 것만으로는 충분하지 않습니다. 역정규화된 merge_requests 행 한 건을 ClickHouse 데이터베이스에 삽입하려면 연관 테이블에 대한 별도 쿼리가 필요합니다.

변경 감지(change detection)를 구현하는 것은 간단하지 않습니다. 몇 가지 절충안이 있습니다:

  • 이 기능은 GitLab Premium 및 GitLab Ultimate 고객을 위해 제공됩니다. 모든 데이터를 동기화할 필요 없이, 라이선스가 있는 그룹에 속한 merge_requests 레코드만 선택적으로 동기화할 수 있습니다.

  • 데이터 변경은 (주로) MergeRequest 서비스를 통해 발생하며, updated_at 타임스탬프 칼럼의 업데이트가 대체로 일관되게 이루어집니다. 일종의 점진적 동기화 프로세스를 구현할 수 있습니다.

  • 머지된 머지 리퀘스트만 조회하면 됩니다. 머지된 이후에는 레코드가 거의 변경되지 않습니다.

데이터베이스 테이블 구조#

데이터베이스 테이블 구조는 역정규화를 사용하여 필요한 모든 칼럼을 하나의 데이터베이스 테이블에서 사용할 수 있도록 합니다. 이렇게 하면 JOIN이 필요하지 않습니다.

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 now64(6, 'UTC') NOT NULL,
    `updated_at` DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC') NOT NULL
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (project_id, merged_at, id);

활동 데이터 예시와 마찬가지로, 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;

일부 정수형 데이터 타입은 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. 계산은 두 단계로 이루어집니다:

  • 월별 카운트 및 월별 기간 값을 요청합니다.

  • 카운트를 합산하여 연간 카운트를 구합니다.

  • 기간을 합산하여 연간 기간을 구합니다.

  • 기간을 카운트로 나눕니다.

ClickHouse에서는 평균 머지 소요 시간을 하나의 쿼리로 계산할 수 있습니다:

SELECT
  SUM(
    dateDiff('second', merged_at, created_at) / 3600 / 24
  ) / COUNT(*) AS mean_time_to_merge -- mean_time_to_merge is in days
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 -- array includes 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 -- banned users
  project_id = 200
  AND milestone_id = 15
  AND has(label_ids, 118) AND -- array includes 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에 동기화하는 방법을 시도할 수 있습니다. 이 방법을 사용하면 ClickHouse 데이터베이스 쿼리에서 대체로 일관된 banned_users 테이블을 활용할 수 있습니다:

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 -- array includes 118
  merged_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 00:00:00'
GROUP BY year, month

또는, 쿼리 성능을 더욱 개선하기 위해 banned_users 테이블을 딕셔너리로 저장할 수도 있습니다.

아이디어 3: 기능 변경#

분석적 계산에서는 이 특정 필터를 제거하는 것이 허용될 수 있습니다. 이 방법은 차단된 사용자의 머지 리퀘스트를 포함하더라도 통계에 큰 영향을 주지 않는다고 가정합니다.