GitLabアクティビティデータをClickHouseに保存

既存の実装の概要

GitLabアクティビティデータとは？

GitLabはオペレーション中、ユーザーがアプリケーションを操作する際のアクティビティデータを記録します。これらのインタラクションのほとんどは、プロジェクト、イシュー、マージリクエストのドメインオブジェクトを中心としたものです。ユーザーはいくつかの異なるアクションを実行することができ、これらのアクションの一部はevents という別の PostgreSQL データベースのテーブルに記録されます。

イベントの例

  • イシューオープン
  • イシュー再開
  • ユーザーがプロジェクトに参加しました。
  • マージリクエストがマージされました。
  • リポジトリがプッシュ
  • スニペット作成

アクティビティデータの使用場所

アクティビティデータを使用する機能がいくつかあります：

  • プロフィールページにあるユーザーの貢献者カレンダー。
  • ユーザーの貢献者のページ分割リスト。
  • プロジェクトとグループのユーザーアクティビティのページ分割リスト。
  • 貢献分析。

アクティビティデータの作成方法

アクティビティデータは通常、ユーザーによって特定のオペレーションが実行されると、サービスレイヤーで生成されます。events レコードの永続性特性は、サービスの実装に依存します。主に2つのアプローチがあります：

  1. 実際のイベントが発生するデータベーストランザクション内。
  2. データベーストランザクションの後（遅延する可能性があります）。

上記の仕組みにより、「ほぼ」一貫したストリームevents 。

例えば、events の記録を一貫して記録すること：

ApplicationRecord.transaction do
  issue.closed!
  Event.create!(action: :closed, target: issue)
end

例、events の安全でない記録：

ApplicationRecord.transaction do
  issue.closed!
end

# If a crash happens here, the event will not be recorded.
Event.create!(action: :closed, target: issue)

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

events テーブルはポリモーフィック・アソシエーションを使用して、レコードに異なるデータベース・テーブル (イシュー、マージ・リクエストなど) を関連付けることができます。簡略化されたデータベース構造

   Column    |           Type            | Nullable |              Default               | Storage  |
-------------+--------------------------+-----------+----------+------------------------------------+
 project_id  | integer                   |          |                                    | plain    |
 author_id   | integer                   | not null |                                    | plain    |
 target_id   | integer                   |          |                                    | plain    |
 created_at  | timestamp with time zone  | not null |                                    | plain    |
 updated_at  | timestamp with time zone  | not null |                                    | plain    |
 action      | smallint                  | not null |                                    | plain    |
 target_type | character varying         |          |                                    | extended |
 group_id    | bigint                    |          |                                    | plain    |
 fingerprint | bytea                     |          |                                    | extended |
 id          | bigint                    | not null | nextval('events_id_seq'::regclass) | plain    |

進化するデータベース設計によるいくつかの予期せぬ特徴：

  • project_id とgroup_id カラムは相互に排他的で、内部ではリソースペアレントと呼んでいます。
    • 例1: イシューがオープンされたイベントの場合、project_id フィールドが入力されます。
    • 例2: エピック関連イベントの場合、group_id フィールドに値が入力されます(エピックは常にグループの一部です)。
  • target_id とtarget_type 列のペアは、対象のレコードを識別します。
    • 例：target_id=1 とtarget_type=Issue 。
    • カラムがnull の場合、データベースに表現がないイベントを指します。例えば、リポジトリpush アクションです。
  • フィンガープリントは、メタデータの変更に基づいてイベントを変更するために使用されます。このアプローチは主に Wiki ページに使用されます。

データベースレコードの変更

ほとんどのデータは一度だけ書き込まれますが、テーブルが追記のみであるとは言えません。実際に行の更新や削除が行われるユースケースをいくつか紹介します：

  • 特定の Wiki ページレコードの指紋ベースの更新。
  • ユーザーまたは関連リソースが削除されると、イベント行も削除されます。
    • 関連するevents レコードの削除はバッチで行われます。

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

  • テーブルがかなりのディスク容量を使用しています。
  • 新しいイベントを追加すると、データベースのレコード数が大幅に増える可能性があります。
  • データ刈り込みロジックの実装は困難です。
  • 時間範囲ベースの集計は十分なパフォーマンスが得られず、データベースのクエリが遅いために一部の機能が壊れる可能性があります。

クエリの例

note
これらのクエリは、実際のクエリから大幅に簡略化されています。

ユーザーの貢献グラフのデータベースクエリ：

