GitLabアクティビティデータをClickHouseに保存
既存の実装の概要
GitLabアクティビティデータとは?
GitLabはオペレーション中、ユーザーがアプリケーションを操作する際のアクティビティデータを記録します。これらのインタラクションのほとんどは、プロジェクト、イシュー、マージリクエストのドメインオブジェクトを中心としたものです。ユーザーはいくつかの異なるアクションを実行することができ、これらのアクションの一部はevents
という別の PostgreSQL データベースのテーブルに記録されます。
イベントの例
- イシューオープン
- イシュー再開
- ユーザーがプロジェクトに参加しました。
- マージリクエストがマージされました。
- リポジトリがプッシュ
- スニペット作成
アクティビティデータの使用場所
アクティビティデータを使用する機能がいくつかあります:
アクティビティデータの作成方法
アクティビティデータは通常、ユーザーによって特定のオペレーションが実行されると、サービスレイヤーで生成されます。events
レコードの永続性特性は、サービスの実装に依存します。主に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
フィールドに値が入力されます(エピックは常にグループの一部です)。
- 例1: イシューがオープンされたイベントの場合、
-
target_id
とtarget_type
列のペアは、対象のレコードを識別します。- 例:
target_id=1
とtarget_type=Issue
。 - カラムが
null
の場合、データベースに表現がないイベントを指します。例えば、リポジトリpush
アクションです。
- 例:
- フィンガープリントは、メタデータの変更に基づいてイベントを変更するために使用されます。このアプローチは主に Wiki ページに使用されます。
データベースレコードの変更
ほとんどのデータは一度だけ書き込まれますが、テーブルが追記のみであるとは言えません。実際に行の更新や削除が行われるユースケースをいくつか紹介します:
- 特定の Wiki ページレコードの指紋ベースの更新。
- ユーザーまたは関連リソースが削除されると、イベント行も削除されます。
- 関連する
events
レコードの削除はバッチで行われます。
- 関連する
現在のパフォーマンス問題
- テーブルがかなりのディスク容量を使用しています。
- 新しいイベントを追加すると、データベースのレコード数が大幅に増える可能性があります。
- データ刈り込みロジックの実装は困難です。
- 時間範囲ベースの集計は十分なパフォーマンスが得られず、データベースのクエリが遅いために一部の機能が壊れる可能性があります。
クエリの例
ユーザーの貢献グラフのデータベースクエリ:
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