diff options
Diffstat (limited to 'doc/development/database/clickhouse/gitlab_activity_data.md')
| -rw-r--r-- | doc/development/database/clickhouse/gitlab_activity_data.md | 482 |
1 files changed, 482 insertions, 0 deletions
diff --git a/doc/development/database/clickhouse/gitlab_activity_data.md b/doc/development/database/clickhouse/gitlab_activity_data.md new file mode 100644 index 00000000000..6ba11b8afaf --- /dev/null +++ b/doc/development/database/clickhouse/gitlab_activity_data.md @@ -0,0 +1,482 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments +--- + +# Store GitLab activity data in ClickHouse + +## Overview of the existing implementation + +### What is GitLab activity data + +GitLab records activity data during its operation as users interact with the application. Most of these interactions revolve around the projects, issues, and merge requests domain objects. Users can perform several different actions and some of these actions are recorded in a separate PostgreSQL database table called `events`. + +Example events: + +- Issue opened +- Issue reopened +- User joined a project +- Merge Request merged +- Repository pushed +- Snippet created + +### Where is the activity data used + +Several features use activity data: + +- The user's [contribution calendar](../../../user/profile/contributions_calendar.md) on the profile page. +- Paginated list of the user's contributions. +- Paginated list of user activity for a Project and a Group. +- [Contribution analytics](../../../user/group/contribution_analytics/index.md). + +### How is the activity data created + +The activity data is usually generated on the service layer when a specific operation is executed by the user. The persistence characteristics of an `events` record depend on the implementation of the service. Two main approaches exist: + +1. In the database transaction where the actual event occurs. +1. After the database transaction (which could be delayed). + +The above-mentioned mechanics provide a "mostly" consistent stream of `events`. + +For example, consistently recording an `events` record: + +```ruby +ApplicationRecord.transaction do + issue.closed! + Event.create!(action: :closed, target: issue) +end +``` + +Example, unsafe recording of an `events` record: + +```ruby +ApplicationRecord.transaction do + issue.closed! +end + +# If a crash happens here, the event will not be recorded. +Event.create!(action: :closed, target: issue) +``` + +### Database table structure + +The `events` table uses [polymorphic association](https://guides.rubyonrails.org/association_basics.html#polymorphic-associations) to allow associating different database tables (issues, merge requests, etc.) with a record. A simplified database structure: + +```sql + 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 | +``` + +Some unexpected characteristics due to the evolving database design: + +- The `project_id` and the `group_id` columns are mutually exclusive, internally we call them resource parent. + - Example 1: for an issue opened event, the `project_id` field is populated. + - Example 2: for an epic-related event, the `group_id` field is populated (epic is always part of a group). +- The `target_id` and `target_type` column pair identifies the target record. + - Example: `target_id=1` and `target_type=Issue`. + - When the columns are `null`, we refer to an event which has no representation in the database. For example a repository `push` action. +- Fingerprint is used in some cases to later alter the event based on some metadata change. This approach is mostly used for Wiki pages. + +### Database record modifications + +Most of the data is written once however, we cannot say that the table is append-only. A few use cases where actual row updates and deletions happen: + +- Fingerprint-based update for certain Wiki page records. +- When user or an associated resource is deleted, the event rows are also deleted. + - The deletion of the associated `events` records happens in batches. + +### Current performance problems + +- The table uses significant disk space. +- Adding new events may significantly increase the database record count. +- Implementing data pruning logic is difficult. +- Time-range-based aggregations are not performant enough, some features may break due to slow database queries. + +### Example queries + +NOTE: +These queries have been significantly simplified from the actual queries from production. + +Database query for the user's contribution graph: + +```sql +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) +``` + +Query for group contributions for each user: + +```sql +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 +``` + +## Storing activity data in ClickHouse + +### Data persistence + +At the moment, there is no consensus about the way we would replicate data from the PostgreSQL database to ClickHouse. A few ideas that might work for the `events` table: + +#### Record data immediately + +This approach provides a simple way to keep the existing `events` table working while we're also sending data to the ClickHouse database. When an event record is created, ensure that it's created outside of the transaction. After persisting the data in PostgreSQL, persist it in ClickHouse. + +```ruby +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) +``` + +What's behind the implementation of `ClickHouse::Event` is not decided yet, it could be one of the following: + +- ActiveRecord model directly connecting the ClickHouse database. +- REST API call to an intermediate service. +- Enqueueing an event to an event-streaming tool (like Kafka). + +#### Replication of `events` rows + +Assuming that the creation of `events` record is an integral part of the system, introducing another storage call might cause performance degradation in various code paths, or it could introduce significant complexity. + +Rather than sending data to ClickHouse on event creation time, we would move this processing in the background by iterating over the `events` table and sending the newly created database rows. + +By keeping track of which records have been sent over ClickHouse, we could incrementally send data. + +```ruby +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 database table structure + +When coming up with the initial database structure, we must look at the way the data is queried. + +We have two main use cases: + +- Query data for a certain user, within a time range. + - `WHERE author_id = 1 AND created_at BETWEEN '2021-01-01' AND '2021-12-31'` + - Additionally, there might be extra `project_id` condition due to the access control check. +- Query data for a project or group, within a time range. + - `WHERE project_id IN (1, 2) AND created_at BETWEEN '2021-01-01' AND '2021-12-31'` + +The `author_id` and `project_id` columns are considered high-selectivity columns. By this we mean that optimizing the filtering of the `author_id` and the `project_id` columns is desirable for having performant database queries. + +The most recent activity data is queried more often. At some point, we might just drop or relocate older data. Most of the features look back only a year. + +For these reasons, we could start with a database table storing low-level `events` data: + +```plantuml +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 +} +``` + +The SQL statement for creating the table: + +```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; +``` + +A few changes compared to the PostgreSQL version: + +- `target_type` uses [an optimization](https://clickhouse.com/docs/en/sql-reference/data-types/lowcardinality/) for low-cardinality column values. +- `fingerprint` becomes an integer and leverages a performant integer-based hashing function such as xxHash64. +- All columns get a default value, the 0 default value for the integer columns means no value. See the related [best practices](https://clickhouse.com/docs/en/cloud/bestpractices/avoid-nullable-columns/). +- `NOT NULL` to ensure that we always use the default values when data is missing (different behavior compared to PostgreSQL). +- The "primary" key automatically becomes the `id` column due to the `ORDER BY` clause. + +Let's insert the same primary key value twice: + +```sql +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); +``` + +Let's inspect the results: + +```sql +SELECT * FROM events +``` + +- We have two rows with the same `id` value (primary key). +- The `null` `action` becomes `0`. +- The non-specified fingerprint column becomes `0`. +- The `DateTime` columns have the insert timestamp. + +ClickHouse will eventually "replace" the rows with the same primary key in the background. When running this operation, the higher `updated_at` value takes precedence. The same behavior can be simulated with the `final` keyword: + +```sql +SELECT * FROM events FINAL +``` + +Adding `FINAL` to a query can have significant performance consequences, some of the issues are documented in the [ClickHouse documentation](https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier). + +We should always expect duplicated values in the table, so we must take care of the deduplication in query time. + +### ClickHouse database queries + +ClickHouse uses SQL for querying the data, in some cases, a PostgreSQL query can be used in ClickHouse without major modifications assuming that the underlying database structure is very similar. + +Query for group contributions for each user (PostgreSQL): + +```sql +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 +``` + +The same query would work in PostgreSQL however, we might see duplicated values in ClickHouse due to the way the table engine works. The deduplication can be achieved by using a nested `FROM` statement. + +```sql +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 +``` + +- Take the most recent column values based on the `updated_at` column. +- Take the first value for `created_at`, assuming that the first `INSERT` contains the correct value. An issue only when we don't sync `created_at` at all and the default value (`NOW()`) is used. +- Take the most recent `updated_at` value. + +The query looks more complicated now because of the deduplication logic. The complexity can be hidden behind a database view. + +### Optimizing the performance + +The aggregation query in the previous section might not be performant enough for production use due to the large volume of data. + +Let's add 1 million extra rows to the `events` table: + +```sql +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; +``` + +Running the previous aggregation query in the console prints out some performance data: + +```plaintext +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.) +``` + +The query returned 1 row (correctly) however, it had to process 1 million rows (full table). We can optimize the query with an index on the `project_id` column: + +```sql +ALTER TABLE events ADD INDEX project_id_index project_id TYPE minmax GRANULARITY 10; +ALTER TABLE events MATERIALIZE INDEX project_id_index; +``` + +Executing the query returns much better figures: + +```plaintext +Read 2 rows, 107.00 B in 0.005616811 sec., 356 rows/sec., 18.60 KiB/sec. +``` + +To optimize the date range filter on the `created_at` column, we could try adding another index on the `created_at` column. + +#### Query for the contribution graph + +Just to recap, this is the PostgreSQL query: + +```sql +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) +``` + +The filtering and the count aggregation is mainly done on the `author_id` and the `created_at` columns. Grouping the data by these two columns would probably give an adequate performance. + +We could attempt adding an index on the `author_id` column however, we still need an additional index on the `created_at` column to properly cover this query. Besides, under the contribution graph, GitLab shows the list of ordered contributions of the user which would be great to get it efficiently via a different query with the `ORDER BY` clause. + +For these reasons, it's probably better to use a ClickHouse projection which stores the events rows redundantly but we can specify a different sort order. + +The ClickHouse query would be the following (with a slightly adjusted date range): + +```sql +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) +``` + +The query does a full table scan, let's optimize it: + +```sql +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; +``` + +#### Pagination of contributions + +Listing the contributions of a user can be queried in the following way: + +```sql +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 supports the standard `LIMIT N OFFSET M` clauses, so we can request the next page: + +```sql +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 +``` |