SELECT DATE(events.created_at), COUNT(*)
FROM events
WHERE events.author_id = 1
AND events.created_at BETWEEN '2022-01-17 23:00:00' AND '2023-01-18 22:59:59.999999'
AND (
  (
    events.action = 5
  ) OR
  (
    events.action IN (1, 3) -- Enum values are documented in the Event model, see the ACTIONS constant in app/models/event.rb
    AND events.target_type IN ('Issue', 'WorkItem')
  ) OR
  (
    events.action IN (7, 1, 3)
    AND events.target_type = 'MergeRequest'
  ) OR
  (
    events.action = 6
  )
)
GROUP BY DATE(events.created_at)

各ユーザーのグループ貢献のクエリ：

SELECT events.author_id, events.target_type, events.action, COUNT(*)
FROM events
WHERE events.created_at BETWEEN '2022-01-17 23:00:00' AND '2023-03-18 22:59:59.999999'
AND events.project_id IN (1, 2, 3) -- list of project ids in the group
GROUP BY events.author_id, events.target_type, events.action

ClickHouseにアクティビティデータを保存

データの永続性

今のところ、PostgreSQLデータベースからClickHouseにデータをレプリケートする方法についてのコンセンサスはありません。events 、いくつかのアイデアがあります：

データを即座に記録

この方法は、既存のevents テーブルを動作させながら、ClickHouse データベースにデータを送信する簡単な方法です。イベントレコードが作成されたら、トランザクションの外で作成されるようにします。PostgreSQLにデータを永続化した後、ClickHouseに永続化します。

ApplicationRecord.transaction do
  issue.update!(state: :closed)
end

# could be a method to hide complexity
Event.create!(action: :closed, target: issue)
ClickHouse::Event.create(action: :closed, target: issue)

ClickHouse::Event の実装の背後にあるものはまだ決まっていません：

  • ClickHouseデータベースに直接接続するActiveRecordモデル。
  • 中間サービスへのREST API呼び出し。
  • イベントストリーミングツール（Kafkaなど）へのイベントのエンキュー。

events 行のレプリケーション。

events レコードの作成がシステム内部に不可欠な部分であると仮定すると、別のストレージコールを導入することは、様々なコードパスにおいてパフォーマンスの低下を引き起こす可能性があります。

イベント作成時にClickHouseにデータを送信するのではなく、events テーブルを繰り返し、新しく作成されたデータベース行を送信することで、この処理をバックグラウンドで行います。

どのレコードがClickHouseに送信されたかを追跡することで、データを段階的に送信することができます。

last_updated_at = SyncProcess.last_updated_at

# oversimplified loop, we would probably batch this...
Event.where(updated_at > last_updated_at).each do |row|
  last_row = ClickHouse::Event.create(row)
end

SyncProcess.last_updated_at = last_row.updated_at

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

データベースの初期構造を考えるとき、データのクエリ方法を見なければなりません。

主に2つのユースケースがあります：

  • 時間範囲内の特定のユーザーのデータをクエリします。
    • WHERE author_id = 1 AND created_at BETWEEN '2021-01-01' AND '2021-12-31'
    • さらに、アクセス・コントロール・チェックのため、project_id 。
  • プロジェクトまたはグループの、時間範囲内のデータをクエリします。
    • WHERE project_id IN (1, 2) AND created_at BETWEEN '2021-01-01' AND '2021-12-31'

author_id とproject_id カラムは高選択性カラムとみなされます。これは、author_id とproject_id カラムのフィルタリングを最適化することが、パフォーマンスの高いデータベースクエリを実現するために望ましいことを意味します。

最新のアクティビティデータはより頻繁にクエリされます。ある時点で、古いデータを削除したり、再配置したりすることもあります。ほとんどの機能は1年前までしか見ません。

このような理由から、events 低レベルのデータを保存するデータベース・テーブルから始めることができます：

テーブルを作成するSQL文：

