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_id
열을 통해. -
마일스톤:
milestone_id
열을 통해. - 소스 브랜치.
- 대상 브랜치.
-
프로젝트:
project_id
열을 통해.
ClickHouse 데이터 최신 상태 유지
merge_requests
테이블을 복제하거나 동기화하는 것만으로 충분하지 않습니다. 정규화되지 않은 merge_requests
행을 ClickHouse 데이터베이스에 삽입하려면 관련된 테이블에 대한 별도의 쿼리가 필요합니다.
변경 감지는 구현하기가 어렵습니다. 이를 해결할 수 있는 몇 가지 방법이 있습니다.
- 이 기능은 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);
활동 데이터 예와 유사하게 ReplacingMergeTree
엔진을 사용합니다. 병합 요청 레코드의 여러 열이 변경될 수 있으므로 테이블을 최신 상태로 유지하는 것이 중요합니다.
데이터베이스 테이블은 project_id, merged_at, id
열로 정렬됩니다. 이러한 순서는 project
에서 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를 사용하면 데이터를 1년치로 집계할 수 있습니다.
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는 일 단위로 표시됩니다.
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줄 in set. Elapsed: 0.016 sec.
처리된 8.19 천 개의 행, 589.99 KB (505.38 천 개의 행/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에 정기적으로 전체 테이블을 동기화할 수 있습니다. 이 접근 방식으로 거의 일관된 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 -- 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
테이블은 dictionary로 저장되어 쿼리 성능을 더 향상시킬 수 있습니다.
아이디어 3: 기능 변경
분석 계산에 대해 금지된 사용자의 병합 요청을 포함시키지 않는 것도 수용할 수 있을 것입니다. 이 접근 방식은 금지된 사용자의 병합 요청을 포함하는 것이 통계를 현저하게 왜곡시키지 않는다고 가정합니다.