ClickHouseとマージリクエスト分析

マージリクエスト分析機能は、プロジェクト内でマージされたマージリクエストに関する統計を表示し、レコードレベルのメタデータも公開します。集計には以下が含まれます：

  • マージまでの平均時間：作成時間とマージ時間の間の期間。
  • 月別集計：マージされたマージリクエストの12ヶ月のチャート。

チャートの下で、ユーザーはページごとに12ヶ月分のマージリクエストのリストを見ることができます。

でフィルターできます：

  • Author
  • Assignee
  • ラベル
  • Milestone
  • ソースブランチ
  • ターゲットブランチ

現在のパフォーマンス問題

  • 集計クエリには特殊なインデックスが必要で、追加のディスクスペースがかかります（インデックスのみのスキャン）。
  • 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にマージリクエストデータを保存しクエリするには、他にもいくつかの使用例があります。このドキュメントでは、この特定の機能に焦点を当てます。

Core データはmerge_request_metrics とmerge_requests データベーステーブルに存在します。いくつかのフィルターは、結合するために余分なテーブルを必要とします：

  • banned_users:禁止されたユーザーによって作成されたマージリクエストを除外します。
  • labels:マージリクエストには1つ以上のラベルを割り当てることができます。
  • assignees:マージリクエストは1つ以上の担当者を持つことができます。
  • merged_at:カラムは merged_at merge_request_metrics テーブルにあります。

merge_requests テーブルには、直接フィルタリングできるデータが含まれています：

  • 作成者:author_id カラム経由で。
  • マイルストーン：milestone_id コラムより。
  • ソースブランチ。
  • ターゲットブランチ。
  • プロジェクト:project_id 列から。

クリックハウスのデータを最新に保つ

merge_requests テーブルの merge_requests複製や同期merge_requests だけでは残念ながら十分 merge_requestsではありません。ClickHouseデータベースにmerge_requests 正規化解除された1 merge_requests行merge_requests を挿入するためには、関連テーブルへの個別のクエリが必要 merge_requestsです。

変更検出を実装するのは容易ではありません。いくつかの手抜きがあります：

  • この機能はGitLab PremiumとGitLab Ultimateのお客様にご利用いただけます。全てのデータを同期する必要はありませんが、代わりにライセンスされたグループの一部であるmerge_requests レコードだけを同期します。
  • データの変更は（多くの場合）MergeRequest サービス経由で行われます。updated_at のタイムスタンプ列をバンプすることで、ほぼ一貫性が保たれます。ある種のインクリメンタルな同期プロセスを実装することができます。
  • 私たちはマージされたマージリクエストをクエリするだけでよいのです。マージ後、レコードが変更されることはほとんどありません。

データベースのテーブル構造

データベースのテーブル構造では、非正規化を使用して、必要なすべてのカラムを1つのデータベーステーブルで利用できるようにしています。これにより、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 列によって順序付けられます。この順序付けにより、今回のユースケースであるプロジェクト内の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 としてキャストされているので、異なる行で同じ値を持つ可能性が高いです。

元のカウントクエリでは、1ヶ月分のデータしか集計できませんでした。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.計算は2つの別々のステップで行われます：

  1. 月ごとのカウントと月ごとの期間値を要求します。
  2. カウントを合計して年間カウントを取得します。
  3. 継続時間を合計して年間の継続時間を求めます。
  4. 期間をカウントで割ります。

ClickHouseでは、1つのクエリでマージする平均時間を計算できます：

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 table が何百万行にも膨れ上がらないと仮定すると、テーブル全体を定期的に 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 テーブルを辞書として保存することで、クエリのパフォーマンスをさらに向上させることもできます。

アイデア3：機能を変更する

分析的な計算のために、この特定のフィルターを削除してもよいかもしれません。このアプローチは、禁止されたユーザーのマージリクエストを含めても統計が大きく歪まないことを前提としています。