CREATE TABLE events
(
    `id` UInt64,
    `project_id` UInt64 DEFAULT 0 NOT NULL,
    `group_id` UInt64 DEFAULT 0 NOT NULL,
    `author_id` UInt64 DEFAULT 0 NOT NULL,
    `target_id` UInt64 DEFAULT 0 NOT NULL,
    `target_type` LowCardinality(String) DEFAULT '' NOT NULL,
    `action` UInt8 DEFAULT 0 NOT NULL,
    `fingerprint` UInt64 DEFAULT 0 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 id;

PostgreSQLバージョンと比較して、いくつかの変更があります：

  • target_type は低い基数性の列値に対する最適化を使用しています。
  • fingerprint は整数となり、xxHash64 のような高性能な整数ベースのハッシュ関数を利用します。
  • すべてのカラムにデフォルト値が設定され、整数カラムのデフォルト値 0 は値がないことを意味します。関連するベストプラクティスを参照してください。
  • NOT NULL を参照してください（PostgreSQLとは動作が異なります）。
  • ORDER BY 節により、”主 “キーは自動的にid 列になります。

同じ主キーの値を2回挿入してみましょう：

INSERT INTO events (id, project_id, target_id, author_id, target_type, action) VALUES (1, 2, 3, 4, 'Issue', null);
INSERT INTO events (id, project_id, target_id, author_id, target_type, action) VALUES (1, 20, 30, 5, 'Issue', null);

その結果を見てみましょう：

SELECT * FROM events
  • id （主キー）の値が同じ行が2つあります。
  • null action は0 になります。
  • 非特定フィンガープリント列は0 となります。
  • DateTime カラムは挿入タイムスタンプを持ちます。

ClickHouseは、最終的にバックグラウンドで同じ主キーを持つ行を “置換 “します。このオペレーションを実行する場合、updated_at の値が高い方が優先されます。同じ動作をfinal キーワードでシミュレートできます：

SELECT * FROM events FINAL

クエリにFINAL を追加すると、パフォーマンスに重大な影響を及ぼす可能性があります。いくつかのイシューはClickHouseのドキュメントに記載されています。

テーブルには常に重複した値が存在することが予想されるため、クエリ時に重複排除を行う必要があります。

ClickHouseデータベースクエリ

ClickHouseはデータのクエリにSQLを使用します。場合によっては、PostgreSQLのクエリをClickHouseで使用することができます。

各ユーザーのグループ貢献のクエリ（PostgreSQL）：

SELECT events.author_id, events.target_type, events.action, COUNT(*)
FROM events
WHERE events.created_at BETWEEN '2022-01-17 23:00:00' AND '2023-03-18 22:59:59.999999'
AND events.project_id IN (1, 2, 3) -- list of project ids in the group
GROUP BY events.author_id, events.target_type, events.action

同じクエリがPostgreSQLでも動作しますが、ClickHouseではテーブルエンジンの動作により重複した値が表示される可能性があります。重複排除は、FROM ステートメントをネストすることで実現できます。

SELECT author_id, target_type, action, count(*)
FROM (
  SELECT
  id,
  argMax(events.project_id, events.updated_at) AS project_id,
  argMax(events.group_id, events.updated_at) AS group_id,
  argMax(events.author_id, events.updated_at) AS author_id,
  argMax(events.target_type, events.updated_at) AS target_type,
  argMax(events.target_id, events.updated_at) AS target_id,
  argMax(events.action, events.updated_at) AS action,
  argMax(events.fingerprint, events.updated_at) AS fingerprint,
  FIRST_VALUE(events.created_at) AS created_at,
  MAX(events.updated_at) AS updated_at
  FROM events
  WHERE events.created_at BETWEEN '2022-01-17 23:00:00' AND '2023-03-18 22:59:59.999999'
  AND events.project_id IN (1, 2, 3) -- list of project ids in the group
  GROUP BY id
) AS events
GROUP BY author_id, target_type, action
  • updated_at カラムに基づいて、最新のカラム値を取得します。
  • 最初のINSERT に正しい値が含まれていると仮定して、created_at の最初の値を取ります。created_at をまったく同期せず、デフォルト値 (NOW()) を使用する場合にのみイシューが発生します。
  • 最新のupdated_at の値を取ります。

重複排除ロジックのため、クエリはより複雑に見えます。この複雑さはデータベースビューの後ろに隠すことができます。

パフォーマンスの最適化

前節の集計クエリは、データ量が多いため、実運用には十分なパフォーマンスではないかもしれません。

events テーブルに100万行追加してみましょう：

INSERT INTO events (id, project_id, author_id, target_id, target_type, action)  SELECT id, project_id, author_id, target_id, 'Issue' AS target_type, action FROM generateRandom('id UInt64, project_id UInt64, author_id UInt64, target_id UInt64, action UInt64') LIMIT 1000000;

先ほどの集計クエリをコンソールで実行すると、パフォーマンスデータが出力されます：

1 row in set. Elapsed: 0.122 sec. Processed 1.00 million rows, 42.00 MB (8.21 million rows/s., 344.96 MB/s.)

クエリは1行を（正しく）返しましたが、100万行（フルテーブル）を処理しなければなりませんでした。このクエリは、project_id 列にインデックスを付けることで最適化できます：

ALTER TABLE events ADD INDEX project_id_index project_id TYPE minmax GRANULARITY 10;
ALTER TABLE events MATERIALIZE INDEX project_id_index;

クエリを実行すると、より良い数値が返されます：

Read 2 rows, 107.00 B in 0.005616811 sec., 356 rows/sec., 18.60 KiB/sec.

created_at カラムの created_at日付範囲フィルタを最適化するために、created_at カラムに別のインデックスを追加して created_atみましょう。

貢献グラフのクエリ

これはPostgreSQLのクエリです：

SELECT DATE(events.created_at), COUNT(*)
FROM events
WHERE events.author_id = 1
AND events.created_at BETWEEN '2022-01-17 23:00:00' AND '2023-01-18 22:59:59.999999'
AND (
  (
    events.action = 5
  ) OR
  (
    events.action IN (1, 3) -- Enum values are documented in the Event model, see the ACTIONS constant in app/models/event.rb
    AND events.target_type IN ('Issue', 'WorkItem')
  ) OR
  (
    events.action IN (7, 1, 3)
    AND events.target_type = 'MergeRequest'
  ) OR
  (
    events.action = 6
  )
)
GROUP BY DATE(events.created_at)

フィルタリングとカウントの集約は主にauthor_id とcreated_at 列に対して行われます。この2つのカラムでデータをグループ化すれば、おそらく十分なパフォーマンスが得られるでしょう。

author_id カラムにインデックスを追加することもできますが、このクエリを適切に処理するにはcreated_at カラムにもインデックスを追加する必要があります。それに、GitLab では貢献グラフの下にユーザーの貢献した順番のリストが表示されます。ORDER BY 節を使った別のクエリでこのリストを効率的に取得することもできます。

このような理由から、イベントの行を冗長に保存する ClickHouse プロジェクトを使ったほうがいいでしょう。

ClickHouseクエリは以下のようになります（日付範囲は若干調整されています）：

SELECT DATE(events.created_at) AS date, COUNT(*) AS count
FROM (
  SELECT
  id,
  argMax(events.created_at, events.updated_at) AS created_at
  FROM events
  WHERE events.author_id = 4
  AND events.created_at BETWEEN '2023-01-01 23:00:00' AND '2024-01-01 22:59:59.999999'
  AND (
    (
      events.action = 5
    ) OR
    (
      events.action IN (1, 3) -- Enum values are documented in the Event model, see the ACTIONS constant in app/models/event.rb
      AND events.target_type IN ('Issue', 'WorkItem')
    ) OR
    (
      events.action IN (7, 1, 3)
      AND events.target_type = 'MergeRequest'
    ) OR
    (
      events.action = 6
    )
  )
  GROUP BY id
) AS events
GROUP BY DATE(events.created_at)

このクエリはフルテーブルスキャンを行います：

ALTER TABLE events ADD PROJECTION events_by_authors (
  SELECT * ORDER BY author_id, created_at -- different sort order for the table
);

ALTER TABLE events MATERIALIZE PROJECTION events_by_authors;

貢献者のページネーション

ユーザーの貢献者の一覧は以下の方法でクエリできます：

SELECT events.*
FROM (
  SELECT
  id,
  argMax(events.project_id, events.updated_at) AS project_id,
  argMax(events.group_id, events.updated_at) AS group_id,
  argMax(events.author_id, events.updated_at) AS author_id,
  argMax(events.target_type, events.updated_at) AS target_type,
  argMax(events.target_id, events.updated_at) AS target_id,
  argMax(events.action, events.updated_at) AS action,
  argMax(events.fingerprint, events.updated_at) AS fingerprint,
  FIRST_VALUE(events.created_at) AS created_at,
  MAX(events.updated_at) AS updated_at
  FROM events
  WHERE events.author_id = 4
  GROUP BY id
  ORDER BY created_at DESC, id DESC
) AS events
LIMIT 20

ClickHouseは標準のLIMIT N OFFSET M 節をサポートしているので、次のページを要求することができます：

SELECT events.*
FROM (
  SELECT
  id,
  argMax(events.project_id, events.updated_at) AS project_id,
  argMax(events.group_id, events.updated_at) AS group_id,
  argMax(events.author_id, events.updated_at) AS author_id,
  argMax(events.target_type, events.updated_at) AS target_type,
  argMax(events.target_id, events.updated_at) AS target_id,
  argMax(events.action, events.updated_at) AS action,
  argMax(events.fingerprint, events.updated_at) AS fingerprint,
  FIRST_VALUE(events.created_at) AS created_at,
  MAX(events.updated_at) AS updated_at
  FROM events
  WHERE events.author_id = 4
  GROUP BY id
  ORDER BY created_at DESC, id DESC
) AS events
LIMIT 20 OFFSET 20