Help us learn about your current experience with the documentation. Take the survey.

在 ClickHouse 中存储 GitLab 活动数据

现有实现概述

什么是 GitLab 活动数据

GitLab 在运行时会记录活动数据，这些数据产生于用户与应用程序的交互过程中。大多数交互都围绕项目、议题和合并请求这些领域对象展开。用户可以执行多种不同的操作，其中一些操作会被记录在一个名为 events 的独立 PostgreSQL 数据库表中。

示例事件：

  • 议题已打开
  • 议题已重新打开
  • 用户加入项目
  • 合并请求已合并
  • 仓库已推送
  • 代码片段已创建

活动数据用于何处

多个功能使用活动数据：

  • 用户在个人资料页面上的贡献日历。
  • 用户贡献的分页列表。
  • 项目和组的用户活动分页列表。
  • 贡献分析。

活动数据是如何创建的

活动数据通常在服务层生成，当用户执行特定操作时。events 记录的持久化特性取决于服务的实现。存在两种主要方法：

  1. 在实际事件发生的数据库事务中。
  2. 在数据库事务之后（可能是延迟的）。

上述机制提供了一个"基本"一致的 events 数据流。

例如，一致地记录 events 记录：

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

示例，不安全地记录 events 记录：

ApplicationRecord.transaction do
  issue.closed!
end

# 如果在这里发生崩溃，事件将不会被记录。
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 操作。
  • 指纹（Fingerprint）在某些情况下用于根据某些元数据更改稍后修改事件。此方法主要用于 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) -- 枚举值在 Event 模型中有文档，请参见 app/models/event.rb 中的 ACTIONS 常量
    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) -- 组中的项目 ID 列表
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

# 可能是一个隐藏复杂性的方法
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 来将此处理移至后台。

通过跟踪哪些记录已发送到 ClickHouse，我们可以增量发送数据。

last_updated_at = SyncProcess.last_updated_at

# 过于简化的循环，我们可能会批量处理...
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 数据库表结构

在设计初始数据库结构时，我们必须考虑数据的查询方式。

我们有两个主要用例：

  • 查询特定用户在一定时间范围内的数据。
    • 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 列的过滤对于获得高性能数据库查询是可取的。

最新的活动数据被更频繁地查询。在某些时候，我们可能只是删除或移动旧数据。大多数功能只回溯一年。

出于这些原因，我们可以从存储低级 events 数据的数据库表开始：

hide circle

entity "events" as events {
  id : UInt64 ("primary key")
--
  project_id : UInt64
  group_id : UInt64
  target_id : UInt64
  target_type : String
  action : UInt8
  fingerprint : UInt64
  created_at : DateTime
  updated_at : DateTime
}

创建表的 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 列。

让我们插入相同的主键值两次：

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 值（主键）。
  • 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) -- 组中的项目 ID 列表
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) -- 组中的项目 ID 列表
  GROUP BY id
) AS events
GROUP BY author_id, target_type, action
  • 根据 updated_at 列取最新的列值。
  • 对 created_at 取第一个值，假设第一个 INSERT 包含正确的值。仅当我们完全不同步 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 列上添加另一个索引。

贡献图查询

回顾一下，这是 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) -- 枚举值在 Event 模型中有文档，请参见 app/models/event.rb 中的 ACTIONS 常量
    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 列上完成。按这两个列对数据进行分组可能会提供足够的性能。

我们可以尝试在 author_id 列上添加索引，但我们仍然需要在 created_at 列上添加额外的索引来正确覆盖此查询。此外，在贡献图下，GitLab 显示用户有序的贡献列表，通过带有 ORDER BY 子句的不同查询高效获取它将是很好的。

出于这些原因，使用 ClickHouse 投影（projection）可能更好，它冗余存储事件行，但我们可以指定不同的排序顺序。

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) -- 枚举值在 Event 模型中有文档，请参见 app/models/event.rb 中的 ACTIONS 常量
      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 -- 表的不同排序顺序
);

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